New post
Article
· Feb 26, 2018 2m read

M:N Relationship

#Other

If you have worked with Caché Objects,
You know already all about Relationships (one:many , parent:child) ...
But you will not find a word on many:many relationships in the docs.

But I met the question quite often from new adopters of Caché objects:
"Is it possible to implement many:many relationships ?" YES - of course !

HowTo depends on the related tasks: There is a heavy and a lightweight solution.
Both have in common that they are not just out of the box and you have
to add some code to manage it.

Let's take an example based on SAMPLES namespace:

we have the case of an N:1 relationship

But how do you handle Employees with more than 1 Company ?

The heavy solution:
You add an additional persitstent class with a one:many relationship to both sides.

The advantage: You can add administrative information as validity, various timestamps, ...
and other stuff related to this "link".
But it is an additional persistent class with all pros and cons. So I feel this to be heavy.
The class may look like this:

Class Sample.Jobs Extends %Persistent  [Final]
{
Property Status As %Boolean;
Relationship Company As Sample.Company [ Cardinality = one, Inverse = Slot ];
Relationship Employee As Sample.Employee [ Cardinality = one, Inverse = Jobs ];
Index EmployeeIndex On Employee;
Index CompanyIndex On Company;
Index StatusIndex [Type = bitmap];
}

The lightweight solution:
If you are just interested if there is a relations ship or not and nothing else then you
can avoid the third class by using an array on both ends.
It's not more effort than the docking point of the Caché relationship but without
"supervisor"  in between.

all you need is

Class Sample.Employee Extends Person
{
Property Slot As Array of Company;
Class Sample.Company Extends %Persistent
{
Property Slot As Array of Employee;

Using the  related %Id() as key with the array prevents duplicates.  

The last variant allows also you to let N or M be zero.
To express one sided termination too.
 

Discussion (7)5
Log in or sign up to continue
Evgeny Shvarov · Feb 27, 2018

Hi, Robert!

Thanks for sharing the approach to this sensitive topic. I have a question: does this approach provide any object-access methods which will give you iterator on all Companies for a given Employee and vice versa?

And if there is an iterator, can you navigate through ids only or for an indexed property (Company.Name, Employee.Surname) as well?

0 0
Robert Cemper · Feb 27, 2018

Hi, Evegeny!
for both variants you work along the array by GetNext() method
for each employee you have an array with the OREFs of the company. So you have the full object at hands.
similar the opposite direction from company to employee and employee->nae in SQL or employee.name

And as we always have a Collection type Array indexing is simple either by (KEY) or (ELEMENT). whether you  need the ID or the OREF
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_indices#GSQLOPT_indices_collections

The only tricky action is DELETE: there you have to have a method to "DisRelate" before delete.
But real commercial systems never delete anything. Just set a flag "isDeleted". Which is much easier for any "undo" action. 

0 0
Robert Cemper · Feb 28, 2018

Hi Alexey,
You hit the point: "lightweight"  just documents the relation.  Full stop. No further service.
You have to service (ie. Delete) it at both ends.
If you use a serial object with OREF + Status)  you still have to service both ends.

The "heavy" variant does it centralized at one place.
Though from storage point of view you move out the additional subscript level from array.

1 0