[MySQL] Relacja wiele do wielu

Dla specjalistów Programowanie
#1

Mam tabele  przedmioty (przedmiot_id, typ_id, …), typy (typ_id, …), grupy (grupa_id, typ_id, …) i  przedmioty_grupy (przedmiot_id, grupa_id). Każdy z przedmiotów może być tylko jednego typu i należeć do kilku grup, tak samo w jednej grupie może być kilka przedmiotów - tutaj nie mam problemu. Problemem jest to, że w jednej grupie mogą być przedmioty wyłączenie tego samego typu. O ile w PHP mogę sobie sprawdzić i dodawać tylko przedmioty tego samego typu do jednej grupy, to czy istnieje możliwość takiej walidacji z poziomu MySQL?

 

baza.png

#2

Tak na szybko, nie mozesz wrzucic typ_id do przedmioty_grupy jako dodatkowy klucz, a tablice typy polaczyc tylko z jedna z tych tablic?

#3

Nawet jeśli zrobię tak, jak piszesz, to w żaden sposób nie rozwiązuje to problemu.

#4

Taką walidację można pewnie zrobić triggerem, pytanie czy jest sens. Tego typu bardziej zaawansowane reguły biznesowe waliduje się raczej po stronie aplikacji, a nie w samej bazie - chociaż oczywiście można.

Za pomocą triggerów, procedur itp. można w bazie wszystko oprogramować. Raczej się obecnie tak jednak nie robi, ze względu na to że wygodniej i szybciej zrobić to na poziomie aplikacyjnym. Nieraz, używając ORM’ów, nie ma się wręcz w ogóle bezpośredniej styczności z bazą, mimo że gdzieś tam pod spodem jest. 

 

Jeśli chodzi o logikę, to w momencie tworzenia rekordu przedmioty_grupy patrzysz na typ z przedmiotu i typ z grupy. Jeśli są inne - wywalasz błąd.

#5

Jeśli można to zrobić tylko za pomocą wyzwalaczy, to tak jak piszesz, lepiej zrobić to w aplikacji.

#6

Możesz to zrobić trochę na około i bez sensu, ale dać się da to zrobić constraint-ami:

CREATE TABLE `SUBJECT` (

  `id` int,

  `type` int,

  PRIMARY KEY (`id`, `type`),

  UNIQUE (`id`)

);



CREATE TABLE `GROUP` (

  `id` int,

  `type` int,

  PRIMARY KEY (`id`, `type`),

  UNIQUE (`id`)

);



CREATE TABLE `SUBGRPREL` (

  `sub_id` int,

  `grp_id` int,

  `type` int,

  PRIMARY KEY(`sub_id`, `grp_id`),

  FOREIGN KEY (`sub_id`, `type`) REFERENCES `SUBJECT`(`id`, `type`),

  FOREIGN KEY (`grp_id`, `type`) REFERENCES `GROUP`(`id`, `type`)

);



INSERT INTO `SUBJECT` (`id`, `type`) VALUES (10, 1);

INSERT INTO `SUBJECT` (`id`, `type`) VALUES (20, 1);



INSERT INTO `GROUP` (`id`, `type`) VALUES (30, 1);

INSERT INTO `GROUP` (`id`, `type`) VALUES (40, 2);



INSERT INTO `SUBGRPREL` (`sub_id`, `grp_id`, `type`) VALUES (10, 30, 1); -- spoko, działa

INSERT INTO `SUBGRPREL` (`sub_id`, `grp_id`, `type`) VALUES (20, 30, 1); -- spoko, działa

INSERT INTO `SUBGRPREL` (`sub_id`, `grp_id`, `type`) VALUES (10, 40, 2); -- wywali się nie ważne czy wstawisz 1 czy 2 jako trzeci parametr, któryś z FK się będzie pluć ergo nie dodasz czegoś takiego

Cały myk polega na tym, żeby dodać unique na ID w tabelach GROUP/SUBJECT, żeby nie móc dodać dwóch tych samych id ze względu na złożony klucz główny.

Imho lepiej zrobić to w logice aplikacji, ale zostawiam to, bo dać się da, może komuś się przyda do czegoś.

#7

Jeśli nie ma rygorystycznego wymogu odnośnie walidacji na poziomie bazy, to lepiej walidację zapewnić na warstwie ORM. Poza tym jeśli byśmy mieli to rozstrzygać na poziomie bazy, to problemem nie jest operacja insert, a bardziej update. Co jeśli w grupie jest kilka przedmiotów tego samego typu i nagle zmienimy typ jednego z przedmiotów, albo zmienimy typ samej grupy, która nie jest pusta.

#8

W przypadku powyższego schematu taka operacja aktualizacji się nie uda, bo zostaną naruszone więzy integralności. Trzeba najpierw odpiąć przedmiot ze wszystkich grup a dopiero potem zmienić jego typ. I analogicznie musisz najpierw opróżnić grupę ze wszystkich przedmiotów by zmienić jej typ. Ale to samo musiałbyś zrobić po stronie aplikacji by nie zostać w stanie niespójnym w sensie stricte logicznym. Może za wyjątkiem tego, że w transakcji mógłbyś to zrobić w dowolnej kolejności.

Nie mniej zgadzam się, że aplikacja to lepsze miejsce do oprogramowania tego niż BD. Powyższy schemat ma tylko pokazać, że karkołomnie da się zrobić walidację na constraint-ach wbrew temu co było powiedziane wcześniej i triggery są zbędne.

#9

Bardzo ciekawy przykład. Nie pracuję z MySQL, a w MS SQL i tam bym zamienił mimo wszystko dodatkowe tabele na jakąś kolumnę wyliczaną z uniq, żeby uniknąć zabawy przy CRUDach :)