Relationships to a PDM

Previous  Index  Next  How To Use

A relationship will generate a Foreign Key or Referential Constraint in a Physical Data Model.

 

How exactly this constraint is generated depends on the relationship type.

 

1.one-to-one relationship

2.one-to-many or many-to-one relationship

3.many-to-many relationship

 

 

If you selected the attributes that are part of the relationship, no additional columns will be added to the tables involved. If you leave the attributes set to [default], new columns will be created and added to the tables.

 

When creating new columns, these will be NOT NULL, depending on the "required" status of the relationship.

 

 

One-to-one relationship

With a one-to-one relationship, it depends on the "direction" or "dominant role" from which table to which table the constraint is "pointing". If you did not define the attributes that make up the relationship yourself, the dominant role is used to decide in which table to add the foreign column.

 

Take a look at the following example.

 

One-to-one relationship, Person as a "base class" for Employee

 

In the above screenshot, you can say that "Person" is over "Employee", without a person, there will be no employee. This will generate a foreign key constraint as follows:

Foreign Key from Employee to Person

 

This generates the column PERSONID into table Employee. If the relationship is an "identifying" relationship, the newly generated column will be added to the primary key of the child table.

 

Back to Top

 

 

One-to-many or many-to-one relationship

These relationships are the same, only the direction differs. This is a common relationship, see the example below.

 

A department has zero or multiple employees, while each employee works at 1 department

 

Each employee works at a department, this is a required item. This means that the column for Department will be NOT NULL. It's a non-identifying relationship, so the new column will not be part of the primary key of the Employee table.

 

The result is a simple foreign key constraint:

 

A foreign key from Employee to Department

 

Back to Top

 

 

Many-to-many relationship

A many to many relationship cannot be generated by creating a foreign key from the source to destination table. In the example below, an "Online Order" can contain multiple "Products", while any "Product" can be used by multiple "Online Orders".

 

Example of a many-to-many relationship

 

The many-to-many relationship will be "solved" by using an intermediate table named after the relationship that contains both the identifier from the source and destination table. This combined set of identifier attributes now forms the primary key of the intermediate table.

 

Generated tables and foreign key constraints

 

Back to Top