[MySQL/PHP] DB-design: hoe strikt moet je ontwerp zijn?

[Rekcor]

Voor het ontwerpen van een database bestaan regels. Wat ik altijd lastig vind, is hoe strak die regels te hanteren.

Ik heb in PHP een class gemaakt waarmee je SQL-databasetabellen kunt beheren. Deze class vereist dat ieder item in een willekeurige tabel, een unieke ID heeft. Bijvoorbeeld:

Auto's:

code:

[ID][naam][kleur]

Het 'probleem' doet zich voor (tenminste, mijn vraag is dus of het een echt probleem is), wanneer een andere tabel een uitbreiding is van een eerdere tabel. Neem bijvoorbeeld de tabel 'Franse auto's'. Deze moet terugverwijzen naar de tabel auto's:

Franse auto's:

code:

[ID][autoId][eigenschap]

Nu is het vanwege een functie in mijn PHP-class nodig dat ID=autoId in deze tabel. Vanwege de opbouw van de class, mag ik echter niet een van beide kolommen laten vervallen. Je krijgt dus:

Franse auto's:

code:

+-----+--------+------------+
| ID  | autoId | eigenschap |
+-----+--------+------------+
|  1  |    1   | blue       |
+-----+--------+------------+
| 24  |   24   | blanc      |
+- ---+--------+------------+
| 144 |  144   | rouge      |
+-----+--------+------------+

Mijn vraag: hoe erg is dit? Natuurlijk: ik heb nu dubbele data in mijn database staan (ID=autoId). Ik moet mijn algemene PHP-class - die in de overige 99% van de gevallen wel prima werkt - wel heel erg ombouwen om een van beide kolommen te laten vervallen. Hoe strikt moet je zijn?

[Megamind]

Ik snap het niet?

Als je 2 tabellen hebt, 1 met ID als key en de andere met AutoID die gelinkt staat op die ID van tabel 1, dan is er toch niks aan de hand?

[Rekcor]

Megamind schreef op woensdag 06 december 2006 @ 10:09:
Ik snap het niet?

Als je 2 tabellen hebt, 1 met ID als key en de andere met AutoID die gelinkt staat op die ID van tabel 1, dan is er toch niks aan de hand?

In mijn class kun je zeggen: de kolom "autoId" is een verwijzing naar de kolom "naam" in de tabel "auto's":

code:

Auto's
+-----+--------+--------------+
| ID  | autoId | <eigenschap> |
+-----+--------+--------------+
|  1  |    1   | blue         |
+-----+--------+--------------+
| 24  |   24   | blanc        |
+- ---+--------+--------------+
| 144 |  144   | rouge        |
+-----+--------+--------------+

wordt dan

code:

+-----+---------+--------------+
| ID  | autoId  | <eigenschap> |
+-----+---------+--------------+
|  1  | Citroen | blue         |
+-----+---------+--------------+
| 24  | Renood  | blanc        |
+- ---+---------+--------------+
| 144 | Peugot  | rouge        |
+-----+---------+--------------+

(de class maakt dus een JOIN met die andere tabel). Nu kan ik ook tegen de class zeggen: maak van ID de namen van de auto:

code:

+---------+--------------+
| ID      | <eigenschap> |
+---------+--------------+
| Citroen | blue         |
+---------+--------------+
| Renood  | blanc        |
+---------+--------------+
| Peugot  | rouge        |
+---------+--------------+

Nu werken andere functies in de class echter niet meer, aangezien er in de tabel "Franse auto's" geen auto is met ID=Peugot.

Nu kan ik dus twee dingen doen:
- de kolom autoId maken (weinig werk)
- mijn class ombouwen (veel werk)

Mijn vraag is: wat zouden jullie doen?

[ Voor 4% gewijzigd door Rekcor op 06-12-2006 10:15 ]

[Janoz]

Bij het vervangen lijkt het me logischer dat je de naam overneemt van de kolom die je joint. De laatste tabel zou ik dan zelf ook eerder de kolomnamen 'naam' en 'eigenschap' geven. Daarnaast snap ik je franse auto tabel niet helemaal. Is dit een 1:1 relatie? In dat geval zou ik de kolommen gewoon bij auto zelf indoen. Is het soms om aan te geven uit welk land een merk komt? Dan is je ontwerp fout. Zoiets hoor je niet op te lossen door de tabelnaam. In principe kun je dat ook redelijk snel zien omdat je straks exact dezelfde tabellen gaat krijgen die alleen in naam verschillen (Frans, engels, duits, japans enz enz). Zoiets zou ik veel eerder oplossen door een kolom toe te voegen (bij merk dan).

