Get best of the week

Relationships between Database Tables

1. Introduction


Communication is a rather important topic that should be understood when designing databases. From my own personal experience I’ll say that, having realized the connections, I was much easier to understand the normalization of the database.

1.1. Who is this article for?


This article will be useful for those who want to understand the relationships between database tables. In it, I tried to tell in plain language what it is. For a better understanding of the topic, I alternate theoretical material with practical examples presented in the form of a diagram and a query that creates the tables we need. I use Microsoft SQL Server DBMS and I write queries in T-SQL. The code I wrote should work on other DBMSs, since queries are universal and do not use specific T-SQL constructs.

1.2. How can you apply this knowledge?


  1. The process of creating databases will become easier and more understandable for you.
  2. Understanding the relationships between tables will help you to better understand normalization, which is very important when designing a database.
  3. Dealing with a foreign database will be much easier.
  4. At the interview, this will be a very good plus.

2. Acknowledgments


The advice and criticism of the authors were taken into account. jobgemws, unfilled, firnind, Hamaruba.
Thank!

3.1. How are communications organized?


Links are created using foreign keys.
A foreign key is an attribute or set of attributes that reference the primary key or unique of another table. In other words, it is something like a pointer to a row in another table.

3.2. Types of Relations


Links are divided into:

  1. Many to many.
  2. One to many.
    • with obligatory connection;
    • with optional communication;
  3. One to one.
    • with obligatory connection;
    • with optional communication;

Let us consider in detail each of them.

4. Many to many


Imagine that we need to write a database that will be stored by an IT company employee. At the same time, there is a certain standard set of posts. Wherein:

  • An employee may have one or more posts. For example, a certain employee can be both an administrator and a programmer.
  • A position may “own” one or more employees. For example, admins are a specific set of workers. In other words, some workers belong to admins.

Employees are represented by the Employee table (id, name, age), positions are represented by the Position table (id and position name). As you can see, both of these tables are interconnected by the rule many to many: each employee has one or more positions (many positions), each position corresponds to one or more employees (many employees).

4.1. How to build such tables?


We already have two tables describing the employee and the profession. Now we need to establish many to many relationships between them. To implement such a relationship, we need some kind of intermediary between the "Employee" and "Position" tables. In our case, it will be a certain table “EmployeesPositions” (workers and positions). This mediation table links the employee and the position as follows:
EmployeeIdPositionId
11
12
23
33
On the left are the employees (their id), on the right are the positions (their id). Employees and positions on this table are indicated using id'shniki.

This table can be viewed from two sides:

  1. Thus, we say that the employee with id 1 is on the position with id 1. At the same time, pay attention to the fact that in this table the employee with id 1 has two positions: 1 and 2. That is, each employee on the left corresponds to a certain position on the right.
  2. We can also say that posts with id 3 belong to users with id 2 and 3. That is, each employee on the right has a certain employee on the left.

4.2. Implementation


Diagram


T-SQL Code
create table dbo.Employee
(
	EmployeeId int primary key,
	EmployeeName nvarchar(128) not null,
	EmployeeAge int not null
)

--   Employee .
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (1, N'John Smith', 22)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (2, N'Hilary White', 22)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (3, N'Emily Brown', 22)

create table dbo.Position
(
	PositionId int primary key,
	PositionName nvarchar(64) not null
)

--   Position .
insert into dbo.Position(PositionId, PositionName) values(1, N'IT-director')
insert into dbo.Position(PositionId, PositionName) values(2, N'Programmer')
insert into dbo.Position(PositionId, PositionName) values(3, N'Engineer')

--   EmployeesPositions .
create table dbo.EmployeesPositions
(
	PositionId int foreign key references dbo.Position(PositionId),
	EmployeeId int foreign key references dbo.Employee(EmployeeId),
	primary key(PositionId, EmployeeId)
)

insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 1)
insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 2)
insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (2, 3)
insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (3, 3)


Explanation
foreign key primary key unique .

  • PositionId EmployeesPositions PositionId Position;
  • EmployeeId EmployeesPositions — EmployeeId Employee;


4.3. Conclusion


To implement many-to-many relationships, we need a mediator between the two tables under consideration. It must store two foreign keys, the first of which refers to the first table, and the second to the second.

5. One to many


This is the most common connection between databases. We consider it after linking many to many for comparison.

Suppose we need to implement a certain database that records user data. The user has: name, surname, age, phone numbers. Moreover, each user can have one or more phone numbers (many phone numbers).

In this case, we observe the following: a user can have many phone numbers, but it cannot be said that a specific user belongs to the phone number.

In other words, the phone belongs to only one user. A user may own 1 or more phones (many).

As we can see, this relationship is one to many.

5.1. How to build such tables?


Users will be represented by a certain “Person” table (id, first name, last name, age), phone numbers will be represented by the “Phone” table. It will look like this:
PhoneIdPersonidPhone number
1511 091-10
2519 124-66
31721 972-02
This table represents three phone numbers. In this case, the phone numbers with id 1 and 2 belong to the user with id 5. But the number with id 3 belongs to the user with id 17.
Note . If the “Phones” table had more attributes, we would safely add them to this table.

5.2. Why aren't we doing an intermediary table here?


An intermediary table is only needed if we have a many-to-many relationship. For the simple reason that we can consider it from two sides. Like the EmployeesPositions table earlier:

  1. Each employee has several positions (many).
  2. Each position belongs to several employees (many).

