relacje w mysql - jeden do wielu Opcje

[michael8383]

witam

mam problem z zaprojektowaniem bazy. robie bardzo prosty katalog filmow i zatrzymalem sie w pewnym miejscu. mam tabele film, w ktorej przechowuje wszystko o filmie. jednakze, gdy doszedlem do pola gatunek, zaczely sie schody - film moze miec wiecej niz jeden gatunek. majac tabele dodatkowa gatunek w ktorej przechowuje nazwy gatunkow, moge wstawic id_gatunku w tabele film. tylko co jesli film ma np 3 gatunki? zrobilem tabele pomocnicza ktora sklada sie z id_film, id_gatunek. operacja dodania filmu i zaznaczeniu 3 gatunkow (przez formularz na stronie), dodawany jest rekord w tabeli film i 3 rekordy w tabeli pomocniczej gdzie id_film jest taki sam a zmienia sie tylko id_gatunek.

w tym miejscu mam pierwsze pytanie : czy taka konstrukcja utworzenia dodatkowej (zbiorczej) tabeli pomocniczej jest jedynym wyjsciem?

druga sprawa to klucze obce w mysql. chcialbym zrobic tak, ze w przypadku usuniecia rekordu z tabeli film o id=1, to automatycznie usuwane sa rekordy w tabeli pomocniczej. utworzylem indeks w tabeli film wskazujacy na tabele posrednia_tabela_z_gatunkami i ustawilem on delete=cascade, on update=cascade i wszystko jest ok, ale w przypadku gdy usune rekordy w tabeli gatunki z danym id_film wtedy w tabeli film znika dany rekord. a ja chce zrobic na odwrot. usuwam rekord w film i serwer usuwa mi rekordy w tabeli pomocniczej. proba utworzenia indeksu (klikam na ikonke indeks w phpmyadmin) i tam wskazujac na pole film.id wyskakuje mi blad :

Kod

#1452 - Cannot add or update a child row: a foreign key constraint fails (`filmy2`.`#sql-da0_ae`, CONSTRAINT `#sql-da0_ae_ibfk_1` FOREIGN KEY (`id_film`) REFERENCES `film` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

wiem, ze da sie to zrobic w pph usuwajac po prostu rekordy odpowiednim zapytaniem, a wlasciwie dwoma zapytaniami ale wydaje mi sie, ze szybciej zrobi to serwer bazy, niz php. wlasnie tutaj jest nastepne pytanie : co jest lepsze, kaskadowe usuwanie w php zapytaniami czy zaprojektowanie bazy z relacjami?

przedstawiona ponizej struktura mojej bazy:

film
id (PK)
tytul

gatunek:
id (PK)
gatunek

posrednia_tabela_z_gatunkami
id_film
id_gatunek

czesze ten internet i czesze i nic konkretnego nie znalazlem. same zapytania na forach bez odpowiedzi moze tutaj ktos mi pomoze. z gory dziekuje bardzo

pozdrawiam


ps oczywiscie tabele sa INNODB

Ten post edytował michael8383 30.03.2010, 23:55:48

[nospor]

: czy taka konstrukcja utworzenia dodatkowej (zbiorczej) tabeli pomocniczej jest jedynym wyjsciem?

jedynym rozsądnym - tak

to automatycznie usuwane sa rekordy w tabeli pomocniczej.

To nie jest tabela pomocnicza. To pelnoprawna tabela.

ale w przypadku gdy usune rekordy w tabeli gatunki z danym id_film wtedy w tabeli film znika dany rekord

Cos pomieszales z zakladaniem relacji.

Przyklad poprawnej bazy i relacji:

[SQL] pobierz, plaintext 
CREATE TABLE `gatunek` (
  `ID` mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME-I-INNE BZDETY` varchar(64) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB;
 
CREATE TABLE `film` (
  `ID` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME-I-INNE-BZDETY` varchar(64) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB;
 
CREATE TABLE `film_gatunek` (
  `FK_FILM` int UNSIGNED NOT NULL,
  `FK_GATUNEK` mediumint UNSIGNED NOT NULL,
  PRIMARY KEY  (`FK_FILM`,`FK_GATUNEK`),
  CONSTRAINT `fg_fk_film` FOREIGN KEY (`FK_FILM`) REFERENCES `film` (`ID`) ON DELETE CASCADE,	
  CONSTRAINT `fg_fk_gatunek` FOREIGN KEY (`FK_GATUNEK`) REFERENCES `gatunek` (`ID`) ON DELETE RESTRICT	
) ENGINE=InnoDB;
[SQL] pobierz, plaintext 

Gdy usuwasz film - znikają powiązanie w tabeli film_gatunek
Gdy usuwasz gatunek, który jest juz powiązany z filmem - baza na to nie pozwala. Gdybyś chciał jednak w tym miejscu usuwac powiązania w film_gatunek to zamien RESTRICT na CASCADE

[michael8383]

slicznie dziekuje, dziala. zmodyfikowalem wprawdzie kod tak, ze ustawilem tylko jeden klucz obcy wskazujacy na tabele film. operacje na gatunkach nie beda robione ani spod poziomu bazy ani w php. to po prostu na sztywno ustawione nazwy.

poklikalem troche i wyglada na to, ze nie ustawilem klucza glownego w tabeli film_gatunek na oba pola. nie wiem czemu, ale dziala

jeszcze raz dzieki!