M:N Relationship

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

  • + 6
  • 0
  • 509
  • 8


Of course!
For the "heavy" variant the class is projected as any other table in Caché.
for the "light" variant the array is projected as subtable   Tablename_ArrayName
Implicit join using -> is also available. No limits!


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?

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

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. 



Hi Robert,

But real commercial systems never delete anything. Just set a flag "isDeleted"

Let us discuss a case when an employee has left a company and joined a new one.
Where would you place this flag in The lightweight solution?

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.

Taking in account that real commercial systems should never delete anything, the lightweight solution can't be recommended for use in such kind of systems. I'm just curious: when it can be helpful?

"lightweight" is the theoretical approach for object purists that want to have just 1 object covering the world and don't care about practical use. They often were moaning "oh only 1.n relationship".  
 As you noted "heavy" allows real relation management. And could be faster in some cases.