[database] DB relatie ontwerp vraag

[leader1000]

Beste forumleden,

Bij het ontwerpen van een database kom ik regelmatig met hetzelfde probleem in aanraking. Veel gezocht op dit foum en op google, maar zonde resultaat. Misschien ook wel deels omdat ik niet de juiste zoektermen weet.

Voor alle zekerheid: De database gaat gebruik worden in een PHP/MySQL omgeving.

Het probleem is het volgende:
Er is een tabel persoon, docent en leerling. Een persoon is of een docent of een leerling. Nou heb ik een aantal ontwerp ideeen bedacht.

1. Docent heeft een 1 op veel relatie met persoon en leerling heeft een 1 op veel relatie met persoon. Dit zou betekenen dat persoon twee FK's krijgt, namelijk de PK's van docent en leerling. Programmatechnisch zou je dan kunnen afvangen welke van toepassing is. Maar DB technisch gezien lijkt me dit niet netjes, omdat je dan een lege FK zou krijgen.

2. Tussen persoon - docent en tussen persoon - leerling maak je een veel op veel relatie. Hierdoor komen er twee tussentabbelen bij. In je programma kijk je dan of de des betreffende persoon een relatie heeft met docent of met leerling.

Beide oplossingen lijken me niet zo netjes. Ben daarom ook heel benieuwd hoe jullie dit oplossen. Of jullie een van deze opties toepassen, een andere oplossing hebben of een aangepaste oplossing.

In het voorbeeld zijn de tabellen gekozen om het beter duidelijk te maken. In werkelijkheid gaat het vaak over andere onderwerpen. Maar het komt er op neer dat er twee tabellen een relatie hebben met een zelfde tabel, maar waar altijd maar een van de twee relaties geldt.

Hopelijk is er iemand die mij verder kan helpen. Alvast bedankt voor de moeite.

[rsmits]

Een tabel persoon en een tabel persoonstype, welke de waarde docent(1) en leerling(2) heeft. In de tabel persoon een veld persoonstype opnemen die de waarde 1 of 2 heeft.

[djluc]

Het is toch allen een persoon en hoort gewoon in dezelfde tabel? spuit11

[ Voor 5% gewijzigd door djluc op 10-09-2007 20:45 ]

[leader1000]

Bedankt voor jullie snelle reactie.

Dit had inderdaad zo gekunt, maar wat nu als je voor docent en leerling nog aparte data wil opslaan. Bij leraar bijvoorbeeld salaris en bij leerling niveau. Even heel simpel gezegd.

Dus je hebt echt met drie tabellen te maken, waarvan voor iedere persoon maar een relatie met een van de twee andere tabellen heeft.

[Boss]

Hangt er vanaf hoe ver je wilt gaan met normalisatie. Ik kom in dergelijke situaties toch vaak snel op twee tabellen uit.
Wat wil je later met de gegevens gaan doen? Gaan er queries komen waarbij je alle personen (leerlingen en docenten) onder elkaar nodig hebt? Of gebruik je ze voornamelijk alleen in relatie tot elkaar? In dat geval zou ik twee tabellen houden.

[leader1000]

Boss schreef op maandag 10 september 2007 @ 21:06:
Hangt er vanaf hoe ver je wilt gaan met normalisatie. Ik kom in dergelijke situaties toch vaak snel op twee tabellen uit.
Wat wil je later met de gegevens gaan doen? Gaan er queries komen waarbij je alle personen (leerlingen en docenten) onder elkaar nodig hebt? Of gebruik je ze voornamelijk alleen in relatie tot elkaar? In dat geval zou ik twee tabellen houden.

Het zal inderdaad voor komen dat alle personen opgevraagd worden, en dan de leerlingen en de docenten.

Wat bedoelt u precies met ik zou twee tabellen gebruiken?

[rsmits]

idee (misschien niet de meest mooie):

persoon <> persoontype

detailgegevens: tabel met een veld omschrijving, welke waarden als salaris en niveau kunnen bevatten.

koppeltabel met
persoon (id), detailgegevens (id), beschrijving (input uit text veld)

op deze manier kunnen later ook detailvelden toegevoegd worden door deze aan de detail tabel toe te voegen

[leader1000]

rsmits schreef op maandag 10 september 2007 @ 21:22:
idee (misschien niet de meest mooie):

persoon <> persoontype

detailgegevens: tabel met een veld omschrijving, welke waarden als salaris en niveau kunnen bevatten.

koppeltabel met
persoon (id), detailgegevens (id), beschrijving (input uit text veld)

op deze manier kunnen later ook detailvelden toegevoegd worden door deze aan de detail tabel toe te voegen

