The type of key that is generated in the PDM depends on the cardinality and type of dependency defined for a relationship in the CDM. Primary identifiers generate primary and foreign keys. Other identifiers that are not primary identifiers generate alternate keys:
A primary key is a column or columns whose values uniquely identify a row in a table.
A foreign key is a column or columns that depend on and migrate from a primary key column in another table.
An alternate key is a column or columns whose values uniquely identify a row in a table, and is not a primary key.
In independent one-to-many relationships, the primary identifier of the entity on the one side of the relationship is generated as a:
Primary key in the table generated by the entity on the one side of the relationship
Foreign key in the table generated by the entity on the many side of the relationship
The following CDM shows an independent relationship. Each division contains one or more employees:
The following PDM will be generated:
Table |
Primary key |
Foreign key |
---|---|---|
Division |
Division number |
— |
Employee |
Employee number |
Division number |
In dependent relationships, the primary identifier of the nondependent entity is generated as a primary/foreign key in the table generated by the dependent entity. The migrated column is integrated into the primary key if it already exists.
The following CDM shows a dependent relationship. Each task must have a project number.
The following PDM will be generated:
Table |
Primary key |
Foreign key |
---|---|---|
Project |
Project number |
— |
Task |
Project number/Task number |
Project number |
In independent many-to-many relationships, the primary identifiers of both entities migrate to a join table as primary/foreign keys. The CDM below shows an independent relationship. Each employee can be a member of one or more teams, and each team can have one or more employees as members.
The following PDM will be generated:
Table |
Primary key |
Foreign key |
---|---|---|
Team |
Team number |
— |
Employee |
Employee number |
— |
Member |
Team number/Employee number |
Team number/Employee number |
In independent one-to-one relationships, the primary identifier of one entity migrates to the other generated table as a foreign key.