[Rekcor]

Janoz schreef op woensdag 06 december 2006 @ 10:19:
Bij het vervangen lijkt het me logischer dat je de naam overneemt van de kolom die je joint. De laatste tabel zou ik dan zelf ook eerder de kolomnamen 'naam' en 'eigenschap' geven. Daarnaast snap ik je franse auto tabel niet helemaal. Is dit een 1:1 relatie? In dat geval zou ik de kolommen gewoon bij auto zelf indoen. Is het soms om aan te geven uit welk land een merk komt? Dan is je ontwerp fout. Zoiets hoor je niet op te lossen door de tabelnaam. In principe kun je dat ook redelijk snel zien omdat je straks exact dezelfde tabellen gaat krijgen die alleen in naam verschillen (Frans, engels, duits, japans enz enz). Zoiets zou ik veel eerder oplossen door een kolom toe te voegen (bij merk dan).

Nu gaat de discussie de verkeerde kant op. De aanname is dat de kolom "eigenschap" aleen geldt voor Franse auto's en dat de tabel "Franse auto's" er dus moet zijn. (ik heb deze tabellen nu ook even verzonnen, om toch ergens over te kunnen praten)

Mijn vraag betreft de extra kolom autoId, die vanwege de opbouw van mijn class er moet zijn, maar die wel dubbele data bevat. Als ik op <strikt mode> zou staan, zou ik zeggen: pas die class aan, maar als ik op <doe-niet-zo-moeilijk mode> zou staan, zou ik zeggen: laat het lekker zo, het werkt immers en het is maar een kleine overtreding van de design-regels.

[BertS]

In je <doe-niet-zo-moeilijk mode> is je db-ontwerp in feite afhankelijk van jouw implementatie. Dat moet je m.i. niet willen.
Dus ik zou ervoor zorgen dat je generieke class wordt aangepast, zodat die ook met het strikte ontwerp van je db overweg kan.

[Rekcor]

BertS schreef op woensdag 06 december 2006 @ 10:35:
In je <doe-niet-zo-moeilijk mode> is je db-ontwerp in feite afhankelijk van jouw implementatie. Dat moet je m.i. niet willen.
Dus ik zou ervoor zorgen dat je generieke class wordt aangepast, zodat die ook met het strikte ontwerp van je db overweg kan.

Akkoord, maar je maakt m.i. toch altijd een afweging tussen de - achter de tekentafel verzonnen - theorie en de praktijk? Ik zou denk ik wel een beetje pragmatisch willen zijn in mijn ontwerp. De normalisatieregels zijn goed, maar het moet wel werkzaam blijven.

[Janoz]

Rekcor schreef op woensdag 06 december 2006 @ 10:24:
[...]


Nu gaat de discussie de verkeerde kant op. De aanname is dat de kolom "eigenschap" aleen geldt voor Franse auto's en dat de tabel "Franse auto's" er dus moet zijn. (ik heb deze tabellen nu ook even verzonnen, om toch ergens over te kunnen praten)

Mijn vraag betreft de extra kolom autoId, die vanwege de opbouw van mijn class er moet zijn, maar die wel dubbele data bevat. Als ik op <strikt mode> zou staan, zou ik zeggen: pas die class aan, maar als ik op <doe-niet-zo-moeilijk mode> zou staan, zou ik zeggen: laat het lekker zo, het werkt immers en het is maar een kleine overtreding van de design-regels.

Misschien moet je een iets beter voorbeeld verzinnen. Ik begrijp waarom je een autoId er in wilt en ik begrijp ook waarom je een primary key wilt, maar ik begrijp niet waarom de autoId en de primary key gelijk aan elkaar moeten zijn. Voor mij is gaat er dan een vlaggetje omhoog 'Dit is een 1:1 relatie en de gegevens uit beide tabellen horen dus in 1 tabel'.

Er is eigenlijk maar 1 type tabel die geen (op 1 kolom slaande) primary key heeft, en dat is een koppel tabel. Hierbij heb je een primary key die bestaat uit de combinatie van twee foreign keys.