Zoals je al zelf aangeeft inderdaad niet echt de mooiste manier. Hopelijk komen er nog meer mensen met ideeen.

[chime]

Zowel een docent en een student zijn personen, ze hebben alleen een aparte rol/functie.
Dus, het advies van rsmits opvolgens lijkt me het beste.

Want wat ga je doen als je student ineens docent wordt, of je docent bij een collega wat klassen gaat volgen ... Als je een aparte tabel aanmaakt voor studenten/docenten ga je dan data moeten repliceren wat niet goed is.

Edit:
De relatie van persoon naar rol is dus mogelijk een n op n.
Maar meerdere personen kunnen eenzelfde rol hebben:
Bijvoorbeeld:
Studenten van de klas Toegepaste Informatica kunnen allemaal dezelfde rol "student toegepaste informatica" krijgen.
Heb je een situatie waar elke student zijn eigen cursus-pakket kan samenstellen, dan zou het best kunnen dat elke student 1 unieke rol krijgt.

[ Voor 35% gewijzigd door chime op 10-09-2007 21:35 ]

[BikkelZ]

code:

adres -> persoon -> leraar   -> koppeltabel
                 -> leerling

En in die koppeltabel gebruik je een foreign key naar het persoon_id veld van de tabel leraar of leerling (en natuurlijk tussen leraar/leerling en persoon, en naar adres). Dus dan gebruik je altijd de persoon_id, maar de tabel leraar of leerling fungeert eigenlijk als filter of die persoon_id een leraar_id of een leerling_id wordt.

Je dwingt zo de rollen en de bijbehorende informatie af, aan de andere kant kan iemand leerling en leraar zijn, vader docent en zoon leraar op zelfde adres kan.

Volgens mij kan het niet cleaner.

[ Voor 22% gewijzigd door BikkelZ op 10-09-2007 21:39 ]

[DaannO]

Toevallig IIE`er?

[BikkelZ]

DaannO schreef op maandag 10 september 2007 @ 21:40:
Toevallig IIE`er?

Nee Fontys, maar dit is inderdaad wel een typische schoolopdracht


-----------


fl0,25

[ Voor 7% gewijzigd door BikkelZ op 10-09-2007 21:46 ]

[djluc]

Wat misschien wel een overweging waard is: Met functies gaan werken. Je hebt dus een persoon die je koppelt aan één of meerdere functies die je aparte velden geeft. Dan kan je ook aparte velden voor bijvoorbeeld een ingehuurde kracht regelen. Wel worden je queries een tik zwaarder en moet je op gaan passen dat je niet een té flexibel systeem gaat maken (database in database e.d.). Middels een uniforme API is een dergelijke structuur wel redelijk goed te benaderen vanuit de overige code.

[rsmits]

BikkelZ schreef op maandag 10 september 2007 @ 21:37:

code:

adres -> persoon -> leraar   -> koppeltabel
                 -> leerling

En in die koppeltabel gebruik je een foreign key naar het persoon_id veld van de tabel leraar of leerling (en natuurlijk tussen leraar/leerling en persoon, en naar adres). Dus dan gebruik je altijd de persoon_id, maar de tabel leraar of leerling fungeert eigenlijk als filter of die persoon_id een leraar_id of een leerling_id wordt.

Je dwingt zo de rollen en de bijbehorende informatie af, aan de andere kant kan iemand leerling en leraar zijn, vader docent en zoon leraar op zelfde adres kan.

Volgens mij kan het niet cleaner.

En als je nu een Congierge in hetzelfde datamodel wil toevoegen? Dan maak je een nieuwe tabel aan? Een van de grootste fouten is om voor iedere rol of product (dat zie ik vaker, principe is het zelfde) een nieuwe tabel te maken.

Ook de vraag aan djluc, hoe stel je voor die aparte velden aan te geven in een datamodel?

[BikkelZ]

rsmits schreef op maandag 10 september 2007 @ 23:02:
[...]


En als je nu een Congierge in hetzelfde datamodel wil toevoegen? Dan maak je een nieuwe tabel aan? Een van de grootste fouten is om voor iedere rol of product (dat zie ik vaker, principe is het zelfde) een nieuwe tabel te maken.

Ook de vraag aan djluc, hoe stel je voor die aparte velden aan te geven in een datamodel?

Het hangt van de applicatie af. Maar een leraar en een concierge zijn uiteindelijk beiden mensen met werktijden, een salaris en vakantiedagen, in tegenstelling tot de leerling.

[rsmits]

