In this blog post, we would like to demonstrate how to do complex CDS entity relationship modeling using the CDS Graphical Modeler. In CDS, entities can have relationship called associations and compositions. And we can model the entity relationship using the CDS Graphical Modeler for managed associations and compositions. The “managed” relationship would mean the end user would not need to deal with join conditions based on the foreign key explicitly, but only focuses on the entity relationships themselves and CDS would manage the relationship for you. For more details about CDS entity relationship please refer to Associations & Compositions.
CDS Graphical Modeler is an extension of SAP Business Application Studio. For more details about how to setup dev space, please refer to https://blogs.sap.com/2021/03/24/extending-sap-odm-model-using-sap-cds-graphical-modeler/.
After the dev space has been created, you would need to create a CAP project using the project template. Please also refer to https://blogs.sap.com/2021/03/24/extending-sap-odm-model-using-sap-cds-graphical-modeler/ for more details.
Once the project is setup, you can open db/data-model.cds using the CDS Graphical Modeler. Please drag common.cds into the graphical modeler to import this CDS file into the data model.
There are some predefined named aspects we can include to the newly created entities so that we don’t need to spend time setting up keys and properties.
Now, let’s create an Authors entity using the CDS Graphical Modeler:
And once the “Authors” entity is created, we can include a few predefined named aspects cuid and managed to this entity. Including cuid will basically include a “ID” key property so that you don’t need to create the key manually yourself.
After closing the dialog, you will see the Authors entity would include the properties that are inherited from the 2 named aspects:
Now let’s create another entity “Addresses”, in the same way as we created Authors:
We can then think about a simplied entity relationship for the 3 entities:
- Any book has one author.
- An author has multiple books.
- Any author has one address.
- Any address has one owner whose type is author.
Let’s first create the relationship from Books to Authors:
and the new relationship dialog shows up, and we can create a managed association for Books that points to Authors:
A few things to mention:
- Choose “Association” instead of “Composition” for this relationship because both Authors and Books are top level entities that should be able to exist independently.
- Leave the “Many” checkbox unchecked because we’re trying to create a managed to-one relationship from Books to Authors.
- Leave the backlink property field empty because we would expect in this case the foreign key would exist on the source side.
Click “Create” button and we would have created the to-one managed association for the Books entity:
We would now need to create the relationship from Authors to Books because we would expect this relationship is bi-directional so that the query would also be able to navigate from Authors to Books:
- Check “Many” checkbox, because we would expect one author would have multiple books.
- Select “author” property from the Books entity as the backlink property.
Clicking “Create” button to close the dialog and we have created a bi-directional relationship between Books and Authors:
and if you want to check the CDS file, you will see below content:
And we can do something similar between Authors and Addresses, so that we can have a to-one bi-directional relationship between the 2 entities:
And if we follow the similar approach by create more entities like Chapters, Covers, Publishers and Industries and try to setup relationship among those entities, we can very easily achieve this using the CDS Graphical Modeler:
And the CDS file would contain below content to reflect the entity relationship:
As a summary, in this blog post we demonstrate how to visually create CDS entities and setup complex relationship among those entities using the CDS Graphical Modeler without having to write a single line of CDS code. You can very quickly create a CDS entity and include predefined aspects including cuid, managed and temporal to create properties, and create managed relationship for those entities.