Best way to implement a one-to-one relationship
I know that 1-1 relationships are not officialy supported by intersystems cache/iris so I want to know the best way to store data with this kind of data model.
Currently I have two classes that where implemented some time ago:
Table A with a relationship type one on table B
Table B with a relationship type one on table A
To compile I have a double compile with qualifyer U.
What is the best way to implement a data model with 1-1 relationships?
Thank you
The most simple way to do it:
in class Table.A have
Property TableB as Table.B;
in class Table.B have
Property TableA as Table.A;
in your code it may look like this:
set objB=##class(Table.B).%OpenId(99)
set objA.TableB=objB
set objB.TableA=objA
do objA.%Save(), objB.%Save()
You are free to index properties TableA or TableB according to your needs
and you can also use Implicit JOIN between these tables.
In this case it's also important to use foreign keys (see: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=ROBJ_classdef_foreignkey) to ensure referential integrity. (Especially worth considering: what happens to the associated record in B/A when the other is deleted?)
Another option is to use a one-to-many relationship with a Unique index on the "many" side:
Class DC.Demo.OneToOne.ClassA Extends %Persistent { Relationship ClassB As DC.Demo.OneToOne.ClassB [ Cardinality = many, Inverse = ClassA ]; } Class DC.Demo.OneToOne.ClassB Extends %Persistent { Relationship ClassA As DC.Demo.OneToOne.ClassA [ Cardinality = one, Inverse = ClassB ]; Index ClassAIndex On ClassA [ Unique ]; }Great idea !
ONE -> MANY but MANY is UNIQUE
The Idea of a one to one is to create a property of the class in each other.
The SQL will allow you to build any table by displaying the ID (not the object)
A Many to Many will not allow the SQL to work
Thus
Class one has a property
Property objPointToTwo as Classname;
Class two has a propery
Property objPointToOne as Classname;
The %Resultset will allow
select ID,objPointToTwo
from ClassName
So, to sum up, the best strategy would be to define properties on each of the tables and then also FKs to ensure referential integrity