Четвертый вид SQL-связи между таблицами (и Плутон)

(UPDATE. Вот также и с Плутоном, сначала объявили планетой, вывели в люди, а потом бац — и теперь астероид. Существенное дополнение к высказанным мыслям в конце.)

Сколько видов SQL-связи вы знаете? Три?

1) один-ко-многим;
2) один-к-одному;
3) многие-ко-многим.

Однако если заняться вопросом вплотную, возникает желание списочек расширить.

Представим такую ситуацию – вы хотите автоматизировать составление SQL запросов. (А кто не хочет? Они все длинные и однообразные.) Для этого надо прикинуть 1) какие вообще есть связи и как с ними работать, 2) инвентаризировать объекты (таблицы) вашего проекта на предмет этих связей.

Перечисляем связи

Это конечно азбука, вы можете только по диагонали просмотреть SQL выражения, речь пойдет о покупателе и его 1) кредитках, 2) паспорте, 3) покупках.

1) «один-ко-многим»
Самое милое дело. Потому что всё просто. Например, покупатель (один) и его кредитки (их много, и они принадлежат только одному). Запрос выглядит так:

SELECT cards.* FROM cards WHERE cards.buyer_id=’покупатель_id’

Суть в том, что ключ находится в таблице, связанной со словом много (это кредитки).

2) «один-к-одному»
Например, покупатель и его паспорт. Это по сути та же история, только ключ может находиться в любой из таблиц (и покупатель, и паспорт). То есть положение ключа уже не угадаешь, надо где-то записывать. Сам запрос такой:

SELECT passport.* FROM passport WHERE passport.buyer_id=’покупатель_id’

или

SELECT passport.* FROM passport, buyer WHERE passport.id=buyer.passport_id AND  buyer. id=’покупатель_id’

3) «многие-ко-многим»
И наконец, покупатель и его покупки (купленные товары, например, за всё время). Товаров много, покупателей на них — обычно тоже. Ключ приходится хранить в отдельной записи специальной таблицы, ну вы всё знаете. Запрос выглядит примерно так:

SELECT goods.* FROM goods, buyer, goods_buyer AS  gb WHERE gb.buyer_id=’покупатель_id’  AND goods.id=gb.goods_id. 

Запрос нудноватый, но понятный.

Инвентаризация связей

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

Таблица link:

1. Таблица 1
2. Таблица 2
3. Вид связи (1м, мм, 11)
4. Поле ключа
5. Таблица ключа
6. Дополнительная метка связи (бывает больше одной связи между объектами, например, покупатель-ненавидит-(какие-то) продукты )
7. (Всякая лирика – название, описание, и пр.)

Запрос для получения связи между таблицей покупателей и таблицей кредиток выглядел бы так:

SELECT link.* FROM link WHERE ( link.table1=’покупатель’  AND link.table2=’кредитка’) OR ( link.table1=’кредитка’  AND link.table2=’покупатель’)

Здрасьте, приехали

Идеальную жизнь с ручными и предсказуемыми SQL-запросами мы начинаем с какого-то дикого запроса, не попадающего в наши три вида. Некрасиво…

Дальше есть три пути:
1) По-марксистски четко определить тип данной связи как «многие-ко-многим» и по-энгельсовски решительно разделить одну таблицу «связь» на две – «связь» и таблица-связка со связанными «таблицами». По ходу потеряв наглядность таблицы «связь», простоту запроса и возможно скорость доступа (записей немного и перебор может оказаться быстрее связывания двух таблиц).
2) Либо сделать оппортунистическое предположение, что из теории трех видов связи бывают исключения, и на практике просто написать свой частный кулацкий SQL-запрос.
3) Или смело и отчаянно постулировать, что есть еще один вид связи, назовем его «несколько-ко-многим». Это когда в одной таблице можно записать несколько внешних ключей, и соответственно, немного усложнить процедуру сравнения (с учетом комбинаций возникает оператор OR). Тогда такие запросы тоже можно автоматизировать.

Мне лично кажется, что правильный выбор – пункт 3. Допустим, что так программировать запросы неправильно (я так не думаю, но допустим), но всё равно порой возникает желание так написать, поэтому такой вид связи надо включать в систему, сделав подобающие оговорки (например, неэффективно на больших таблицах и т.п.).
Добавив это четвертый вид связи «несколько-ко-многим», мы сможем завершить автоматизацию SQL-запросов, автоматизировав наш «дикий» запрос. Повторю этот вид запроса с примером.

