One-to-One and Many-to-Many
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:
![](https://codefinity-content-media-v2.s3.eu-west-1.amazonaws.com/courses/5ac24d9d-4a16-45b3-8856-07dec028c5e9/Section+2/O-O_Person-Passport_var1%404x-8.png)
Another illustration:
![](https://codefinity-content-media-v2.s3.eu-west-1.amazonaws.com/courses/5ac24d9d-4a16-45b3-8856-07dec028c5e9/Section+2/O-O_Person-Passport_var2%404x-8.png)
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.
![Driver and Car relations](https://codefinity-content-media-v2.s3.eu-west-1.amazonaws.com/courses/5ac24d9d-4a16-45b3-8856-07dec028c5e9/2-4/M-M_driver_car_grouped.png)
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:
![](https://codefinity-content-media-v2.s3.eu-west-1.amazonaws.com/courses/5ac24d9d-4a16-45b3-8856-07dec028c5e9/Section+2/M-M_diagram%404x-8.png)
Typically, these intermediary tables are omitted from diagrams. As evident above, the driver-car table isn't displayed.
¿Todo estuvo claro?
Contenido del Curso
Relational Database and Normalization
3. Normalization and Normal Forms
Relational Database and Normalization
One-to-One and Many-to-Many
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:
![](https://codefinity-content-media-v2.s3.eu-west-1.amazonaws.com/courses/5ac24d9d-4a16-45b3-8856-07dec028c5e9/Section+2/O-O_Person-Passport_var1%404x-8.png)
Another illustration:
![](https://codefinity-content-media-v2.s3.eu-west-1.amazonaws.com/courses/5ac24d9d-4a16-45b3-8856-07dec028c5e9/Section+2/O-O_Person-Passport_var2%404x-8.png)
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.
![Driver and Car relations](https://codefinity-content-media-v2.s3.eu-west-1.amazonaws.com/courses/5ac24d9d-4a16-45b3-8856-07dec028c5e9/2-4/M-M_driver_car_grouped.png)
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:
![](https://codefinity-content-media-v2.s3.eu-west-1.amazonaws.com/courses/5ac24d9d-4a16-45b3-8856-07dec028c5e9/Section+2/M-M_diagram%404x-8.png)
Typically, these intermediary tables are omitted from diagrams. As evident above, the driver-car table isn't displayed.
¿Todo estuvo claro?