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

MySQL - это реляционная база данных. Это означает, что данные в базе могут быть распределены в нескольких таблицах, и связаны друг с другом с помощью отношений relation. Отсюда и название - реляционные.

один-ко-многим
один-к-одному
многие-ко-многим

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

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

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

Работников представляет таблица еmployee (id, name, age):

еmployee
id	name	age
1	Вадим	37
2	Иван	26
3	Петр	42
4	Герман	26

Должности представляет таблица position (id и dolzhnost):

position
id	dolzhnost
1	Системный администратор
2	Программист 1С
3	Верстальшик
4	Вебмастер

Обе эти таблицы связаны между собой по правилу многие ко многим: каждому работнику соответствует одна и больше должностей (многие должности), каждой должности соответствует один и больше работников (многие работники).

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

employees_positions
employee_id	position_id
1	1
1	2
2	3
3	3

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

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

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

Базы данных:

CREATE DATABASE IF NOT EXISTS test;

USE test;

CREATE TABLE еmployee

(
id int(3) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
age int(3)
);

CREATE TABLE position

(
id int(3) PRIMARY KEY AUTO_INCREMENT,
dolzhnost VARCHAR(35)

);

CREATE TABLE employees_positions

(
employee_id int(3),
position_id int(3),
FOREIGN KEY (employee_id) REFERENCES еmployee (Id),
FOREIGN KEY (position_id) REFERENCES position (Id)
);

Заполнение баз данных:

USE test;

INSERT INTO еmployee (name, age) VALUES
('Вадим', 37),
('Иван', 26),
('Петр', 42),
('Герман', 26);

INSERT INTO position (dolzhnost) VALUES
('Системный администратор'),
('Программист 1С'),
('Верстальшик'),
('Вебмастер');

INSERT INTO employees_positions (employee_id, position_id) VALUES
(1, 1),
(1, 2),
(1, 3),
(3, 3);

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

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

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

Пользователей будет представлять таблица person (id, name, surname, age):

person
id	name	surname	age
1	Вадим	Иванов	37
2	Иван	Петров	26
3	Петр	Сидоров	42
4	Герман	Греф	26

Номера телефонов будет представлять таблица phone:

phone
id	person_id	phone_number
1	2	89250000001
2	2	89250000002
3	4	89250000003

Таблица представляет три номера телефона. При этом номера телефона с phone_id 1 и 2 принадлежат пользователю с id 2. А вот номер с phone_id 3 принадлежит пользователю с id 4. Если бы у таблицы «phone» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.

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

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

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

Базы данных:

CREATE DATABASE IF NOT EXISTS test;

USE test;

CREATE TABLE person

(
id int(3) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
surname VARCHAR(35),
age int(3)
);

CREATE TABLE phone

(
id int(3) PRIMARY KEY AUTO_INCREMENT,
person_id int(3),
phone_number VARCHAR(12),
FOREIGN KEY (person_id) REFERENCES person (Id)
);

Номера телефонов:

USE test;

INSERT INTO person (name, surname, age) VALUES
('Вадим', 'Иванов', 37),
('Иван', 'Петров', 26),
('Петр', 'Сидоров', 42),
('Герман', 'Греф', 26);


INSERT INTO phone (person_id, phone_number) VALUES
(2, '89250000001'),
(2, '89250000002'),
(4, '89250000003');

Один-к-одному

Представим, на работе дали задание написать БД учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее все условные 1000 работников компании.

Работников будет представлять таблица person (id, name, age, phone):

person
id	name	age	phone
1	Вадим	37	89250000001
2	Иван	26	89250000002
3	Петр	42	89250000003
4	Герман	26	89250000004

И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений, ведь true вы будете вписывать намного реже, чем false.

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

disabled
id	person_id
1	1
2	722
3	937

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

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

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

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

Базы данных:

CREATE DATABASE IF NOT EXISTS test;

USE test;

CREATE TABLE person

(
id int(3) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
age int(3),
phone VARCHAR(12)
);

CREATE TABLE disabled

(
id int(3) PRIMARY KEY AUTO_INCREMENT,
person_id int(3) UNIQUE,
FOREIGN KEY (person_id) REFERENCES person (Id)
);

Заполнение баз данных:

USE test;

INSERT INTO person (name, age, phone) VALUES
('Вадим', 37, '89250000001'),
('Иван', 26, '89250000002'),
('Петр', 42, '89250000003'),
('Герман', 26, '8925000000');


INSERT INTO disabled (person_id) VALUES
(2),
(3);

Обязательные и необязательные связи

Связи можно поделить на обязательные и необязательные.

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

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

Один ко многим с необязательной связью: на планете Земля живут все люди. Каждый человек живет только на Земле. При этом планета может существовать и без человечества. Соответственно, нахождение нас на Земле не является обязательным.

Одну и ту же связь можно рассматривать как обязательную и как необязательную. Рассмотрим вот такой пример:

У одной биологической матери может быть много детей. У ребенка есть только одна биологическая мать.

У женщины необязательно есть свои дети. Соответственно, связь необязательна
У ребенка обязательно есть только одна биологическая мать – в таком случае, связь обязательна

Один к одному

Один к одному с обязательной связью: у одного гражданина определенной страны обязательно есть только один паспорт этой страны. У одного паспорта есть только один владелец.

Один к одному с необязательной связью: у одной страны может быть только одна конституция. Одна конституция принадлежит только одной стране. Но конституция не является обязательной. У страны она может быть, а может и не быть, как, например, у Израиля и Великобритании.

Одну и ту же связь можно рассматривать как обязательную и как необязательную:

У одного человека может быть только один загранпаспорт. У одного загранпаспорта есть только один владелец.

Наличие загранпаспорта необязательно – его может и не быть у гражданина. Это необязательная связь
У загранпаспорта обязательно есть только один владелец. В этом случае, это уже обязательная связь

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

Любая связь многие ко многим является необязательной:

Человек может инвестировать в акции разных компаний (многих). Инвесторами какой-то компании являются определенные люди (многие).

Человек может вообще не инвестировать свои деньги в акции
Акции компании мог никто не купить