1:N Entity Relationships

Introduction

A one-to-many relationship is not a property of the data, but a property of the relationship itself.

The one-to-many relationship is only a principle of relational database design. It cannot be explicitly defined in the database structure, but created and enforced by the use of relationships between a parent entity and child entities. One record in a table can be associated with one or more records in another table:

In a one-to-many relationship, the parent entity might have zero, one or multiple child records while a child entity can have one and only one parent entity record.

In Innovation Studio, you can create one-to-many relationships by adding a lookup attribute on the child entity to reference the parent entity. When adding a lookup attribute, the Lookup Relationship Name field will be automatically filled in by the system with the concatenation of the two entity names, following this naming pattern: ChildEntityName_ParentEntityName. It allows you to display on entity the following:

  • the lookup attribute referencing the parent on child data form
  • the list with child records on parent entity

Creating a one-to-many relationship

This section provides the use case scenario for recording customer sales transactions over time. The database will contain two tables, as follows:

  • The Investor table used to keep the customer details. The table primary key is the Investorid column.
  • The Assets table used to keep the track of individual investor assets. It contains the assetOwner foreign key which references the Investorid column in the Investor table to track the customer to whom the asset belongs.

A single asset can only be associated to one investor while one investor can have many assets. The logic is defined by the one-to-many relationship:

To create a one investor-to-many assets transactions, you need to create a lookup attribute on the Assets entity. To do so, follow these steps:

  1. From the menu, click Evolutive Data Core > Data Model Explorer. The Business Entities List page appears.
  2. Search for the 'Assets' entity and double-click it. The Edit Business Entity page will be displayed.
  3. Scroll-down to the Data Model section and click on the section header. The Data Model section expands.
  4. Click the Insert button. The Add Attribute page will be displayed.
  5. Enter the attribute properties as follows:
    6. Property Value
    Name assetOwner
    Attribute Type From the drop-down list, select Lookup.
    Display Name Asset Owner
    Lookup to Entity Click the down arrow icon from the right side of the field. From the newly displayed page, select Investor and double-click it.
    Lookup Relationship Name assets_assetOwner_Investor is automatically filled in this field after making the entity selection in the Lookup to entity field.
  6. At the top-right corner of the page click the Save and close icon. The lookup attribute will be saved.

The newly created relationship is displayed on both entities, as follows:

  • On the 'assets' entity the relationship is displayed within the Relationships Referenced section, as this section lists the relationships where the current entity is a child.

  • On the 'Investor' entity it is displayed within the Relationships Referencing section, as this section lists the relationships where the current entity is a parent.