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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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