Связи таблиц SQL

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

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

Существуют такие типы связей между таблицами в SQL:

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

усложненная модель

Для полного понимания темы советую ознакомиться с темой ключи sql. Без знания ключей понять примеры будет сложно.

Связь один ко многим (one-to-many)

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

create table shoes(shoes_id int auto_increment primary key, title text, size int, price float, count int, type varchar(30), supplier int, 
foreign key (supplier) references supplier (supplier_id));

Чтобы еще раз продемонстрировать Вам связь один ко многим, предположим, что поле size подразумевает нечто больше чем просто число о размере обуви. Ведь если магазин будет работать по всему миру, то единого стандарта размеров не будет. Пусть size будет ссылкой на отдельную таблицу, которую так и назовем: shoes_size. В ней для примера будут поля: size_id, european_size, american_size. Модифицируем таблицу согласно обновленных условий:

create table shoes_size(size_id int auto_increment primary key, european_size int, american_size int);
alter table shoes add foreign key (size) references shoes_size(size_id);

добавление внешнего ключа

Теперь, у нас таблица shoes ссылается на таблицы supplier и size по связи многие к одному: много обуви может иметь одного поставщика и один размер. И наоборот: один поставщик может иметь много обуви; один размер может быть у большого количества обуви.

Один к одному (one-to-one)

Связь один к одному это когда одной записи в таблице отвечает только одна запись из другой таблицы. Чтобы продемонстрировать связь один к одному возьмем таблицу supplier:

supplier table

и вынесем колонку full_address в отдельную таблицу. Таким образом в таблице supplier будет ссылка на таблицу address, в которой будут такие поля: address_id, coutry, city, street.

Не теряя времени сделаем нужные изменения.

alter table supplier modify column full_address int;

Так как поле full_address будет ссылкой на таблицу address сделаем его тип целочисленным.

Далее создадим саму таблицу address.

create table address(address_id int auto_increment primary key, country text, city text, street text);

Теперь укажем, что поле full_address будет внешним ключом:

alter table supplier add foreign key (full_address) references address(address_id);

Связь один к одному означает, что в таблице supplier будет уникальный идентификатор записи с таблицы address. Поэтому, нужно сделать поле full_address уникальным:

alter table supplier add foreign key (full_address) references address(address_id);

Теперь, каждый адрес будет относиться только к одному поставщику и все попытки добавить поставщику адрес другого поставщика будет приводить к ошибке.

describe table supplier

Многие ко многим (many-to-many)

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

Для примера предположим, обувь может быть нескольких типов (кроссовки-кеды или туфли-мокасины). Может пример и не самый удачный, однако для тренировочных целей будет то что нужно.

shoe describe

 

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

Создаем таблицу type с полями: type_id, name:

create table type(type_id int auto_increment primary key, name text);

Теперь удаляем поле type с таблицы shoes:

alter table shoes drop type;

Далее создаем таблицу shoes_type с полями type_id, shoes_id, который будут ссылками на таблицы type и shoes соответственно.

create table shoes_type(type_id int, shoes_id int, foreign key (type_id) references type(type_id), foreign key (shoes_id) references shoes(shoes_id));

many to many relation

Вот так просто можно смоделировать отношение многие ко многим.

Мы не плохо так оптимизировали нашу базу данных. Желательно делать это на стадии разработки реляционной модели, а не после ее создания. Наши оптимизации местами могут показаться не совсем логичными и нужными. В данном случаи мы их делали только для изучения отношений между таблицами SQL. На деле же оптимизацию нужно проводить более тщательно. Потому что база данных — костяк (фундамент) любого приложения.

На этом пока все. Следите за новыми уроками по SQL, не забывайте учить Java и комбинируйте все это в многослойных веб приложениях.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *