Guide to designing relational databases (7-9 part of 15) [translation]
Continued.
Previous parts: 1-3 , 4-6
I have already shown you how data from different tables can be linked using a foreign key relationship . You have seen orders contact 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.
')
(It is technically better to talk about a woman and her children instead of a mother and her children because, in the one-to-many context, 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 it, ok?)
When one record in table A can be associated with 0, 1 or a set of records in table B, you are dealing with a one-to-many relationship. In a relational data model, one-to-many relationships use two tables.
Schematic representation of a one-to-many relationship. A record in table A has 0, 1, or the set of records associated with it 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 is (or perhaps not one), then you are dealing with a one-to-many relationship.
Some examples of one-to-many communication:
In this case, everything is so simple that only therefore it can be difficult to understand. Take the last example of houses. There can indeed 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 house cannot be specifically in two places at the same time, on two different streets, and we are not talking about some abstract house at all, but about a specific 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 the school where teachers teach students. In most schools, each teacher teaches many students, and each student can be trained 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 a variety of suppliers.
Please note that when designing a database, you should not ask yourself whether there are certain links at the moment, but whether there are any links at all 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 connection 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 join 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 implies that the combination of fields A and B must be unique in table A_B.
An example of a database project below shows you tables that could exist in a many-to-many relationship between Belgian beer brands and their suppliers in the Netherlands. Note that all combinations of beer_id and distributor_id are unique in the join table.
The tables above link suppliers and many-to-many relationships with beer using a join table. 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 a supplier of 3 types of beer from the table, namely: Gentse Tripel (157, AC009), Uilenspiegel (158, AC009) and Jupiler (163, AC009).
Note also that in the tables above, the primary key fields are blue and underlined. In the database design model, primary keys are usually underlined. Again, note that the join table beer_distributor has a primary key made up of two foreign keys. The join table always has a composite primary key.
There is one more important thing you need to know. A many-to-many relationship consists of two one-to-many connections . Both tables: beer suppliers and beer - have a one-to-many relationship with the join table.
As a last example, let me show how a table of hotel room orders by visitors could be modeled.
Many-to-many join join table has additional fields.
In this example, you can see that there is a many-to-many relationship between guest tables and rooms. 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. It is a separate entity that has connections with two other entities.
You will often be confronted with such situations when the combination of two entities will be a new entity.
In a one-to-one relationship, each entity block A may be associated with a block 0, 1 entity block B. An employee, for example, is usually associated with one office. Or a beer brand may have only one country of origin.
One-to-one communication is easily modeled in one table. Table entries contain data that is 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 to overcome the limitations of the RDBMS or to increase productivity (for example, sometimes it is to move the field with the data type blob to a separate table to speed up the search in the parent table). Or sometimes you may decide that you want to split 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 true relationships between tables. These rules are called “normal forms.” In the following sections, I will show how to normalize your database.
Examples of table relationships in practice. When some data is unique for a particular object , for example, a person and his passport numbers, we are dealing with a one-to-many connection . Those. in one table we have a list of some people, and in another table we have a list of the passport numbers of this person (for example, the passport of the country of residence and 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: 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 data for a specific person and can be reused.
In such cases, using the many-to-many relationship using three tables and storing general information centrally is very convenient. After all, if the general data changes, in order for the information in the database to correspond to reality it is enough to correct it only in one place, since it is stored only in one place (table), in the other tables there are only references to it.
And when you have a set of unique data that are related only to each other, then store everything in one table. Your choice is a one-to-one connection. For example, you have a small collection of cars and you want to store information about them (color, brand, 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 a foreign key relationship . You have seen orders contact 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.
')
(It is technically better to talk about a woman and her children instead of a mother and her children because, in the one-to-many context, 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 it, ok?)
When one record in table A can be associated with 0, 1 or a set of records in table B, you are dealing with a one-to-many relationship. In a relational data model, one-to-many relationships use two tables.
Schematic representation of a one-to-many relationship. A record in table A has 0, 1, or the set of records associated with it 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 is (or perhaps not one), then you are dealing with a one-to-many relationship.
Examples
Some examples of one-to-many communication:
- Machine and its parts. Each part of the machine belongs to only one machine at a time, but the machine can have many parts.
- Cinemas and screens. There can be multiple screens in one cinema, but each screen belongs to only one cinema.
- The 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 may be several houses on the street, but each house belongs to only one street.
In this case, everything is so simple that only therefore it can be difficult to understand. Take the last example of houses. There can indeed 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 house cannot be specifically in two places at the same time, on two different streets, and we are not talking about some abstract house at all, but about a specific one.
8. Many-to-many communication.
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 the school where teachers teach students. In most schools, each teacher teaches many students, and each student can be trained 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 a variety of suppliers.
Please note that when designing a database, you should not ask yourself whether there are certain links at the moment, but whether there are any links at all 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 connection between suppliers and types of beer - will not be prepared for this.
Creating a many-to-many relationship.
A many-to-many relationship is created using three tables. Two tables - “source” and one join 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 implies that the combination of fields A and B must be unique in table A_B.
An example of a database project below shows you tables that could exist in a many-to-many relationship between Belgian beer brands and their suppliers in the Netherlands. Note that all combinations of beer_id and distributor_id are unique in the join table.
Table “about beer”.
The tables above link suppliers and many-to-many relationships with beer using a join table. 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 a supplier of 3 types of beer from the table, namely: Gentse Tripel (157, AC009), Uilenspiegel (158, AC009) and Jupiler (163, AC009).
Note also that in the tables above, the primary key fields are blue and underlined. In the database design model, primary keys are usually underlined. Again, note that the join table beer_distributor has a primary key made up of two foreign keys. The join table always has a composite primary key.
There is one more important thing you need to know. A many-to-many relationship consists of two one-to-many connections . Both tables: beer suppliers and beer - have a one-to-many relationship with the join table.
Another example of many-to-many communication: booking tickets at a hotel.
As a last example, let me show how a table of hotel room orders by visitors could be modeled.
Many-to-many join join table has additional fields.
In this example, you can see that there is a many-to-many relationship between guest tables and rooms. 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. It is a separate entity that has connections with two other entities.
You will often be confronted with such situations when the combination of two entities will be a new entity.
9. One-to-one communication.
In a one-to-one relationship, each entity block A may be associated with a block 0, 1 entity block B. An employee, for example, is usually associated with one office. Or a beer brand may have only one country of origin.
In one table.
One-to-one communication is easily modeled in one table. Table entries contain data that is 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 to overcome the limitations of the RDBMS or to increase productivity (for example, sometimes it is to move the field with the data type blob to a separate table to speed up the search in the parent table). Or sometimes you may decide that you want to split 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. Every 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 true relationships between tables. These rules are called “normal forms.” In the following sections, I will show how to normalize your database.
What kind of communication do you need?
Examples of table relationships in practice. When some data is unique for a particular object , for example, a person and his passport numbers, we are dealing with a one-to-many connection . Those. in one table we have a list of some people, and in another table we have a list of the passport numbers of this person (for example, the passport of the country of residence and 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: 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 data for a specific person and can be reused.
In such cases, using the many-to-many relationship using three tables and storing general information centrally is very convenient. After all, if the general data changes, in order for the information in the database to correspond to reality it is enough to correct it only in one place, since it is stored only in one place (table), in the other tables there are only references to it.
And when you have a set of unique data that are related only to each other, then store everything in one table. Your choice is a one-to-one connection. For example, you have a small collection of cars and you want to store information about them (color, brand, year of manufacture, etc.).
Source: https://habr.com/ru/post/193380/
All Articles