Rekcor schreef op woensdag 06 december 2006 @ 10:46:
Akkoord, maar je maakt m.i. toch altijd een afweging tussen de - achter de tekentafel verzonnen - theorie en de praktijk? Ik zou denk ik wel een beetje pragmatisch willen zijn in mijn ontwerp. De normalisatieregels zijn goed, maar het moet wel werkzaam blijven.

Als je in deze fase van je ontwikkel proces (dus bij het schrijven van een generieke class) al consessies moet doen mbt normalisatie regels dan is er imho behoorlijk wat mis met die class of met je db ontwerp. Natuurlijk moet je niet te theoretisch blijven, maar wat ik zelf heel vaak zie is dat mensen tegen problemen aan lopen en daarom van 'de theorie' afstappen terwijl de problemen eerder veroorzaakt zijn door eht niet goed toepassen van de theorie.

Pragmatische aanpak is eerder iets dat je in een veel later stadium van je ontwikkel proces toe gaat passen.

[ Voor 26% gewijzigd door Janoz op 06-12-2006 10:55 ]

[Rekcor]

Janoz schreef op woensdag 06 december 2006 @ 10:52:
[...]

Misschien moet je een iets beter voorbeeld verzinnen. Ik begrijp waarom je een autoId er in wilt en ik begrijp ook waarom je een primary key wilt, maar ik begrijp niet waarom de autoId en de primary key gelijk aan elkaar moeten zijn. Voor mij is gaat er dan een vlaggetje omhoog 'Dit is een 1:1 relatie en de gegevens uit beide tabellen horen dus in 1 tabel'.

Er is eigenlijk maar 1 type tabel die geen (op 1 kolom slaande) primary key heeft, en dat is een koppel tabel. Hierbij heb je een primary key die bestaat uit de combinatie van twee foreign keys.

Beter voorbeeld: in mijn database heb je de tabel "personen". Dit zijn zowel medewerkers van het bedrijf, als externe personen (bijv. vertegenwoordigers van leveranciers). In de tabel "personen" worden namen, adressen, tel. nrs etc bewaard.
De tabel "medewerkers" is een uitbreiding van de tabel "personen". Van medewerkers wil ik namelijk nog meer dingen opslaan: uurloon, sofinummer.

In code:

Personen:

code:

[ID][naam][adres][PC][plaats][telefoon]

Medewerkers:

code:

[ID][persoonId][uurloon][sofi]

Als je in deze fase van je ontwikkel proces (dus bij het schrijven van een generieke class) al consessies moet doen mbt normalisatie regels dan is er imho behoorlijk wat mis met die class of met je db ontwerp. Natuurlijk moet je niet te theoretisch blijven, maar wat ik zelf heel vaak zie is dat mensen tegen problemen aan lopen en daarom van 'de theorie' afstappen terwijl de problemen eerder veroorzaakt zijn door eht niet goed toepassen van de theorie.

Ik ben de class nu ook aan het herschrijven. Maar toch blijf ik het lastig vinden waar je het evenwicht moet zoeken. Soms is het zo gemakkelijk om even van de regels af te stappen...

[Creepy]

Dus persoon en mederwerkes is nog steeds een 1 op 1 relatie.

Je hebt twee mogelijkheden wat mij betreft.
- 2 tabellen. Maar zorg er dan voor dat je die extra ID in Medewerkers niet nodig hebt. Dit is een beperking van jouw DB class die nergens goed voor is. PersoonId is al volledig uniek.
- 1 tabel waarin je een type kolom opneemt. A.d.v. het type bepaal je welke velden in de tabel gevuld zijn en welke niet.

Als het voorkomt dat een persoon meerdere types kan zijn dan heb je een n:m relatie waarbij je het een koppeltabel zal moeten gaan werken. Zoals Janoz ook al meld is de combinatie van de twee foreign keys in de koppeltabel al uniek en is er geen los ID veld nodig. De eis die je DB class stelt aan een tabel kan beperkend werken en is wat mij betreft reden genoeg om eens goed naar je DB class te kijken en aan te passen zodat deze beperking er niet is.

[ Voor 39% gewijzigd door Creepy op 06-12-2006 11:35 ]

[Rekcor]

Zucht, wat zijn jullie toch hard voor mij

Maar goed, je hebt wel gelijk. Bedankt!

[ Voor 5% gewijzigd door Rekcor op 06-12-2006 13:59 ]

[Janoz]

