Many to many relationship best practice

#1

What is the best practice to setup a many to many relationship in C3IoT Type System ?

Let say I have Person and Address. I would like too be able to get all addresses on a which a person is living and vice versa all people living on a certain address.

Thanks !

0 Likes

#2

One option is to create a type which represents the relationship. In your example, you could build types like this, using foreign key references and stored calcs.

 entity type Person {
        relationships: [PersonAddress] (person)
        addresses: [Address] stored calc "relationships.address"

 }
 entity type Address {
     relationships: [PersonAddress] (address)
     people: [Person] stored calc "relationships.person"
 }

 entity type PersonAddress {
     person: Person
     address: Address
 }

You may also want to look at the type “TimedRelation” which supports relationships that have a start/end (for example, people might move from place to place)

Finally, if you are building on top of the ‘foundation’ package, note that we specify this relationship through FixedAssets (buildings, essentially) . So the data model is something like Person -> Owner -> FixedAsset -> Location -> Address where several links along that chain are timed.

0 Likes

#3

Thanks Riley, very clear.
Why do you use a stored calc? I presume you want to avoid join operations at runtime ? On the other hand will the refresh of the stored calc or even storage not be too heavy ?

0 Likes

#4

The cost of populating the stored calc will be the same as the run-time join, so you are just moving the time spent on calculating the value from query time to data load time. If you have many more queries than data loads, this is a net performance gain (at the cost of one extra column in the DB). If your data changes much more frequently than you query it, then you may consider not creating the stored calc.

1 Like