But in our case, we cannot say that each phone has several users - only one user can belong to a phone number.
Now read again the note at the end of paragraph 5.1. - it will become more understandable for you.

5.3. Implementation


Diagram


T-SQL Code
create table dbo.Person
(
	PersonId int primary key,
	FirstName nvarchar(64) not null,
	LastName nvarchar(64) not null,
	PersonAge int not null
)

insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (5, N'John', N'Doe', 25)
insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (17, N'Izabella', N'MacMillan', 19)

create table dbo.Phone
(
	PhoneId int primary key,
	PersonId int foreign key references dbo.Person(PersonId),
	PhoneNumber varchar(64) not null
)

insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (1, 5, '11 091-10')
insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (2, 5, '19 124-66')
insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (3, 17, '21 972-02')


Explanation
Phone . ( Person). , : « ». , id .

6. One to one


Imagine that at work you were given the task of writing a database to record all employees for HR. The boss assured that the company needed to know only about the name, age and phone number of the employee. You developed such a database and placed in it all 1000 employees of the company. And then the boss says that for some reason they need to know about whether the employee is disabled or not. The simplest thing that comes to mind is to add a new bool column to your table. But it’s too long to enter 1000 values ​​and true you will enter much less often than false (2% will be true, for example).

An easier solution would be to create a new table, let's call it “DisabledEmployee”. It will look like this:
DisabledPersonIdEmployeeId
1159
2722
3937
But this is not a one-to-one relationship. The fact is that in such a table an employee can be entered more than once; accordingly, we have received a one-to-many relationship: an employee can be disabled several times. It is necessary to make sure that the employee can be entered in the table only once, respectively, could be disabled only once. To do this, we need to indicate that the EmployeeId column can only store unique values. We just need to impose a unique constraint on the EmloyeeId column. This restriction reports that an attribute can only take unique values.

By doing this, we got a one-to-one relationship.

The note. Note that we could also impose a primary key constraint on the EmloyeeId attribute. It differs from the unique constraint only in that it cannot be null.

6.1. Conclusion


We can say that a one-to-one relationship is a division of the same table into two.

6.2. Implementation


Diagram


T-SQL Code
create table dbo.Employee
(
	EmployeeId int primary key,
	EmployeeName nvarchar(128) not null,
	EmployeeAge int not null
)

insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (159, N'John Smith', 22)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (722, N'Hilary White', 29)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (937, N'Emily Brown', 19)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (100, N'Frederic Miller', 16)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (99, N'Henry Lorens', 20)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (189, N'Bob Red', 25)

create table dbo.DisabledEmployee
(
	DisabledPersonId int primary key,
	EmployeeId int unique foreign key references dbo.Employee(EmployeeId)
)

insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (1, 159)
insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (2, 722)
insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (3, 937)


Explanation
DisabledEmployee EmployeeId, . EmployeeId Employee. , unique, , . , .

7. Mandatory and optional communications


Relations can be divided into mandatory and optional.

7.1. One to many


  1. One to many with a mandatory connection:
    Many soldiers belong to one regiment. One fighter belongs to only one regiment. Please note that any soldier necessarily belongs to one regiment, and a regiment cannot exist without soldiers.
  2. One to many with optional connection:
    All people live on planet Earth. Everyone lives only on Earth. Moreover, a planet can exist without humanity. Accordingly, finding us on Earth is optional

The same relationship can be considered as mandatory and as optional. Consider this example:
One biological mother can have many children. A child has only one biological mother.
A) A woman doesn’t necessarily have her own children. Accordingly, communication is optional.
B) The child necessarily has only one biological mother - in this case, communication is required.

7.2. One to one


  1. One to one with a mandatory connection:
    One citizen of a certain country must have only one passport of that country. One passport has only one holder.
  2. One to one with optional link:
    One country can have only one constitution. One constitution belongs to only one country. But the constitution is not binding. A country may or may not have it, as, for example, Israel and Great Britain.

The same connection can be considered as mandatory and as optional:
One person can have only one passport. One passport has only one owner.
A) The presence of a passport is optional - a citizen may not have it. This is an optional link.
B) A passport must have only one owner. In this case, this is already a mandatory connection.

7.3. Many to many


Any many-to-many relationship is optional. For instance:
A person can invest in shares of different companies (many). Some company’s investors are certain people (many).
A) A person may not invest his money in stocks at all.
B) Nobody could buy shares of the company.

8. How to read charts?


Above, I gave diagrams of the tables we created. But in order to understand them, you need to know how to "read" them. We will understand this using the example of the diagram from paragraph 5.3.



We see a one-to-many relationship. One person owns many phones.

  1. Near the table Person is a golden key. It denotes the word "one."
  2. Near the Phone table is an infinity sign. It denotes the word "many."

9. Summary


  1. Connections are:
    • Many to many.
    • One to many.
      1) with obligatory communication;
      2) with an optional link.
    • One to one.
      1) with obligatory communication;
      2) with an optional link.
  2. Connections are organized using foreign keys.
  3. A foreign key is an attribute or set of attributes that reference the primary key or unique of another table. In other words, it is something like a pointer to a row in another table.

10. Objectives


For better assimilation of the material, I suggest you solve the following problems:

  1. : id, , , , . , , , .
  2. : id, , , . , .
  3. : , , ,
    • : id, , .
    • : id, .

    , — . , .
  4. 6.2. - , DisabledEmployee.

More articles:


All Articles