Грамотная организация хранения данных в базе данных подразумевает разделение на различные таблицы. Это способствует эффективному взаимодействию с хранимой информацией. Данные одних таблиц могут зависеть от данных других, что создает необходимость построения связей между такими таблицами. В этой статье мы рассмотрим различные типы подобных связей: связь «один ко многим», «один к одному» и «многие ко многим». Мы создадим каждую из этих связей с помощью механизма внешнего ключа (foreign key), используя СУБД MySQL.
Описание примера
Для пояснения концепций отношений между таблицами в SQL будем использовать пример на футбольную тематику. Создадим базу данных, в которой будет содержаться следующая информация:
- футбольные сборные;
- игроки, выступающие за эти сборные;
- статистика по каждому футболисту;
- турниры, в которых участвовали сборные.
Для каждой сущности выделим по одной таблице: teams (команды), players (игроки), statistics (статистика игроков) и tournaments (турниры).
Добавить связи можно как существующим таблицам, так и при создании новых таблиц. В статье мы упомянем оба варианта. Сперва разберем, как происходит связывание таблиц.
Foreign key
Связь одной таблицы с другой устанавливается с помощью внешнего ключа (foreign key) — столбца одной таблицы, который является первичным ключом (primary key) другой таблицы. Часто для primary key выбирают столбец с уникальным идентификатором строки (ID). Схематически эту связь можно изобразить так:
В этом примере столбец primary key таблицы teams связан с внешним ключом team_id таблицы players.
Такие отношения имеют разные типы, правильное построение которых определяет качественную бизнес-модель хранения данных. Далее разберем, что из себя представляют эти типы связей.
Типы связей между таблицами
Cуществуют следующие основные типы связей между таблицами в базе данных:
- «один к одному»;
- «один ко многим»;
- «многие ко многим».
Рассмотрим подробно каждую из них.
Связь один к одному
Связь «один к одному» подразумевает, что каждая строка одной таблицы соответствует только одной уникальной строке другой таблицы. Такой вид отношений на практике встречается нечасто.
Рассмотрим эту связь на нашем примере. Создадим две таблицы: players (игроки) и statistics (их статистика). У каждого игрока есть свой набор статистических данных, которые представлены в единственном для этого футболиста экземпляре во внешней таблице statistics.
Колонки id обеих таблицы будут primary key. Свяжем таблицы по колонкам players.id и statistics.player_id. Эти колонки должны быть уникальными, чтобы отношение между таблицами являлось «один к одному». Столбец players.id имеет auto_increment, поэтому его значения будут увеличиваться на единицу автоматически при добавлении новой записи. Также players.id будет уникальным, благодаря primary key. Колонку statistics.player_id сделаем уникальной с помощью unique. Заполним каждую таблицу демонстрационными данными.
Схема подобной связи выглядит следующим образом:
Команды SQL:
-- создание таблицы players
create table players
(
id int auto_increment,
name varchar(50) not null,
birth_year smallint not null,
primary key (id)
);
-- демо данные
insert into players(name, birth_year) values ('Cristiano Ronaldo', 1985);
insert into players(name, birth_year) values ('Antoine Griezmann', 1991);
insert into players(name, birth_year) values ('Lionel Messi', 1987);
insert into players(name, birth_year) values ('Kylian Mbappe', 1998);
-- создание таблицы statistics
create table statistics
(
id int auto_increment,
goals int,
passes int,
player_id int,
primary key (id),
foreign key (player_id) references players(id),
unique (player_id)
);
-- демо данные
insert into statistics(goals, passes, player_id) values (702, 223, 1);
insert into statistics(goals, passes, player_id) values (235, 101, 2);
insert into statistics(goals, passes, player_id) values (702, 223, 3);
insert into statistics(goals, passes, player_id) values (228, 112, 4); Связь один ко многим
Связь «один ко многим» означает, что каждая строка одной таблицы соотносится с несколькими строками другой таблицы. Это самый популярный вид связей.
В нашем примере создадим еще одну таблицу (teams), содержащую футбольные сборные, за которые выступают игроки из таблицы players. Так как один игрок может играть только за одну сборную, а любая сборная имеет в своем составе много игроков, то здесь очевидна связь «один ко многим». Добавим новую колонку team_id в таблицу players и произведем связь между таблицами по колонкам teams.id и players.team_id.
Дополним нашу схему новой связью:
Команды SQL:
-- создание таблицы teams
create table teams
(
id int auto_increment,
name varchar(50) not null,
year_founded smallint not null,
fifa_code varchar(3),
primary key (id)
);
-- демо данные
insert into teams(name, year_founded, fifa_code) values ('Argentina', 1893, 'ARG');
insert into teams(name, year_founded, fifa_code) values ('France', 1904, 'FRA');
insert into teams(name, year_founded, fifa_code) values ('Portugal', 1914, 'POR');
-- новая колонка в players
alter table players add team_id int not null;
-- установим игрокам id их сборных
update players set team_id=3 where id=1;
update players set team_id=2 where id=2;
update players set team_id=1 where id=3;
update players set team_id=2 where id=4;
-- установим связь между таблицами
alter table players add foreign key (team_id) references teams(id); Связь многие ко многим
Связь «многие ко многим» создается между таблицами, когда каждая строка одной таблицы соответствует нескольким строкам второй таблицы, и, в свою очередь, каждая строка второй таблицы соотносится с несколькими строками первой таблицы. Подобная связь создается через таблицу-посредника, которая содержит в себе внешние ключи связываемых таблиц.
Продемонстрировать такое отношение в нашем примере можно, добавив новую таблицу (tournaments), содержащую список международных турниров, в которых участвуют сборные из таблицы teams. Каждая сборная может принимать участие в различных турнирах. И каждый турнир может принимать у себя разные сборные.
Добавим отношение «многие ко многим» в нашу схему, которая будет выглядеть теперь следующим образом:
Команды SQL:
-- создание таблицы tournaments
create table tournaments
(
id int auto_increment,
name varchar(50) not null,
year_founded smallint not null,
region varchar(20) not null,
primary key (id)
);
-- демо данные
insert into tournaments(name, year_founded, region) values ('FIFA World Cup', 1930, 'World');
insert into tournaments(name, year_founded, region) values ('UEFA European Championship', 1960, 'Europe');
-- создание таблицы-посредника
create table teams_tournaments
(
team_id int,
tournament_id int,
foreign key (team_id) references teams(id),
foreign key (tournament_id) references tournaments(id),
primary key (team_id, tournament_id)
);
-- заполнение таблицы-посредника
insert into teams_tournaments(team_id, tournament_id) values (1, 1);
insert into teams_tournaments(team_id, tournament_id) values (2, 1);
insert into teams_tournaments(team_id, tournament_id) values (2, 2);
insert into teams_tournaments(team_id, tournament_id) values (3, 1);
insert into teams_tournaments(team_id, tournament_id) values (3, 2); В заключение отметим, что связи в SQL являются важной частью проектирования баз данных. Они помогают нам лучше организовывать данные и создавать более эффективные запросы. Понимание различных типов отношений и того, как они работают, может помочь нам сделать лучший выбор при проектировании баз данных.
