Skip to Content
Technical Articles
Author's profile photo Xiao-fei Song

CDS entity relationship modeling using CDS Graphical Modeler

In this blog post, we would like to demonstrate how to perform complex CDS entity relationship modeling using the CDS Graphical Modeler.

CDS Managed Association and Composition

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.

Setting up the CDS Graphical Modeler

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/04/23/an-introduction-to-cds-graphical-modeler-for-sap-business-application-studio/.

 

Association Relationship Modeling

Once the project is setup, you can open db/data-model.cds using the CDS Graphical Modeler.

We should like to use the predefined named aspects defined in common.cds on the newly created entities, so let’s import the CDS common.cds before we start doing the entity relationship modeling.

In order to have the common.cds in the CAP project, open a terminal and build the project dependencies by executing “npm install”. After the build is successful and node_modules directory is created, click “Import” button to open the file selection dialog and choose node_modules/@sap/cds/common.cds:

Select common.cds in the file selection dialog and click “Select CDS File” button to dismiss the dialog. Then you will see the dialog to select the available named aspects:

Select cuid, managed and temporal in the dialog:

Click “Select” button to dismiss the dialog. These are the 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 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.

Select cuid and managed named aspects, and click “Select” button to close the dialog:

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 simplified 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:

This time:

  • 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:

Composition Relationship Modeling

Now we can take a look how we can model entity relationship using composition. If we have below CDS mode Books and BookCovers:

Let’s create a composition relationship between the Books and BookCovers:

Let’s assume one book has multiple covers, so we’ll choose:

  • Relationship type: Composition
  • Multiplicity: Many
  • Property Name: covers
  • Target Entity Type: my.bookshop.BookCovers
  • Backlink Property: book

Press “Create” button to dismiss the dialog, and you will see the composition relationship has been created:

Checkout the CDS file to confirm the content:

namespace my.bookshop;

using
{
    cuid,
    managed
}
from '@sap/cds/common';

entity Books
{
    key ID : Integer;
    title : String;
    stock : Integer;
    covers : Composition of many BookCovers on covers.book = $self;
}

entity BookCovers : cuid, managed
{
    book : Association to one Books;
}

You can see the composition relationship between Books and BookCovers relies on a managed association that represented by “book” property in BookCovers entity, and the “book” property is actually the backlink property we just specified in the new relationship dialog.

We can now create a different composition relationship called managed composition, in which case the many side must be an named aspect. So let’s create one first:

Click “+” button and choose “Add aspect” menu item:

In the popped up dialog, enter “Pages” as the aspect name:

Click “Create” to dismiss the dialog, and add an ID as the key property to this aspect:

Now let’s create a managed composition relationship between entity “Books” and named aspect “Pages”:

In the managed composition relationship, you don’t need to specify the backlink property. Click the “Create” button and create the relationship:

We can take a look at CDS file again and check its content:

namespace my.bookshop;

using
{
    cuid,
    managed
}
from '@sap/cds/common';

entity Books
{
    key ID : Integer;
    title : String;
    stock : Integer;
    covers : Composition of many BookCovers on covers.book = $self;
    pages : Composition of many Pages;
}

entity BookCovers : cuid, managed
{
    book : Association to one Books;
}

aspect Pages
{
    key ID : String;
}

Conclusion

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. And we also demonstrate how to create to-many composition and managed composition relationship in the blog post.

References

Assigned tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Aman Garg
      Aman Garg

      This is very helpful. Graphical modeler seems easy to use. Thanks a lot for sharing.

      - Aman Garg

      Author's profile photo Xiao-fei Song
      Xiao-fei Song
      Blog Post Author

      Thanks Aman. In my next blog post, I will be demonstrating how to create projections using the CDS Graphical Modeler.

      Author's profile photo Rajat Agrawal
      Rajat Agrawal

      Interesting  article. Thanks for sharing.

      Author's profile photo Xiao-fei Song
      Xiao-fei Song
      Blog Post Author

      Thanks Rajat. In my next blog post, I will be demonstrating how to create projections using the CDS Graphical Modeler.

      Author's profile photo Patton Li
      Patton Li

      @Xiao-fei, I think we need to add a "npm install" step, or the "node_modules" folder is not existed.

      Author's profile photo Xiao-fei Song
      Xiao-fei Song
      Blog Post Author

      Hi Patton,

      Thank you for your feedback. I have updated the blog post.

      Regards,

      Xiao-fei