Relational Database Design Guide (7–9 of 15) [translation]
- Transfer
To be continued.
Previous Parts: 1-3 , 4-6
I have already shown you how data from different tables can be linked using foreign key relationships . You saw how orders communicate with customers by placing customer_id as a foreign key in the order table.
Another example of a one-to-many relationship is the relationship that exists between a mother and her children. A mother can have many children, but each child can have only one mother.
(Technically, it is better to talk about a woman and her children instead of a mother and her children because, in the context of a one-to-many relationship, a mother can have 0, 1 or many descendants, but a mother with 0 children cannot be considered a mother. But let's close eyes on this, okay?)
When one record in table A can be associated with 0, 1 or many records in table B, you are dealing with a relationshipone to many . In a relational data model, a one-to-many relationship uses two tables.
Schematic representation of a one-to-many relationship. The entry in table A has 0, 1, or the set of associated entries in table B.
If you have two entities, ask yourself:
1) How many objects and B can belong to object A?
2) How many objects from A can relate to an object from B?
If the answer to the first question is many , and the second one (or maybe none), then you are dealing with a one-to-many relationship.
Some examples of one-to-many relationships:
In this case, everything is so simple that only therefore can it be difficult to understand. Take the last example with houses. Indeed, there can really be any number of houses on the street, but each house on this street can have only one street (we don’t take houses that in practice belong to different streets, take, for example, a house in the center of the street). After all, this particular house cannot be in two places at the same time, on two different streets, and we are not talking about some kind of abstract house in general, but about a concrete one.
A many-to-many relationship is a relationship in which multiple records from one table (A) can correspond to multiple records from another (B). An example of such a connection is a school where teachers teach students. In most schools, each teacher teaches many students, and each student can be taught by several teachers.
The connection between the beer supplier and the beer they supply is also a many-to-many relationship. A supplier, in many cases, provides more than one type of beer, and each type of beer can be provided by many suppliers.
Please note that when designing a database, you should not ask yourself whether certain relationships exist at the moment, but whether relations in general are possible in the future. If at the moment all suppliers provide many types of beer, but each type of beer is provided by only one supplier, then you might think that this is a one-to-many relationship, but ... Do not rush to implement a one-to-many relationship in this situation. There is a high probability that in the future two or more suppliers will supply the same type of beer and when this happens your database - with a one-to-many relationship between suppliers and types of beer - will not be prepared for this.
A many-to-many relationship is created using three tables. Two tables - “source” and one connection table. The primary key of the join table A_B is composite . It consists of two fields, two foreign keys that refer to the primary keys of tables A and B.
All primary keys must be unique. This also implies that the combination of fields A and B must be unique in table A_B.
The sample draft database below shows you the tables that might exist in many-to-many relationships between Belgian beer brands and their suppliers in the Netherlands. Note that all combinations of beer_id and distributor_id are unique in the connection table.
The tables above link the suppliers and beer in a many-to-many relationship using a connection table. Please note that the Gentse Tripel beer (157) is supplied by Horeca Import NL (157, AC001) Jansen Horeca (157, AB899) and Petersen Drankenhandel (157, AC009). And vice versa, Petersen Drankenhandel is the supplier of 3 types of beer from the table, namely: Gentse Tripel (157, AC009), Uilenspiegel (158, AC009) and Jupiler (163, AC009).
Also note that in the tables above, the primary key fields are blue and underline. In a database design model, primary keys are usually underlined. And again, note that the beer_distributor join table has a primary key made up of two foreign keys. A connection table always has a composite primary key.
There is another important thing to know. A many-to-many relationship consists of two one-to-many relationships . Both tables: beer suppliers and beer - have a one-to-many relationship with the connection table.
As a final example, let me show you how a table of hotel room bookings by visitors could be modeled.
The many-to-many communication connection table has additional fields.
In this example, you see that there is a many-to-many relationship between the guest and room tables. One room can be booked by many guests over time and over time a guest can book many rooms at the hotel. The join table in this case is not a classic join table, which consists of only two foreign keys. She is a separate entity that has connections with two other entities.
You will often encounter situations where the combination of two entities will be a new entity.
In a one-to-one relationship, each block of entity A can be associated with a 0, 1 block of entity B. An employee, for example, is usually associated with one office. Or a beer brand can have only one country of origin.
A one-to-one relationship is easily modeled in a single table. Table entries contain data that are in a one-to-one relationship with the primary key or record.
In rare cases, a one-to-one relationship is modeled using two tables. This option is sometimes necessary in order to overcome the limitations of the RDBMS or in order to increase performance (for example, sometimes it is putting a field with the blob data type in a separate table to speed up the search in the parent table). Or sometimes you may decide that you want to split the two entities into different tables while they still have a one-to-one relationship. But usually having two tables in a one-to-one relationship is considered bad practice.
A relational database project is a collection of tables that are linked (linked) by primary and foreign keys. The relational data model includes a number of rules that help you create the right relationships between tables. These rules are called “normal forms.” In the following parts I will show how to normalize your database.
Examples of table relationships in practice. When some data is unique to a particular object , for example, a person and his passport numbers, then we are dealing with one-to-many communication . Those. in one table we have a list of certain people, and in another table we have a list of passports of this person (for example, passport of the country of residence and international passport). And this combination of data is unique to each person. Those. each person can have several passport numbers, but each passport can have only one owner. Total: we need two tables.
And if there is some data that can be assigned to any person , then we are dealing with a many-to-many relationship. For example, there is a table with a list of people and we want to store information about which countries each person visited. In this case, there are two entities: people and countries. Any person can visit any number of countries as well as any country can be visited by any person. That is, in this case, the country is not unique to a particular person and can be reused.
In such cases, using many-to-many relationships using three tables and storing general information is very convenient centrally. After all, if the general data changes, then in order for the information in the database to correspond to reality, it is enough to correct it in only one place, because it is stored only in one place (table), in the remaining tables there are only links to it.
And when you have a set of unique data that are relevant only to each other, then store everything in one table. Your choice is a one-to-one relationship. For example, you have a small collection of cars and you want to store information about them (color, make, year of manufacture, etc.).
Previous Parts: 1-3 , 4-6
7. One-to-many communication.
I have already shown you how data from different tables can be linked using foreign key relationships . You saw how orders communicate with customers by placing customer_id as a foreign key in the order table.
Another example of a one-to-many relationship is the relationship that exists between a mother and her children. A mother can have many children, but each child can have only one mother.
(Technically, it is better to talk about a woman and her children instead of a mother and her children because, in the context of a one-to-many relationship, a mother can have 0, 1 or many descendants, but a mother with 0 children cannot be considered a mother. But let's close eyes on this, okay?)
When one record in table A can be associated with 0, 1 or many records in table B, you are dealing with a relationshipone to many . In a relational data model, a one-to-many relationship uses two tables.
Schematic representation of a one-to-many relationship. The entry in table A has 0, 1, or the set of associated entries in table B.
How to recognize a one-to-many relationship?
If you have two entities, ask yourself:
1) How many objects and B can belong to object A?
2) How many objects from A can relate to an object from B?
If the answer to the first question is many , and the second one (or maybe none), then you are dealing with a one-to-many relationship.
Examples.
Some examples of one-to-many relationships:
- The machine and its parts. Each part of a machine belongs to only one machine at a time, but a machine can have many parts.
- Cinemas and screens. There can be many screens in one cinema, but each screen belongs to only one cinema.
- An entity-relationship diagram and its tables. A chart can have more than one table, but each of these tables belongs to only one chart.
- Houses and streets. There can be several houses on the street, but each house belongs to only one street.
In this case, everything is so simple that only therefore can it be difficult to understand. Take the last example with houses. Indeed, there can really be any number of houses on the street, but each house on this street can have only one street (we don’t take houses that in practice belong to different streets, take, for example, a house in the center of the street). After all, this particular house cannot be in two places at the same time, on two different streets, and we are not talking about some kind of abstract house in general, but about a concrete one.
8. Many-to-many relationships.
A many-to-many relationship is a relationship in which multiple records from one table (A) can correspond to multiple records from another (B). An example of such a connection is a school where teachers teach students. In most schools, each teacher teaches many students, and each student can be taught by several teachers.
The connection between the beer supplier and the beer they supply is also a many-to-many relationship. A supplier, in many cases, provides more than one type of beer, and each type of beer can be provided by many suppliers.
Please note that when designing a database, you should not ask yourself whether certain relationships exist at the moment, but whether relations in general are possible in the future. If at the moment all suppliers provide many types of beer, but each type of beer is provided by only one supplier, then you might think that this is a one-to-many relationship, but ... Do not rush to implement a one-to-many relationship in this situation. There is a high probability that in the future two or more suppliers will supply the same type of beer and when this happens your database - with a one-to-many relationship between suppliers and types of beer - will not be prepared for this.
Creating many-to-many relationships.
A many-to-many relationship is created using three tables. Two tables - “source” and one connection table. The primary key of the join table A_B is composite . It consists of two fields, two foreign keys that refer to the primary keys of tables A and B.
All primary keys must be unique. This also implies that the combination of fields A and B must be unique in table A_B.
The sample draft database below shows you the tables that might exist in many-to-many relationships between Belgian beer brands and their suppliers in the Netherlands. Note that all combinations of beer_id and distributor_id are unique in the connection table.
Tables “about beer”.
The tables above link the suppliers and beer in a many-to-many relationship using a connection table. Please note that the Gentse Tripel beer (157) is supplied by Horeca Import NL (157, AC001) Jansen Horeca (157, AB899) and Petersen Drankenhandel (157, AC009). And vice versa, Petersen Drankenhandel is the supplier of 3 types of beer from the table, namely: Gentse Tripel (157, AC009), Uilenspiegel (158, AC009) and Jupiler (163, AC009).
Also note that in the tables above, the primary key fields are blue and underline. In a database design model, primary keys are usually underlined. And again, note that the beer_distributor join table has a primary key made up of two foreign keys. A connection table always has a composite primary key.
There is another important thing to know. A many-to-many relationship consists of two one-to-many relationships . Both tables: beer suppliers and beer - have a one-to-many relationship with the connection table.
Another example of a many-to-many relationship: booking tickets at a hotel.
As a final example, let me show you how a table of hotel room bookings by visitors could be modeled.
The many-to-many communication connection table has additional fields.
In this example, you see that there is a many-to-many relationship between the guest and room tables. One room can be booked by many guests over time and over time a guest can book many rooms at the hotel. The join table in this case is not a classic join table, which consists of only two foreign keys. She is a separate entity that has connections with two other entities.
You will often encounter situations where the combination of two entities will be a new entity.
9. One-to-one communication.
In a one-to-one relationship, each block of entity A can be associated with a 0, 1 block of entity B. An employee, for example, is usually associated with one office. Or a beer brand can have only one country of origin.
In one table.
A one-to-one relationship is easily modeled in a single table. Table entries contain data that are in a one-to-one relationship with the primary key or record.
In separate tables.
In rare cases, a one-to-one relationship is modeled using two tables. This option is sometimes necessary in order to overcome the limitations of the RDBMS or in order to increase performance (for example, sometimes it is putting a field with the blob data type in a separate table to speed up the search in the parent table). Or sometimes you may decide that you want to split the two entities into different tables while they still have a one-to-one relationship. But usually having two tables in a one-to-one relationship is considered bad practice.
One-to-one communication examples.
- People and their passports. Each person in the country has only one valid passport and each passport belongs to only one person.
A relational database project is a collection of tables that are linked (linked) by primary and foreign keys. The relational data model includes a number of rules that help you create the right relationships between tables. These rules are called “normal forms.” In the following parts I will show how to normalize your database.
What kind of connection do you need?
Examples of table relationships in practice. When some data is unique to a particular object , for example, a person and his passport numbers, then we are dealing with one-to-many communication . Those. in one table we have a list of certain people, and in another table we have a list of passports of this person (for example, passport of the country of residence and international passport). And this combination of data is unique to each person. Those. each person can have several passport numbers, but each passport can have only one owner. Total: we need two tables.
And if there is some data that can be assigned to any person , then we are dealing with a many-to-many relationship. For example, there is a table with a list of people and we want to store information about which countries each person visited. In this case, there are two entities: people and countries. Any person can visit any number of countries as well as any country can be visited by any person. That is, in this case, the country is not unique to a particular person and can be reused.
In such cases, using many-to-many relationships using three tables and storing general information is very convenient centrally. After all, if the general data changes, then in order for the information in the database to correspond to reality, it is enough to correct it in only one place, because it is stored only in one place (table), in the remaining tables there are only links to it.
And when you have a set of unique data that are relevant only to each other, then store everything in one table. Your choice is a one-to-one relationship. For example, you have a small collection of cars and you want to store information about them (color, make, year of manufacture, etc.).