Uiteraard heb je gelijk wat betrefd de eigenschappen van een leraar en een concierge, maar als je een veld op zou nemen in de leraar tabel met "vak" (als tekst, in dit voorbeeld even niet als n:m oplossing met een koppeltabel) dan hebben we er ineens een eigenschap bij die niet toepasbaar is op concierge en NULL values moeten zoveel mogelijk voorkomen worden in een datamodel.

Daarnaast wil ik een stukje quoten uit de TS:

In het voorbeeld zijn de tabellen gekozen om het beter duidelijk te maken. In werkelijkheid gaat het vaak over andere onderwerpen. Maar het komt er op neer dat er twee tabellen een relatie hebben met een zelfde tabel, maar waar altijd maar een van de twee relaties geldt.

Ook meteen kijken naar andere situaties . Hierbij zijn wellicht grotere verschillen dan een docent en een concierge, maar het principe blijft gelijk. Productentypes in een webshop bijvoorbeeld.

[kasper_vk]

Ik denk, zoals al eerder gezegd, dat je Persoon zult moeten scheiden van de rollen die de persoon kan aannemen binnen jouw probleemgebied.
Persoon gaat dan niet veel verder dan naamvelden, geslacht & geboortedata o.i.d. en in Leerling c.q. Docent neem je rol -specifieke info op. Leerling & Docent krijgen dan een foreign key naar Persoon, welke tevens (kandidaat)sleutel voor de eigen tabel is.

Het scenario dat iemand in 2001-2004 leerling is, dan een jaartje gaat vakkenvullen, in 2005-2006 toch z'n studie nog afmaakt en vanaf eind 2006 bij zijn eigen school in dienst treedt als docent, kun je dan netjes kwijt zonder redundantie of gegevensverlies (mits je iets van historie vastlegt in dit geval).
Het adres hang je aan de Persoon, salaris e.d. aan Docent, studierichting/niveau aan Leerling.

Overigens denk ik dat een tabel 'Docent' niet geweldig is; wat maakt iemand een docent? Waarschijnlijk de functie waarvoor hij is aangenomen --> daarvoor zou je een tabel functies kunnen aanmaken en dan noem je Docent vanaf nu Werknemer of Dienstverband en verwijs je vanaf daar naar de Functie die de persoon heeft.

Zo voorkom je zoveel als mogelijk wat rsmits al aangaf, al gaat bovenstaande opzet in principe tegen zijn opmerking in .

[ Voor 13% gewijzigd door kasper_vk op 11-09-2007 06:24 ]

[rsmits]

kasper_vk schreef op dinsdag 11 september 2007 @ 06:13:
Ik denk, zoals al eerder gezegd, dat je Persoon zult moeten scheiden van de rollen die de persoon kan aannemen binnen jouw probleemgebied.
Persoon gaat dan niet veel verder dan naamvelden, geslacht & geboortedata o.i.d. en in Leerling c.q. Docent neem je rol -specifieke info op. Leerling & Docent krijgen dan een foreign key naar Persoon, welke tevens (kandidaat)sleutel voor de eigen tabel is.

Het scenario dat iemand in 2001-2004 leerling is, dan een jaartje gaat vakkenvullen, in 2005-2006 toch z'n studie nog afmaakt en vanaf eind 2006 bij zijn eigen school in dienst treedt als docent, kun je dan netjes kwijt zonder redundantie of gegevensverlies (mits je iets van historie vastlegt in dit geval).
Het adres hang je aan de Persoon, salaris e.d. aan Docent, studierichting/niveau aan Leerling.

Hoevaak komt het niet voor dat een maatwerkpakket binnen niet al te lange tijd veranderd, waarbij het probleemgebied uitgebreid wordt?

kasper_vk schreef op dinsdag 11 september 2007 @ 06:13:
Overigens denk ik dat een tabel 'Docent' niet geweldig is; wat maakt iemand een docent? Waarschijnlijk de functie waarvoor hij is aangenomen --> daarvoor zou je een tabel functies kunnen aanmaken en dan noem je Docent vanaf nu Werknemer of Dienstverband en verwijs je vanaf daar naar de Functie die de persoon heeft.

Zoals ik je idee begrijp:
Persoon -> Werknemer -> Functies
-> leerling

Detail gegevens van een leerling worden niet opgeslagen (althans benoem je niet). In de tabel functies benoemen is niet ideaal vanwege naamgeving en een tweede tabel is ook niet handig.

De truc is mijns inziens dat je een tabel maakt waarbij je detail velden kan benoemen en die door een koppeling een waarde mee kan geven. Als je speciale kolommen aan gaat maken voor elk detail, moet je uiteindelijk weer nieuwe tabellen maken voor iemand die andere details heeft. Anders kan je gewoon een nieuw record toevoegen van een detail en die dan koppelen aan een persoon en een waarde toekennen.

