Implementing Relationships in Tables | Databases

The clean approach

Given a relationship $R$ between entity tables $S(\underline{Z}, W)$ and $T(\underline{X}, Y)$ , the default approach is to create a separate table:

$R(X, Z, U)$

where $U$ are any relationship-specific attributes (e.g., a salary paid to an actor for a particular film). Both $X$ and $Z$ serve as foreign keys to $T$ and $S$ respectively.

Many-to-many (M:N)

For a many-to-many relationship, the separate-table approach is mandatory. The key of $R$ is $(X, Z)$ — both foreign keys together form the primary key. If the relationship has attributes $U$ , they are included as non-key columns.

Example: Directed(movie_id, person_id) with key (movie_id, person_id).

One-to-many (1:M)

For a one-to-many relationship, we have two options.

Option 1: Separate table. Create $R(X, Z, U)$ as above. The key is $X$ (the “many” side), since each entity on the many side participates in at most one relationship. Requires a JOIN to navigate.

Option 2: Expand the “many” side table. Expand $T$ to $T(\underline{X}, Y, Z, U)$ , adding the foreign key $Z$ and relationship attributes $U$ directly. Rows that do not participate in the relationship store NULL in $Z$ and $U$ .

	Separate table	Expanded table
JOINs needed	Yes, for every navigation	No
NULL values	None	For rows not in the relationship
Normalisation	Clean	Violates normal forms (see below)
Update complexity	Insert/delete into the relationship table	Update a single column

The expansion approach saves a join but introduces NULL columns and violates normalisation principles: $Z$ (the foreign key) functionally depends on $X$ (the key of the expanded table), but $Z$ is not itself a key. A later lecture covers normal forms in detail.

One-to-one (1:1)

For a one-to-one relationship, either side can absorb the foreign key. Choose the side where NULL values are less common.

Relationship attributes

When a relationship carries its own attributes, the clean approach uses a separate relationship table:

E/R diagram: Actor(id, name) — PerformsIn(character, salary) — Movie(id, title)

Relational schema:

Actors(actor_id, name)
Movies(movie_id, title)
PerformsIn(actor_id, movie_id, character, salary)

The character and salary columns belong to the relationship, not to either entity. Placing them in Actors or Movies would be incorrect: an actor can play different characters in different films for different salaries.

Merging multiple relationships

Sometimes multiple relationships between the same entity sets are merged into a single table with a type discriminator. The course example is has_position:

has_position(movie_id, person_id, role, character_name, position_in_credits)

This single table combines what could have been five separate tables: Directed, ActedIn, Produced, Composed, Wrote. The role column is a type discriminator (e.g., 'director', 'actor', 'producer').

Advantages:

One table instead of five — simpler conceptual model.
Queries across all roles require only one table.
Adding a new role type requires only a new value in the role column, not a new table.

Disadvantages:

character_name is NULL for non-actor roles; position_in_credits might be NULL for some roles.
Redundancy: if we know role = 'director', we can deduce that character_name is NULL. This is a violation of normalisation principles — the value of one non-key column (role) determines whether another column (character_name) must be NULL.
Foreign key constraints cannot easily express “an actor must have a character name” at the schema level. This logic moves to application code or triggers.

The type-tag redundancy problem

Consider two separate relationship tables:

R(X, Z, U)     -- e.g., directed roles
Q(X, Z, V)     -- e.g., acted roles

Now squash them into one:

RQ(X, Z, type, U, V)

where type indicates whether this row belongs to $R$ or $Q$ . This introduces a redundancy: the type value determines which of $U$ or $V$ is meaningful. If type = 'R', then $V$ must be NULL. If type = 'Q', then $U$ must be NULL. The fact that $U$ is NULL is not independent data — it is computable from type. This violates the principle that data should not be derivable from other data.

Summary table

Relationship type	Default implementation	Key of relationship table	Alternative
M:N	Separate table	$(X, Z)$ (composite)	None required
1:M	Separate table or expand the “many” side	$X$ (the many side)	Expand $T$ : $T(\underline{X}, Y, Z, U)$
1:1	Foreign key on either side	Either $X$ or $Z$	Expand either table
With attributes	Separate table	As above	Attributes placed in the table that makes semantic sense

Summary

The clean approach creates a separate table for each relationship.
M:N relationships must use a separate table with a composite key.
1:M relationships can use a separate table or expand the “many” side, trading JOIN cost against normalisation.
Merging multiple relationships into one table with a type tag reduces table count but introduces redundancy and weakens constraint enforcement.
Relationship attributes belong in the relationship table, not in entity tables.