Many to many relationship best practice

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 !

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.

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 ?

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

From what I can tell the stored calc in this example would need a schema name to provision successfully. So it would change to:

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

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

 entity type PersonAddress {
     person: Person
     address: Address
 }

@rileysiebel Would that change your performance advantages at all? I ask because a schema name creates a new table rather than just adding a new column. See: What is the purpose of the schema name?

Seems like you should be able to avoid expensive stored calc and instead do people: [Person] calc 'relationships.person'. Either way you’re doing a join if you include that field.

@trothwein can you recommend best practice?

@yaroslav Though that would make it cleaner from a fetch query perspective it would still have to traverse the FK links at runtime which the calc field was trying to optimize against.

@brett.thompson I’m not sure what you are trying to accomplish with this data model. It looks like ‘Address.relationships’ is endlessly recursive in that each relationship is a PersonAddress, which has an Address’which has relationships, which have PersonAddresses, which have Addresses, which have relationships…

This was an example of how to implement many to many relationships. So we have a Person that has many addresses but each address might have more than just that one person. If there is a better idea for accomplishing this I would love to hear it!

As for what I’m trying to accomplish with the stored calcs, I would like to shorten the FK relationship length to optimize queries. Assume you have multiple many to many relationships then the query can become unreasonably long.

@brett.thompson Got it. In that case, I would say that the stored calcs are totally unnecessary. They are actually wasteful in that each of those stored calcs will essentially be stored in a table that looks surprisingly like the table for ‘PersonAddress’. As such, retrieving via the fkey vs. the stored calcs will each have to join/query to a table other than the root table to get the “other” values. The only difference will be that with the stored calcs you will be able to access the addresses for a Person via “addresses” vs. “relationships.address”. Underneath, both will have the same impact on the db (assuming you have an index on the 2 fields in ‘PersonAddress’). Also, with the stored calc, that will take up more space and cause additional unnecessary processing when creating/updating ‘PersonAddress’ entries.

@trothwein I did some testing before originally posting as I was concerned that the stored calc would end up copying the whole table that is being calculated. I was surprised to find that was note the case. In a larger example where Person and Address had data columns in them the additional table was found to only have 5 column which looked very generic to me.

Person.schema().allTables() lists a ADDR table:

[
	{
		"type": "SqlColumn",
		"name": "TENANT_TAG_ID",
		"dataType": 4,
		"vendorDataType": "numeric",
		"length": 0,
		"precision": 20,
		"scale": 0
	},
	{
		"type": "SqlColumn",
		"name": "RID",
		"dataType": 12,
		"vendorDataType": "varchar",
		"length": 128,
		"precision": -1,
		"scale": -1
	},
	{
		"type": "SqlColumn",
		"name": "KEY",
		"dataType": 4,
		"vendorDataType": "numeric",
		"length": 0,
		"precision": 20,
		"scale": 0
	},
	{
		"type": "SqlColumn",
		"name": "RTIMESTAMP",
		"dataType": 93,
		"vendorDataType": "timestamp",
		"length": 0,
		"precision": 0,
		"scale": -1
	},
	{
		"type": "SqlColumn",
		"name": "VALUE",
		"dataType": 12,
		"vendorDataType": "varchar",
		"length": 128,
		"precision": -1,
		"scale": -1
	}
]

In the simple example here the stored calcs to shorten query times is not very useful as it is easy to index everything on the PersonAddress table. For my implementation we have a much more complex scenario were we have 10 tables that are all linked with different one to many and many to many relationships. This causes some queries to be fairly slow. For example one query could look like this:

MyType.fetch({ include: 'typeA.typeB.typeC.typeD' }) where many of those fields could be arrays. It also makes it difficult to do evaluates, such as sums, across typeD from MyType. I was hoping we could use stored calcs to shorten the FK length and, in the above example, store the array of typeD in the MyType definition directly.

If I need to put a better example with c3typ definitions I can do that for clarity.

Well, like I said, from a number of tables accessed perspective, the stored calc doesn’t buy you anything. When traversing deeply across multiple relationships, it can indeed slow things down. Typically we used stored calcs in that case to totally jump over one or more of the referenced tables to denormalize that result.

1 Like