Продолжаем перечислять связи

4) «несколько-ко-многим»
Например, покупатель участвует в покупательском забеге с корзиной в паре с другим покупателем (САНИ-ДВОЙКА). Запишем в одной записи таблицы САНИ id обоих ездоков. Давайте найдем номер их саней.

SELECT sany.* FROM sany WHERE  ( sany.buyer1=’Иванов_id’  OR  sany.buyer2=’Иванов_id’ ) 

А теперь вместо номера саней имена ездоков.

SELECT buyer.* FROM buyer, sany WHERE  ( sany.buyer1=’Иванов_id’  OR  sany.buyer2=’Иванов_id’ )  AND (buyer.id=sany.buyer1  OR   buyer.id=sany.buyer2)  

Итого

Мы описали четвертый вид связи, привели случаи его использования и в теории удачно завершили автоматизацию SQL запросов.

Вообще отношения «несколько-ко-многим» можно найти в природе достаточно легко, примеры:

1) Супруги, родители, опекуны. Молодожены в загсе делают покупки. Два-ко-многим.
2) Мужики расписывают пулю (три/четыре-ко-многим).
3) Пальцы составляют аккорд на гитаре (пять-ко-многим). Один палец (в известной мере произвольный) зажимает лад-струну или ставит баре (полубаре). Хотят тут уже можно воспользоваться классикой м-м, ибо много дополнительной информации.

Список можно продолжить.

Вид связи «несколько-ко-многим» конечно не самый распространенный, но более жизненным, чем например, узаконенный в учебниках «один-к-одному». Тот же паспорт у клиента за отчетный период легко может поменяться (уже не один, а два — старый и новый, или три и т.д.). Неизменность вообще штука более редкая, чем дружная и тесная (ограниченная числом) компания. И дружная компания – это хорошо, кто будет спорить?

UPDATE. Не бейте меня канделябрами, я не отказываюсь, что данный вид связи это частный случай вида ММ с частичная денормализация с константным числом связей на одной из сторон, как подсказали ниже. Собственно, я и написал это выше, без использования слов «нормализация» и «денормализация», хотя они мне несомненно знакомы.

Я хочу лаконично обозвать один из вариантов связи между таблицами, который нужно выбрать в выпадающим списке. И чтобы это название вписывалось с общую схему названий. Вообще-то, если говорить о частных случаях, то и 11 — это частный случай связи 1М с произвольным положением ключа в любой из связываемых таблиц (определение моё). Почему не ввести такой же частный случай для ММ?

Еще раз, хочу предложить название для определенной ситуации, а не спорить с нормализацией и денормализацией. Если же заголовок несколько броский… это да, немного перечитал ЖЖ. Тогда можете и стукнуть разок, заслужил.

Хотя в моей схеме все равно останется пункт НМ, правда теперь с привкусом меди ))

UPDATE 2.

Большое спасибо за обсуждение статьи (и минусы), оно разогнало туман в голове.

Я думаю, что четкие пацаны заголовок не меняют, и решил его не менять. Но вот что… Я сам указал в статье и многие гораздо убедительней высказались в обсуждении, что предложенная реализация отношения несколько-ко-многим — это стрёмная вещь. «Декартово произведение таблиц в автоматическом запросе — это вы серьезно?». Это требует уточнений, которых можно представить два альтернативных варианта:

1. К «четвертому» виду связи нужно добавить галочку — «для маленьких таблиц» (и соответственно иногда это проверять) и тогда городить огород со всеми ключами в одной записи, а иначе автоматом создавать классическую связь ММ (с ограничением числа участников связи). Только боюсь, эта галочка редко будет использоваться.
2. Либо упразднить четвертый пункт, и добавить галочку к третьему пункту ММ — «несколько-ко-многим со связкой ключей», которую использовать как редкий вариант (по той или иной причине) реализует описанную схему (с денормализацией и декартовым произведением).

Чувство меры всё-таки склоняет ко второму, поэтому интерфейс можно поменять, приводя к классическому списку трех видов, с подвидом НМ для ММ. Плутон мне друг, но истина дороже )).

Типов организации связей (и автоматических запросов) всё равно остается четыре. Заголовок остается тоже.

Автор: dmcolt

Источник ^[1]