(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.
Большое спасибо за обсуждение статьи (и минусы), оно разогнало туман в голове.
Я думаю, что четкие пацаны заголовок не меняют, и решил его не менять. Но вот что… Я сам указал в статье и многие гораздо убедительней высказались в обсуждении, что предложенная реализация отношения несколько-ко-многим — это стрёмная вещь. «Декартово произведение таблиц в автоматическом запросе — это вы серьезно?». Это требует уточнений, которых можно представить два альтернативных варианта:
- К «четвертому» виду связи нужно добавить галочку — «для маленьких таблиц» (и соответственно иногда это проверять) и тогда городить огород со всеми ключами в одной записи, а иначе автоматом создавать классическую связь ММ (с ограничением числа участников связи). Только боюсь, эта галочка редко будет использоваться.
- Либо упразднить четвертый пункт, и добавить галочку к третьему пункту ММ — «несколько-ко-многим со связкой ключей», которую использовать как редкий вариант (по той или иной причине) реализует описанную схему (с денормализацией и декартовым произведением).
Чувство меры всё-таки склоняет ко второму, поэтому интерфейс можно поменять, приводя к классическому списку трех видов, с подвидом НМ для ММ. Плутон мне друг, но истина дороже )).
Типов организации связей (и автоматических запросов) всё равно остается четыре. Заголовок остается тоже.
Автор: dmcolt