Mwah, waar je hier tegenaan loopt is een typisch probleem in de OR-mapper sfeer. Je denkt in objecten die van elkaar kunnen extenden terwijl een database helemaal geen object georienteerde omgeving is. Misschien wel eens leuk om wat over uit te zoeken (ORmapper levert vast wel een enorme berg resultaten op)

[Rekcor]

Janoz schreef op woensdag 06 december 2006 @ 14:14:
Mwah, waar je hier tegenaan loopt is een typisch probleem in de OR-mapper sfeer. Je denkt in objecten die van elkaar kunnen extenden terwijl een database helemaal geen object georienteerde omgeving is. Misschien wel eens leuk om wat over uit te zoeken (ORmapper levert vast wel een enorme berg resultaten op)

Klinkt interessant, maar wat is OR-mapper? Google levert wel resultaten op, maar geen resultaten die ik begrijp (alleen wat geblaat over .NET enzo)

[Rekcor]

Overigens kies ik voor de twee-tabellen optie: je hebt namelijk ook personen die geen medewerkers zijn (en dus niet de extra data nodig hebben uit de personentabel)

[eamelink]

Tsja, uiteindelijk is het toch de beperking van je class. In theorie zou het handigste zijn als je in de tweede tabel gewoon autoID als primary key en als foreign key kan gebruiken.

Een oplossing zou zijn om gewoon ID te gebruiken als primary key, én als foreign key laten referen naar ID van de auto's tabel, maar dat is niet zo heel handig, want dat moet je maar net onthouden

Beste is gewoon je php classe aanpassen zodat je willekeurige primary keys kan gebruiken

[TeeDee]

Rekcor schreef op woensdag 06 december 2006 @ 16:03:
[...]


Klinkt interessant, maar wat is OR-mapper? Google levert wel resultaten op, maar geen resultaten die ik begrijp

Exact wat Janoz zegt: een OO benadering van je DB.

Als je nu eens zoekt op OR-mapper (of iets in die richting) in combinatie met PHP moet je imo al een heel eind komen. Extragratis!

(alleen wat geblaat over .NET enzo)

omdat jij het niet begrijpt is het geblaat?

[ Voor 7% gewijzigd door TeeDee op 06-12-2006 16:14 ]

[Rekcor]

Ik begrijp niet helemaal wat ik met ORmapper zou moeten doen? Is het slecht of juist goed om te OR-mappen?

omdat jij het niet begrijpt is het geblaat?

Eh... 'klinkt het in mijn oren als geblaat'

[TeeDee]

Rekcor schreef op woensdag 06 december 2006 @ 16:22:
Ik begrijp niet helemaal wat ik met ORmapper zou moeten doen? Is het slecht of juist goed om te OR-mappen?

Dat hangt er vanaf. De juiste tool voor de juiste job. Een goede OR-mapper regelt gewoon een hele hoop voor je wat je ontwikkeltijd als het goed is verkort. Het is niet slecht, en het hoeft ook geen pré te zijn.
[...]

Eh... 'klinkt het in mijn oren als geblaat'

[Janoz]

Een ORMapper (object relational mapper) is de laag die tussen je database en je object model zit.
Hier is wel een uitgebreide uitleg te vinden (taal onafhankelijk) : http://en.wikipedia.org/wiki/Object-relational_mapping

Ikzelf vond het eerste hoofdstuk van het boek Hibernate in action (hibernate is een veel gebruikte OR mapper voor Java) een erg duidelijke beschrijving geven.

[Verwijderd]

Janoz schreef op woensdag 06 december 2006 @ 16:39:
(hibernate is een veel gebruikte OR mapper voor Java)

In een marginaal aangepaste vorm is 'Hibernate' tegenwoordig als JPA (Java Persistance Architecture) zelfs standaard onderdeel van de Java specificatie.

In .NET wordt ook gewoon Hibernate gebruikt (NHibernate) en er zijn tevens vele andere OR mappers te krijgen. Ruby on Rails gebruikt ook standaard een OR mapper. Gezien de doelgroep is het voor PHP (nog) niet echt aangeslagen. Er zijn wel wat poginkjes hier en daar, maar het wordt nu niet echt massaal gebruikt.

[merauder]

Ik vind zelf een kolom met de land van herkomst een veel nettere oplossing. Maar ik ben wel benieuwd naar het ontwerp van je database, welke gegevens je op wil slaan en waarom je het op wilt slaan. Ik heb het idee dat er iets in een eerder stadium niet goed is.