Связи между таблицами базы данных

1. Введение

Связи — это довольна важная тема, которую следует понимать при проектировании баз данных. По своему личному опыту скажу, что осознав связи, мне намного легче далось понимание нормализации базы данных.

1.1. Для кого эта статья?

Эта статья будет полезна тем, кто хочет разобраться со связями между таблицами базы данных. В ней я постарался рассказать на понятном языке, что это такое. Для лучшего понимания темы, я чередую теоретический материал с практическими примерами, представленными в виде диаграммы и запроса, создающего нужные нам таблицы. Я использую СУБД Microsoft SQL Server и запросы пишу на T-SQL. Написанный мною код должен работать и на других СУБД, поскольку запросы являются универсальными и не используют специфических конструкций языка T-SQL.

1.2. Как вы можете применить эти знания?

1. Процесс создания баз данных станет для вас легче и понятнее.
2. Понимание связей между таблицами поможет вам легче освоить нормализацию, что является очень важным при проектировании базы данных.
3. Разобраться с чужой базой данных будет значительно проще.
4. На собеседовании это будет очень хорошим плюсом.

2.1. Как организовываются связи?

Связи создаются с помощью вторичных ключей (foreign key).
Вторичный ключ — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.

2.2. Виды связей

Связи делятся на:

1. Многие ко многим.
2. Один ко многим.
3. Один к одному.

Рассмотрим подробно каждый из них.

3. Многие ко многим

Представим, что нам нужно написать БД, которая будет хранить работником IT-компании. При этом существует некий стандартный набор должностей. При этом:

• Работник может иметь одну и более должностей. Например, некий работник может быть и админом, и программистом.
• Должность может «владеть» одним и более работников. Например, админами является определенный набор работников. Другими словами, к админам относятся некие работники.

Работников представляет таблица «Employee» (id, имя, возраст), должности представляет таблица «Position» (id и название должности). Как видно, обе эти таблицы связаны между собой по правилу многие ко многим: каждому работнику соответствует одна и больше должностей (многие должности), каждой должности соответствует один и больше работников (многие работники).

3.1. Как построить такие таблицы?

Мы уже имеем две таблицы, описывающие работника и профессию. Теперь нам нужно установить между ними связь многие ко многим. Для реализации такой связи нам нужен некий посредник между таблицами «Employee» и «Position». В нашем случае это будет некая таблица «EmployeesPositions» (работники и должности). Эта таблица-посредник связывает между собой работника и должность следующим образом:

Слева указаны работники (их id), справа — должности (их id). Работники и должности на этой таблице указываются с помощью id’шников.

На эту таблицу можно посмотреть с двух сторон:

1. Таким образом, мы говорим, что работник с id 1 находится на должность с id 1. При этом обратите внимание на то, что в этой таблице работник с id 1 имеет две должности: 1 и 2. Т.е., каждому работнику слева соответствует некая должность справа.
2. Мы также можем сказать, что должности с id 3 принадлежат пользователи с id 2 и 3. Т.е., каждой роли справа принадлежит некий работник слева.

3.2. Реализация

Диаграмма

create table dbo.Employee ( 	EmployeeId int identity primary key, 	EmployeeName nvarchar(128) not null, 	EmployeeAge int not null )  -- Заполним таблицу Employee данными. insert into dbo.Employee(EmployeeName, EmployeeAge) values ('John Smith', 22) insert into dbo.Employee(EmployeeName, EmployeeAge) values ('Hilary White', 22) insert into dbo.Employee(EmployeeName, EmployeeAge) values ('Emily Brown', 22)  create table dbo.Position ( 	PositionId int identity primary key, 	PositionName nvarchar(64) not null )  -- Заполним таблицу Position данными. insert into dbo.Position(PositionName) values('IT-director') insert into dbo.Position(PositionName) values('Programmer') insert into dbo.Position(PositionName) values('Engineer')  -- Заполним таблицу EmployeesPositions данными. create table dbo.EmployeesPositions ( 	PositionId int foreign key references Position(PositionId), 	EmployeeId int foreign key references Employee(EmployeeId), 	primary key(PositionId, EmployeeId) )  insert into EmployeesPositions(PositionId, EmployeeId) values (1, 1) insert into EmployeesPositions(PositionId, EmployeeId) values (1, 2) insert into EmployeesPositions(PositionId, EmployeeId) values (2, 3) insert into EmployeesPositions(PositionId, EmployeeId) values (3, 3)

3.3. Вывод

Для реализации связи многие ко многим нам нужен некий посредник между двумя рассматриваемыми таблицами. Он должен хранить два вторичных ключа, первый из которых ссылается на первую таблицу, а второй — на вторую.

4. Один ко многим

Эта самая распространенная связь между базами данных. Мы рассматриваем ее после связи многие ко многим для сравнения.

Предположим, нам нужно реализовать некую БД, которая ведет учет данных о пользователях. У пользователя есть: имя, фамилия, возраст, номера телефонов. При этом у каждого пользователя может быть от одного и больше номеров телефонов (многие номера телефонов).

В этом случае мы наблюдаем следующее: пользователь может иметь многие номера телефонов, но нельзя сказать, что номеру телефона принадлежит определенный пользователь.

