N:N Entity Relationships

Introduction

The N:N entity relationship is used to relate many records of one entity to many records of another entity.

Relational databases do not allow implementing a direct many-to-many relationship between two tables, therefore you cannot create a direct N:N relationship between two entities.

To create a N:N entity relationship, you will need to use an intersect/join entity (table) and create two one-to-many relationships between the two entities and the join entity:

Each record in a join table includes a match field (foreign key) that contains the value of the primary keys of the two tables it joins. The foreign key fields are populated with data when records in the join table are created from either table it joins.

Example of a N:N entity relationship is the relationship between employees and departments. Each department can have any number of employees working on a specific task. Also, an employee can work for multiple departments at the same time. Therefore, any number of departments or employees can simultaneously be linked to each other by creating a join table (Employee_Department) that links them using the Employeeid and the Departmentid.

Creating a many-to-many relationship

In Innovation Studio you can create a many-to-many relationship between two entities by adding a relationship referencing another entity and entering value 2 in the Relationship Type field.

When adding a Relationship referencing another entity you can choose how the system will behave when deleting records which belong to entities linked through relationship. To do so, you will have to enter one of the following values in the Relationship Constraint (0-none, 1-restrict, 2-cascade) field:

• 0 - There will be no restrictions on deleting entity records that have been linked by other entities. When deleting the entity record, the value null will be displayed in that field for all entities linked to that entity. For example, if deleting a product, then all the customers who had the deleted product will have the value "null" in the field corresponding to that specific product.
• Restrict – Restricts the deletion of entity records that have been linked by other entities. For example, you are not able to remove a product that is owned by a customer.
• Cascade – Allows the deletion of entity records, including recursive cascades.

Use case scenario

Consider a database for recording customer products over time. The database will contain two tables, as follows:

• The Account table used to keep the customer details. The table primary key is the Accountid column.
• The Product table used to keep the track of individual products. The table primary key is the Productid column.

A many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purchased by many customers.

To create a many customers-to-many products relationship, follow these steps:

1. From the menu, click Evolutive Data Core > Data Model Explorer. The Business Entities List page appears.
2. Search for the Product entity and double-click it. The Edit Business Entity page will be displayed.
3. Scroll-down to the Relationship Referencing section and click on the section header. The Relationship Referencing section expands.
4. On the top of the section, click the Insert button. The Add Relationship page will be displayed.
5. Enter the relationship properties as follows:

   Property	Value
   Name (only for view)	Product_Name. The value in this field should follow this naming convention: concatenation of the two entity names, ChildEntity_PK_ParentEntity.
   Display Name	Customer
   Referenced Entity	Click the down arrow icon displayed on the right side of the field. A new page is displayed. Select Account and double-click it.
   Referencing Entity (only for view)	Product is automatically filled in this field (being the current entity you’re working on, the child entity).
   Relationship Type (only for view) (0-many to one, 1-one to many, 2-many to many)	Type 2.
   Relationship Constraint (0-none, 1-restrict, 2-cascade)	Type the desired value (0, 1 or 2) based on your business needs.

   

6. At the top-right corner of the page click the Save and close icon. The lookup attribute will be saved.

The many-to-many relationship is provisioned in the database and becomes visible in the user interface. Table Product_Account will be the link table between Account and Product.

To see the details of the relationship entity, go to the Entities list (Evolutive Data > Data Model Explorer), search for the Product_Account entity and double-click it. The Edit entity page will be displayed.

Scroll-down to the Data Model section and click on the section header. The Data Model section expands. It contains the relationship entity primary key attribute (Product_Accountid) and two foreign keys (lookup attributes) to the other two entities (Productid and Accountid):

The newly created relationship entity is displayed on both entities (Product and Account), as follows:

• On the Product entity the relationship entity (Product_Account) automatically created by the system is displayed in the Relationship referencing section: Both Product and Account entities reference the Product_Account table in the database.

  

• On the Account entity the relationship entity (Product_Account) is displayed in the Relationship referencing section. The referenced entity the Product_Account join table, corresponding to the many-to-many relationship between the Product and Account entities.

  

This is how the Product and Account tables will look like in the database when filled with data: