SQL & keys

[xces]

Voorheen heb ik mysql databases gemaakt van het type MyISAM en de keys bijgehouden in software (php). Nu wil ik graag naar keys in de databases toe, dus met daadwerkelijke relaties tussen de tabellen. Nu heb ik een ontwerp gemaakt in MySQL workbench, storage engine Innodb en de database is af, alleen zit ik nu nog te 'pielen' met de keys.

Uiteraard heb ik e.e.a. opgezocht en getracht een poging te wagen met MySQL workbench maar toen termen zoals identifying relationship enz. voorbij kwamen waren ze me al kwijt, puur omdat ik niet weet wat die term betekend. Daarnaast is de weergave van de relatie (en dus de relatie zelf) anders als je hem van tabel A naar tabel B legt, of juist andersom.

Om het voor mij zo simpel mogelijk te houden, heb ik 1 scenario waar ik graag wat hulp mee zou krijgen.

Scenario:
1 product mag in meerdere categorien staan, maar 1 product hoeft niet persé aan een categorie gekoppeld te worden.

Tabel category
- categoryid (int, PK, auto)
- naam

Tabel category_product
- category_productid (int, PK, auto)
- categoryid (int)
- productid (int)

Tabel product
- productid (int, PK, auto)
- naam

Ik denk dat ik:
- een 1-1 relatie moet leggen vanuit category naar category_product (op veld categoryid)
- een 1-1 relatie moet leggen vanuit product naar category_product (op veld productid)
- een unique index moet leggen op zowel categoryid als productid in de tabel category_product zodat ik geen dubbele records krijg.

Nu is de vraag of mijn aanname klopt en of ik dan bij category een FK naar category_product moet leggen, of juist vanuit category_product een FK naar de tabel product moet leggen.

[storeman]

Je bent goed op weg. Dit type tabel is een koppeltabel. Je kunt je afvragen of het zinvol is om je koppeltabel te voorzien van een primary key. Je zou voor de pk namelijk ook de twee kolommen kunnen gebruiken, dat vangt ook direct je unique constraint af. Er loopt nog een ander topic wat hier op ingaat, conclusie: Hangt vooral af van je stijl of je een A_I id op je koppeltabel zet.

Denk trouwens ook eens na welke actions de constraints uit moeten voeren bij een update of een delete, aangezien dit nieuw voor je is, is het misschien handig om hierover ook je denkwijze op te schrijven .

[ Voor 21% gewijzigd door storeman op 23-01-2012 13:05 ]

[Big Womly]

Als ik je goed begrepen heb kan 1 product dus gekoppeld zijn aan 0 of meer categorieën.
Dan moet de relatie tussen product en category_product een (1 - 0..*) relatie zijn.
Bij 1-1 zou je zeggen dat 1 product bij exact 1 category behoort en 1 category exact 1 product bevat.

Zoals storeman ook al aangeeft, laat je de autosequence op product_category als PK best laten vallen en je PK laten samenstellen door de combinatie productid en categoryid. Hierdoor wordt je unique constraint automatisch afgedwongen.

[ Voor 30% gewijzigd door Big Womly op 23-01-2012 13:09 ]

[storeman]

@Big Womly: Dit klopt niet omdat er een koppeltabel wordt gebruikt. In deze tabel wordt niets afgedwongen, behalve dat elke rij voorzien is van een productid en categoryid. Hoeveel combinaties hierin staan, maakt natuurlijk niets uit.

[Big Womly]

@storeman: Een koppeltabel dient toch om n-n relaties weg te werken en te vervangen door 2 1-n relaties?

[xces]

@storeman:
Het voordeel om een eigen ID aan de koppeltabel aan te brengen is natuurlijk dat ik niet allebei de id's nodig heb om een record aan te kunnen wijzen in de koppeltabel.

Nu heb ik dit reeds in elkaar geknutseld (voor mijn pseudo voorbeeld) maar ik mis volgens mij nog de indexen op de koppeltabel op de 2 koppelvelden afzonderlijk?

code:

DROP TABLE IF EXISTS `category`;
CREATE TABLE  `category` (
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`categoryid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `product`;
CREATE TABLE  `product` (
  `productid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`productid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `category_product`;
CREATE TABLE  `category_product` (
  `category_productid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `categoryid` INT(10) UNSIGNED NOT NULL,
  `productid` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`category_productid`) USING BTREE,
  CONSTRAINT `fk_categoryid` FOREIGN KEY (`categoryid`) REFERENCES `category` (`categoryid`),
  CONSTRAINT `fk_productid` FOREIGN KEY (`productid`) REFERENCES `product` (`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[Zoijar]

- category_productid (int, PK, auto)

Niet doen. Gewoon een gezamelijke PK op categoryid (int), productid (int). Dan heb je meteen je uniqueness contraint verwerkt.

[xces]

hmm, dat spreekt voor zich, ik heb immers beide ids toch al bij de CRUD acties. Zonder script request-ig over te komen, wat zou den de SQL van de koppeltabel worden?

[storeman]

SQL:

DROP TABLE IF EXISTS `category_product`;
CREATE TABLE  `category_product` (
  `categoryid` INT(10) UNSIGNED NOT NULL,
  `productid` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`categoryid`, `productid`) USING BTREE,
  CONSTRAINT `fk_categoryid` FOREIGN KEY (`categoryid`) REFERENCES `category` (`categoryid`),
  CONSTRAINT `fk_productid` FOREIGN KEY (`productid`) REFERENCES `product` (`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[Zoijar]

Kan aan zoiets denken (even een snelle kopie van een koppeltabel uit mijn database...)

SQL:

CREATE TABLE links
(
  src bigint NOT NULL,
  dst bigint NOT NULL,
  CONSTRAINT links_pkey PRIMARY KEY (src, dst),
  CONSTRAINT from_fk FOREIGN KEY (src)
      REFERENCES documents (docid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT to_fk FOREIGN KEY (dst)
      REFERENCES documents (docid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

[ Voor 7% gewijzigd door Zoijar op 23-01-2012 13:21 ]

[xces]

@allen: dank.

Laatste vraag dan; Stel ik heb een customer tabel en een order tabel.
Voegt onderstaande een 1...N tussen customer en order toe?

code:

ALTER TABLE `order` ADD CONSTRAINT `fk_order_customer` FOREIGN KEY (`customerid`) REFERENCES `customer` (`customerid`);

[ Voor 80% gewijzigd door xces op 23-01-2012 14:01 ]

[Zoijar]

Ja. Als je naar de tabel kijkt zie je dat een 'order' altijd 1 'customer' heeft; elke order verwijst naar 1 customer. Maar dezelfde customer kan gerefereerd worden door verschillende orders. Order 1 wijst naar klant 1, order 2 wijst naar klant 2, maar order 3 wijst ook naar klant 1. Dus vanaf de klant gezien kan hij bij meerdere orders horen. Dus een 1...N relatie (ik weet nooit wat aan welke kant hoort, kan dat sommige het als N...1 noteren. En als je foreign key NULL mag zijn is het een 0...N, maar ook dat onderscheid wordt vaak niet gemaakt)

[ Voor 10% gewijzigd door Zoijar op 23-01-2012 14:23 ]