[kasper_vk]

rsmits schreef op dinsdag 11 september 2007 @ 08:12:

Zoals ik je idee begrijp:
Persoon -> Werknemer -> Functies
-> leerling

Detail gegevens van een leerling worden niet opgeslagen (althans benoem je niet).

Jawel hoor:

kasper_vk schreef op dinsdag 11 september 2007 @ 06:13:
Het scenario dat iemand
.....
Het adres hang je aan de Persoon, salaris e.d. aan Docent, studierichting/niveau aan Leerling.

---

De volgende opmerking begrijp ik niet, want in m'n pogingen om er duidelijk Nederlands van te maken kom ik op diverse mogelijke interpretaties uit. En het bevorderd m.i. de discussie niet om op basis van een verkeerde interpretatie van onderstraande zin langs jou heen te gaan zitten praten / denken / schrijven

rsmits schreef op dinsdag 11 september 2007 @ 08:12:
In de tabel functies benoemen is niet ideaal vanwege naamgeving en een tweede tabel is ook niet handig.

Dus: wat bedoel je hier te zeggen?

[rsmits]

kasper_vk schreef op dinsdag 11 september 2007 @ 08:40:
[...]

Jawel hoor:


[...]

Het is nog vroeg, daar had ik overheen gelezen. Excuses.

kasper_vk schreef op dinsdag 11 september 2007 @ 08:40:
De volgende opmerking begrijp ik niet, want in m'n pogingen om er duidelijk Nederlands van te maken kom ik op diverse mogelijke interpretaties uit. En het bevorderd m.i. de discussie niet om op basis van een verkeerde interpretatie van onderstraande zin langs jou heen te gaan zitten praten / denken / schrijven

[...]

Dus: wat bedoel je hier te zeggen?

Ik bedoel te zeggen dat een eigenschap van een leerling, zoals een gevolgd schooljaar, geen functie is en het dan ook niet logisch is om deze in de tabel functie te plaatsten. Waar je dus, naar mijn interpretatie, naar toe gaat is een tabel voor details van de docent en een tabel voor details voor de leerling. Dit scheiden in 2 tabellen lijkt mij niet handig, omdat je dan voor een uitbreiding van type (eerder genoemde concierge bijvoorbeeld), weer een nieuwe detail tabel aan zou moeten maken.

[ Voor 4% gewijzigd door rsmits op 11-09-2007 08:53 ]

[BikkelZ]

rsmits schreef op maandag 10 september 2007 @ 23:20:
Uiteraard heb je gelijk wat betrefd de eigenschappen van een leraar en een concierge, maar als je een veld op zou nemen in de leraar tabel met "vak" (als tekst, in dit voorbeeld even niet als n:m oplossing met een koppeltabel) dan hebben we er ineens een eigenschap bij die niet toepasbaar is op concierge en NULL values moeten zoveel mogelijk voorkomen worden in een datamodel.

Je moet rolspecifieke zaken altijd op de een of andere manier opslaan. Doe je dat niet door een specifieke tabel te gebruiken (ik zou dan een tabel leraar gebruiken met een FK naar de werknemer tabel op persoon_id), dan kun je ook nog gaan rommelen met veldensets die in de database opgeslagen worden. Dat persoon_type 1 (leerling) in de tabel velden drie entries heeft, namelijk dat er een veld leerlingnummer is, een veld propedeuse en een veld deeltijd, wat een INT, BOOLEAN en een BOOLEAN moeten zijn. En dat er dan weer arbitraire tabellen bestaan die niets meer en niets minder doen dan INTs, BOOLEANs, DATEs en andere zaken opslaan die in die tabellen aangegeven worden.

Ik vind dat een beetje rommelig persoonlijk.....

[rsmits]

BikkelZ schreef op dinsdag 11 september 2007 @ 10:45:
[...]
Ik vind dat een beetje rommelig persoonlijk.....

Je zal niet direct met bijvoorbeeld phpMyAdmin nuttige data uit je datamodel kunnen trekken, daar zal je zelf standaard query's voor moeten maken. In dat opzicht is het dus een wat minder nette manier. Verder moet het geen probleem zijn denk ik en is het datamodel best goed uit te tekenen.

[BikkelZ]

Ik weet niet wat het doet met je performance en het hele idee van een relationele database wordt zo om zeep geholpen. Misschien dat grote jongens als Oracle of IBM er wel echt ondersteuning voor hebben, maar anders zou ik het niet doen.