Relationships in Common Data Service
During recent weeks I have taken the opportunity to familiarise myself with some of Microsoft’s new technologies. In playing with, and trying to understand, the Common Data Service (CDS), and its use with PowerApps, one question keeps cropping up. What is the mechanism for assigning primary and foreign keys when creating a relationship in CDS? This question highlights the required mindset change when a database designer first starts to get to grips with CDS.
Anyone with a passing familiarity with tables in a database will understand the idea of a one-to-many relationship. This is where a record in one table is linked to multiple records in another table. A typical example is a lookup, with product attributes (cost, colour size etc.) linked to multiple rows in a sales history. To look at it from the other side, the sales history might have multiple rows where the same product has been sold, and all are linked to the same single row in the product lookup. This is standard in relational data, and the relationship is defined by a primary key (in the lookup) and a foreign key (in the history). Star schema anyone?
Entities in the CDS look and behave a lot like tables. And relationships can be defined to link entities in a one-to-many fashion. But CDS entities have been designed to encapsulate business logic and processes, so that everywhere the data is used, regardless of app or process, the same rules apply. CDS relationships are no different. In CDS you don’t need to define a relationship by linking a primary key field in a parent entity with a foreign key field in a child entity. You just add a relationship, citing which is the parent and which is the child, and then CDS will manage the link.
Once the relationship has been created, you will find that a field reference to the lookup entity has appeared in the history entity. CDS will manage the relationship in terms of linking records, so that you can then essentially choose any field from the lookup entity when browsing data from the history. This can be very useful when creating canvas apps in PowerApps. Where it can get a little complex is when the behaviour of the relationship needs to be defined. In a nutshell, you need to decide what happens when data is deleted from the parent entity. If there are associated records in the child entity, do these get deleted, do they get unassigned (to blank), or do you prevent deletion of the lookup record until there are no related records?
In this way you are in control of the data integrity. In my experience, it is simplest to set things up so that no parent record can be deleted until there are no more related child records. You should also understand the implications when importing existing data into both the parent and child CDS entities. I’m reliably informed there is a wizard in the PowerApps Admin portal that can help maintain the child-parent relationship when importing data, but it won’t happen on its own. In other words, don’t rely on historic primary and foreign keys to maintain a relationship. It seems the implication is that you should build your entities and associated relationships. Then, enter the lookup values, followed by creating history records that refer to the new lookup values.
So, there you have it. Hopefully that explains how a relationship differs in CDS from those in relational data.
