Привет, коллеги. 
Предо мной задача встала, для не слишком искушённого в БД интересная.

Не хочу изобретать велосипеда, поэтому надеюсь на советы матёрых волков   

Условие:
есть две таблицы: Звери и Пиво.
Например, звери: 1.слон, 2.медуза, 3.пингвин. Пиво: 1.жыгулёвское, 2.клинское, 3.kozel.
Каждый зверь любит не всякое пиво, например, слон любит жыгуль и kozel. Жыгуль любит пингвин, слон и медуза.
Значит, по всем правилам "многие-ко-многим" есть 3я таблица, "зверь-пиво".

Ещё есть _пабы_,  в _пабы_ пускают только определённых зверей с определёнными сортами пива. Например, в паб "дядя гарри" пускают слонов с жыгулём, слонов с kozl-ом и медуз с клинским.
В паб "у джо" пускают, скажем, только медуз с kozlом и пингвинов с жыгулём или kozлом.
Пусть уже есть таблица пабов
1. "дядя гарри"
2. "у джо" и т.д.
А надо сделать таблицу, чтоб описывала в какой паб какого зверя с каким пивом пускают. Казалось бы, ничего сложного - сделать таблицу "паб-зверь-пиво" связующую, но при этом надо учесть, чтоб нельзя было заносить туда зверей с тем пивом, которое они не пьют.

Всё это на MySQL и, конечно, легко решается на прикладном уровне. Но вот ради интереса и красивого решения хочу узнать, как выйти из положения именно в пределах БД.

Пока что мне предложили одно решение:

Сделать в таблице "зверь-пиво" лишнее уникальное поле-счётчик, а для описания пропусков в пабы уже использовать не "паб-зверь-пиво", а "паб-счётчик_зверей_с_пивом_из_связующей_таблицы". Но встаёт вопрос о ключах: как сделать так, чтобы ежели счётчик будет ключом, отследить уникальность комбинации "зверь-пиво" (которая на настоящий момент является составным ключом).

Спааасибо всем, кто хотя бы это дочитал=)) Жду ответа!

интересная задача, особенно та чать что про пиво и пабы   
ну первое что бросается в глаза, со счетчиком, который будет ключем, связку пиво зверь можно отследить через UNIQUE (кажется в MySQL это есть!!!)
второе - оставить первичным ключем эту связку, а счетчик просто инерементом (индексированым)
третье - скинуть все в таблицу  паб-зверь-пиво и добавить логическое поле "нелюбит" тогда можно заносить всех зверей со всем пивом и для всех пабов...
пока все, названия таблиц сильно кружат голову      

Добавлено @ 18:45
кстати в последнем случае ключем будет связка паб-зверь-пиво без нелюбит просто как   а то   вдруг у него вкусы изменятся   

Это сообщение отредактировал(а) vladimir74 - 23.10.2008, 18:45

не верно. если пристрастия зверей изменятся, это не значит, что изменятся характеристики пабов. и наоборот. потому возможный вариант "увязать все в кучу" через дополнительный синтетический ключ только усложнит модификацию. 
логика "не возвращать сочетания зверь+пиво" решается на уровне запроса, а не структуры данных.

да, "тактически" это вполне корректное решение: автоинкрементный синтетический ключ и составной естественный ключ, указанный уникальным ключом таблицы. но "стратегически" это запутывает дело, а не облегчает процесс(см. выше)

ну потому я и предложил третий вариант, мне он в принципе первым в голову пришел, хотя я не уверен что он 100% точный. Но ИМХО для задачи если других условий нет - подходит, лично сейчас пошел бы по этому пути, хотя возможно подумав бы нашел там проблемы.... (я про последний вариант)

Хааа. Насчёт того, что вкусы зверей измениться могут - я и не подумал   
При удалении соответствующей связки "зверь-пиво", в таблице с пабами тоже бы неплохо запись соответствующую удалять, как в обычном каскадном удалении.. А вот обновление запретить=) Только опять же - как это можно сделать?

2skyboy: только вот тут дело не столько в том, что нужно возвращать, столько в том, что нужно хранить. Позволить добавлять и хранить лишние пары "зверь-пиво" в таблице "паб-зверь-пиво", а правильность контролировать только при возвращении данных - как-то некрасиво.
Это ведь сводится к контолю за заполнением "паб-зверь-пиво" на уровне прикладного софта, а я как раз и добиваюсь того, чтобы это было не обязательно 

Касаемо UNIQUE - тут я попытался раскопать в MySQL reference, но абсолютно ничего интересного не нашёл. Может, это словцо (а за ним и его функциональность) в мускуле идёт лесом?  

2vladimir74: касаемо способов 1 и 3 выше, а насчёт второго скажу вот как: счётчик в мускуле обязательно должен быть первичным ключом   , не знаю, почему, но такое ограничение есть. А сделать в таблице "зверь-пиво" счётчик PK я ессно не могу - нарушится обязательность уникальности пары.

Ойй, я уже сам запутался, распутайте меня, плиз!

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

Добавлено через 6 минут и 32 секунды
наверное, я поторопился с ответом.
надо было спросить сначала вот что: если в паб пускают и слонов, и пингвинов. если и те, и другие любят клинское. если в паб пускают слонов с клинским... значит ли это, что пингвинов с клинским тоже пустят? или же это заранее неизвестно?
если могут не пустить, тогда я не прав, ты прав и надо хранить все сочетания зверь-пиво-паб. а ссылочную целосность(каскадное удаление) делать либо средствами внешних ключей(которые поддерживаются только хранилищем innoDB), либо триггерами(которые поддерживаются, начиная с 5 версии), либо делая вставку/изменение/удаление только через хранимые процедуры, которые кроме непосредственно изменения данных, меняют и все связанные элементы тоже.
если же я прав, и дело не в конкретной связке зверь + пиво, а только в списке сортов пива и списке животных, и надо просот среди возможных сочетаний разрешенное пиво + разрешенное животное выбросить пары животное + пиво, которые не соответствуют вкусам животных, то я буду стоять на своем.

Ага, похоже, так и есть. БУдем разбираться с "хранимыми процедурами" и с чем там их едят. Спасибо за то, что не поленились вникнуть в суть проблемы.

Это сообщение отредактировал(а) Buter-Brod - 29.10.2008, 14:36