Generating PDM table keys from CDM entity identifiers

Chapter 9 Working with Data Models

Generating PDM table keys from CDM entity identifiers

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.

Independent one-to-many relationships

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

Dependent one-to-many relationships

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

Independent many-to-many relationships

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

Independent one-to-one relationships

In independent one-to-one relationships, the primary identifier of one entity migrates to the other generated table as a foreign key.