Другими словами, телефон принадлежит только одному пользователю. А пользователю могут принадлежать 1 и более телефонов (многие).

Как мы видим, это отношение один ко многим.

4.1. Как построить такие таблицы?

Пользователей будет представлять некая таблица «Person» (id, имя, фамилия, возраст), номера телефонов будет представлять таблица «Phone». Она будет выглядеть так:

Данная таблица представляет три номера телефона. При этом номера телефона с id 1 и 2 принадлежат пользователю с id 5. А вот номер с id 3 принадлежит пользователю с id 17.
Заметка. Если бы у таблицы «Phones» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.

4.2. Почему мы не делаем тут таблицу-посредника?

Таблица-посредник нужна только в том случае, если мы имеем связь многие-ко-многим. По той простой причине, что мы можем рассматривать ее с двух сторон. Как, например, таблицу EmployeesPositions ранее:

1. Каждому работнику принадлежат несколько должностей (многие).
2. Каждой должности принадлежит несколько работников (многие).

Но в нашем случае мы не можем сказать, что каждому телефону принадлежат несколько пользователей — номеру телефона может принадлежать только один пользователь.
Теперь прочтите еще раз заметку в конце пункта 4.2. — она станет для вас более понятной.

4.3. Реализация

Диаграмма

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, 'John', 'Doe', 25) insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (17, 'Izabella', 'MacMillan', 19)  create table dbo.Phone ( 	PhoneId int identity primary key, 	PersonId int foreign key references Person(PersonId), 	PhoneNumber varchar(64) not null )  insert into dbo.Phone(PersonId, PhoneNumber) values (5, '11 091-10') insert into dbo.Phone(PersonId, PhoneNumber) values (5, '19 124-66') insert into dbo.Phone(PersonId, PhoneNumber) values (17, '21 972-02')

5. Один к одному

Такая связь встречается довольно редко.

Представим, что на работе вам дали задание написать БД для учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее всю 1000 работников компании. И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений и ведь true вы будете вписывать намного реже, чем false (2% будут true, например).

Более простым решением будет создать новую таблицу, назовем ее «Invalide». Она будет выглядеть так:

Но это еще не связь один-к-одному. Дело в том, что в такую таблицу работник может быть вписан более одного раза, соответственно, мы получили отношение один-ко-многим: работник может быть несколько раз инвалидом. Нужно сделать так, чтобы работник мог быть вписан в таблицу только один раз, соответственно, мог быть инвалидом только один раз. Для этого нам нужно указать, что столбец EmployeeId может хранить только уникальные значения. Нам нужно просто наложить на столбец EmloyeeId ограничение unique. Это ограничение сообщает, что атрибут может принимать только уникальные значения.

Выполнив это мы получили связь один-к-одному.

Заметка. Обратите внимание на то, что мы могли также наложить на атрибут EmloyeeId ограничение primary key. Оно отличается от ограничения unique лишь тем, что не может принимать значения null.

5.1. Вывод

Можно сказать, что отношение один к одному — это разделение одной и той же таблицы на две.

5.2. Реализация

Диаграмма

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, 'John Smith', 22) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (722, 'Hilary White', 29) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (937, 'Emily Brown', 19) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (100, 'Frederic Miller', 16) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (99, 'Henry Lorens', 20) insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (189, 'Bob Red', 25)  create table dbo.Invalide ( 	InvalideId int identity primary key, 	EmployeeId int unique foreign key references Employee(EmployeeId) )  insert into dbo.Invalide(EmployeeId) values (159) insert into dbo.Invalide(EmployeeId) values (722) insert into dbo.Invalide(EmployeeId) values (937)

6. Как читать диаграммы?

Выше я приводил диаграммы созданных нами таблиц. Но для того, чтобы их понимать, нужно знать, как их «читать». Разберемся в этом на примере диаграммы из пункта 4.4.

Мы видим отношение один ко многим. Одной персоне принадлежит много телефонов.

1. Возле таблицы Person находится золотой ключик. Он обозначает слово «один».
2. Возле таблицы Phone находится знак бесконечности. Он обозначает слово «многие».

7. Итоги

1. Связи бывают:
   
   • Многие ко многим.
   • Один ко многим.
   • Один к одном.
2. Связи организовываются с помощью вторичных ключей.
3. Foreign key (вторичный ключ) — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.

8. Задачи

Для лучшего усвоения материала предлагаю вам решить следующие задачи:

1. Описать таблицу фильм: id, название, длительность, режиссер, жанр фильма. Обратите внимание на то, что у фильма может быть более одного жанра, а к одному жанру может относится более, чем один фильм.
2. Описать таблицу песня: id, название, длительность, певец. При этом у песни может быть более одного певца, а певец мог записать более одной песни.
3. Реализовать таблицу машина: модель, производитель, цвет, цена
   
   • Описать отдельную таблицу производитель: id, название, рейтинг.
   • Описать отдельную таблицу цвета: id, название.

   У одной машины может быть только один производитель, а у производителя — много машин. У одной машины может быть много цветов, а у одного цвета может быть много машин.

4. Добавить в БД из пункта 5.2. таблицу военно-обязанных по типу того, как мы описали отдельную таблицу Invalide.