How to create Identifying Relationship with Cardinality of many to many relationship:
If a foreign key column/multiple foreign key columns referencing some table, becomes a part of the primary key, and then it is an identifying relationship.
Employee and PROJECTS Table association:
Since the relationship is many to many with Employee_No (Employee Table) and Project_Code (Project table), we go with Identifying Relationship. In Identifying relationship, primary key migrates from the parent table to child table and it is a part of the primary key in Child table. Foreign Key relationship is a thicker line.
- Create table EMPLOYEE_PROJECT with no column.
- Click identifying relationship in the tool bar.
- Click Employee table first and then EMPLOYEE_PROJECT table. The line is not dotted and thicker. Employee_No column is created in EMPLOYEE_PROJECT table.
- Similarly click identifying relationship in the tool bar. Click Project table first and then EMPLOYEE_PROJECT table. The line is not dotted and thicker. Project_Code column is created as primary key in EMPLOYEE_PROJECT table.
The example above talks about many to many relationships and it involves two tables Employee and Projects to form a third table Employee_Projects.
In some scenarios, if a primary key of the table is not able to identify uniqueness of a record in a table, then they add one more column from another table as a primary key with identifying relationship.
Save the data model with a meaningful name and save it either in your local directory or in a shared directory.
This data model is stored as “Data_Modeling_Relationship.txl”. On the tool bar, you can see the save menu.
Finally an image of the completed data model “Data_Modeling_Relationship” looks like this.