Relational Database and Normalization

One-to-One

A One-to-One relationship is where each record in one table corresponds to a single record in another table. Unlike One-to-Many, where one record from a table can relate to multiple records in another, in One-to-One, each pair of records is uniquely linked. This relationship involves two key connections:

• The first table has a foreign key that points to the primary key of the second table.
• Conversely, the second table has a foreign key that points to the primary key of the first table.

Note

The One-to-One relationship hinges on the foreign key. This foreign key should include the “unique=True” parameter.

In certain cases, one record can only exist if another does, but the latter can stand alone. This type of link is termed One mandatory to one.

Consider the following diagram:

Another illustration:

One mandatory to one: Every Passport must be tied to a specific Person. However, a Person can exist without a passport, like in the case of children.

Many-to-Many

A Many-to-Many relationship involves two tables that can have multiple corresponding records in each other, often mediated by an intermediary table. In contrast to One-to-Many, where one record in a table corresponds to multiple records in another, Many-to-Many allows for multiple records in both tables to interrelate.

This intermediary table should have three columns: id (an identifier for each relationship), first_table_id, and second_table_id. The first_table_id and second_table_id columns act as foreign keys that link back to their respective tables.

For example, consider a car-sharing service where both driver_id and car_id act as foreign keys. In the “driver-car” table, five distinct relationships are illustrated:

• (id 1 and 2) - Driver John (driver_id=1) drove the cars Subaru (car_id=1) and BMW (car_id=3).
• (id 3, 4, and 5) - Driver Emma (driver_id=2) drove the cars Subaru (car_id=1), Mercedes (car_id=2), and BMW (car_id=3).

Multiple drivers drove various cars: both John and Emma drove both the Subaru and BMW.

Database schematic:

Typically, these intermediary tables are omitted from diagrams. As evident above, the driver-car table isn't displayed.