Table Relations in Relational Databases

NDO supplies a database table for every class. Thus the different relation types between classes can be mapped to relations between tables. In relational databases such a table relation is represented with primary and foreign keys. A primary key is a special column of a table where all values are different from each other so that the rows of the table can be unambiguously distinguished. A foreign key is a column of a table that holds values matching a primary key value of another table.

Three cases can be distinguished if table A has a relation with table B.

1:1 relation:

The foreign key is in table A and refers to a single row in table B.

1:n relation:

The foreign key is in table B and refers to a single row in table A. Thus it is possible to associate more than one row of table B to table A.

n:n relation:

An intermediate table must be created for this case in which each row holds both a foreign key to table A, and one to table B. This allows associating several rows of table B to one row of table A and vice versa.

Note that 1:1 and 1:n relations can also be implemented with the help of intermediate tables. The n:1 relation that sometimes appears in literature is just a mirrored 1:n relation.

Let’s review the tutorial code: The Employee table has a 1:n relation to the Travel table. Thus the travel table has a foreign key (for example IDEmployee) allowing the assignment of more than one row of the Travel table to a row of the Employee table.  Relations in relational databases are always implicitly bidirectional. As outlined before, we can use the IDEmployee column to find all Travel rows assigned to a specific Employee row. On the other Hand, all Travel objects can use the same IDEmployee column to find their parent object, thus establishing a 1:1 relationship.

To sum it all up, NDO can map a bidirectional relation between two classes with a cardinality of 1 at one side and a cardinality of n at the other side to a table relation using only one foreign key.