Relatie tussen tabellen, wel of geen koppeltabel

[TheNephilim]

Het is al even geleden dat ik écht na moest denken over relaties binnen een (MySQL) database en was benieuwd wat nu de best practice is omtrend koppeltabellen en de verschillende relaties. Ik was al eens aan de slag gegaan in de MySQL Workbench, maar kwam erachter dat ik bepaalde dingen toch uit het oog verloren was. Na wat Googelen kwam ik niet aan de gewenste informatie, dus toch hier maar eens vragen.

Situatie

Het gebruik van een koppeltabel en (iets minder) foreign keys is mij bekend, maar hoe (en of) ik die hier moet toepassen vraag ik me af.

Tabel: user.
- id
- name

Tabel: building. Een user kan meerdere gebouwen bezitten.
- id
- user_id
- name

Tabel: building_log. Bij een building kunnen meerdere log entries horen.
- id
- building_id
- event

... of

Tabel: user.
- id
- name

Tabel: user_has_building. Koppeltabel
- user_id
- building_id

Tabel: building. Een user kan meerdere gebouwen bezitten.
- id
- name

Tabel: building_has_log. Bij een building kunnen meerdere log entries horen.
- building_id
- log_id

Tabel: log. Bij een building kunnen meerdere log entries horen.
- id
- event

---

Goed, een log zou je ook in een non-relationele database kunnen gooien en zo zijn er wel meerdere optimalisaties te verzinnen voor bovenstaand voorbeeld. Het gaat me er meer om; wanneer een koppeltabel en wanneer niet. Bij 1:m relaties hoeft het niet; zie eerste voorbeeld, maar bij n:m relaties kun je niet anders. Toch zie ik ook vaak gezegd worden dat het niet goed is om (bijv.) user_id in de tabel building te hebben.

En waar komt de foreign key in het verhaaltje voor, alleen bij situatie 2 of ook bij het eerste voorbeeld.

[BM]

Ik zie zelf niet in waarom het fout zou zijn om een owner_id oid bij een building vast te leggen. Als je extra informatie over de relatie zelf vast wilt leggen zou ik wel een koppeltabel maken, in het geval van de relatie user <-> building zou daarin dan de start en/of einddatum vastgelegd kunnen worden waarmee je een historie opbouwt, wat in sommige gevallen ook wel prettig kan zijn

[Afvalzak]

Kan één gebouw ook bij meerdere users horen?
Zo nee, dan zou ik gewoon een owner/user_id bij de building knallen. Anders een koppeltabel.
Kan één log ook bij meerdere buildings horen?
Zelfde verhaal

Maar goed als je inderdaad historie vast wilt leggen, wat je zeker in de toekomst wil is een koppeltabel met daarin een Start/Einddatum wel handig bij User <-> Building.

[ Voor 29% gewijzigd door Afvalzak op 19-05-2014 16:23 ]

[TheNephilim]

Van toepassing op beide reacties; een gebouw zou kunnen verhuizen van eigenaar. Dus is een koppeltabel wellicht om een history op te bouwen inderdaad. Met een begin- en einddatum kun je eenvoudig de history voor eigendom opslaan.

De log entries zou je dus gewoon kunnen voorzien van een building_id om op te slaan bij welk gebouw ze horen.

Klinkt logisch!

[Archiebald]

Ik persoonlijk, pas alleen een koppeltabel toe wanneer er sprake is van een meer-op-meer relatie of een een-op-meer relatie, waarbij er op de relatie ook eigenschappen zitten.

In jouw situatie dat een building altijd maar 1 actieve owner heeft, maar dat er op die relatie ook een datum van toepassing is (periode van eigenaarschap), zou ik voor een koppeltabel gaan. Omdat die datum van toepassing is op de relatie tussen owner en building. Dus heeft de koppeling bestaansrecht gekregen in de vorm van een losse entiteit.

[Crazy D]

Ik zou geen aannames doen dat je in toekomst zeker wel begin- en einddatum wilt vastleggen... De vraag is of de klant dat op dit moment wil, en/of de klant nu alvast met een datum in de toekomst een eigenaar wil kunnen wijzigen.

Je kunt prima de owner vastleggen in de building tabel, en bij een mutatie dit in een log tabel wegschrijven (waar je wellicht ook andere mutaties van de building vastlegt zoals het wijzigen van een omschrijving oid). Je kunt dan minder eenvoudig opvragen wie de eigenaar was op 1-1-2003, en je kunt ook niet vastleggen dat Pietje vanaf 1-1-2016 de nieuwe eigenaar is, maar het is de vraag of dat nu wenselijk is. Ik zou er geen rekening mee houden dat dat wellicht over 4 jaar wel wenselijk is, wie dan leeft wie dan zorgt.

[SlaadjeBla]

Archiebald schreef op maandag 19 mei 2014 @ 16:52:
Ik persoonlijk, pas alleen een koppeltabel toe wanneer er sprake is van een meer-op-meer relatie of een een-op-meer relatie, waarbij er op de relatie ook eigenschappen zitten.

Dit zijn inderdaad de "modeleer-regels". In het laatste geval is, zoals je zegt, de relatie waarschijnlijk een entiteit op zich (bijv. verhuurperiode)

[ Voor 3% gewijzigd door SlaadjeBla op 20-05-2014 09:20 ]

[TheNephilim]

Nee er is al wel sprake van het 'verhuizen' van een gebouw van de ene eigenaar naar de andere. Dat zou inderdaad ook in een apart log bestand kunnen, waarin die mutaties bijgehouden worden; maar liever heb ik start_date en end_date (bijv.) die dat regelen.

Het moet allemaal zo eenvoudig mogelijk en de user -> building link is op dit moment de enige waarvoor ik een koppeltabel ga gebruiken. Andere relaties binnen de webapplicatie zijn wat simpeler en kunnen (oa.) niet verhuisd worden.

Volgens mij kom ik zo al een aardig eind; even het database ontwerp aanpassen!

[ACM]

TheNephilim schreef op dinsdag 20 mei 2014 @ 12:00:
Het moet allemaal zo eenvoudig mogelijk en de user -> building link is op dit moment de enige waarvoor ik een koppeltabel ga gebruiken. Andere relaties binnen de webapplicatie zijn wat simpeler en kunnen (oa.) niet verhuisd worden.

Het grote nadeel van een dergelijke koppeltabel is dat je met je joins altijd extra join-clauses moet toevoegen voor 'de user die nu actief is' en dat je ook wat meer moeite moet doen om af te dwingen dat er maar 1 actief is.
Bovendien kun je geen foreign key constructie meer vanuit je buildings afdwingen; je kan dus niet meer afdwingen dat er altijd een eigenaar is (wat je wel kan met een foreign key en 'not null' op je userId-kolom). Bovendien kan je ook niet eenvoudig afdwingen dat er altijd precies 1 eigenaar is.
Met een losse historie-tabel voor voorgaande eigenaren kan je eventueel ook nog aanvullende informatie toevoegen die totaal niet relevant is voor de bestaande eigenaar (eigenlijk is een einddatum al niet eens relevant voor een bestaande eigenaar). Maar ook daar moet je natuurlijk moeite doen om te garanderen dat de historie altijd netjes opeenvolgend een compleet dekkende historie biedt.

Kortom, ik weet niet of de 'er moet een begin/einddatum bij' wel voldoende reden zou zijn voor mij om er een koppeltabel tussen te zetten. Wellicht zou ik in jouw situatie een owner-id toevoegen aan de buildings-tabel en los daarvan een transacties-tabel bijhouden, waarbij je steeds een verkopende owner en een kopende owner hebt en datum van transactie. Maar ook dat heeft natuurlijk weer nadelen

PS, Vergeet trouwens niet dat een owner meerdere keren opnieuw eigenaar ergens van kan worden. Dus dat je geen primary key op die koppeltabel kan zetten op alleen de ownerid+buildingid...

[TheNephilim]

ACM schreef op woensdag 21 mei 2014 @ 07:57:
[...]

Het grote nadeel van een dergelijke koppeltabel is dat je met je joins altijd extra join-clauses moet toevoegen voor 'de user die nu actief is' en dat je ook wat meer moeite moet doen om af te dwingen dat er maar 1 actief is.

Dat is waar, daar had ik nog niet eens zo goed over na gedacht. Nu zijn er niet enorm veel verschillende acties en al helemaal geen 'snelle' updates van de gegevens, maar dat is wel iets om rekening mee te houden.

Bovendien kun je geen foreign key constructie meer vanuit je buildings afdwingen; je kan dus niet meer afdwingen dat er altijd een eigenaar is (wat je wel kan met een foreign key en 'not null' op je userId-kolom). Bovendien kan je ook niet eenvoudig afdwingen dat er altijd precies 1 eigenaar is.

Ook deze had ik nog niet in de smiezen! Daar was ik misschien achter gekomen bij het maken van de definitieve database layout, maar een goede tip.

Met een losse historie-tabel voor voorgaande eigenaren kan je eventueel ook nog aanvullende informatie toevoegen die totaal niet relevant is voor de bestaande eigenaar (eigenlijk is een einddatum al niet eens relevant voor een bestaande eigenaar). Maar ook daar moet je natuurlijk moeite doen om te garanderen dat de historie altijd netjes opeenvolgend een compleet dekkende historie biedt.

Yup, dat is natuurlijk ook makkelijker achteraf aanpasbaar mochten er gegevens bij komen. Het geheel is alleen al makkelijker later toe te voegen, als er een verhuis functie ingebouwd wordt .

Kortom, ik weet niet of de 'er moet een begin/einddatum bij' wel voldoende reden zou zijn voor mij om er een koppeltabel tussen te zetten. Wellicht zou ik in jouw situatie een owner-id toevoegen aan de buildings-tabel en los daarvan een transacties-tabel bijhouden, waarbij je steeds een verkopende owner en een kopende owner hebt en datum van transactie. Maar ook dat heeft natuurlijk weer nadelen

PS, Vergeet trouwens niet dat een owner meerdere keren opnieuw eigenaar ergens van kan worden. Dus dat je geen primary key op die koppeltabel kan zetten op alleen de ownerid+buildingid...

Nee duidelijk, zowel technisch als praktisch heb je een heel goed punt! Ik denk dat ik maar eens gewoon het database ontwerp af moet maken en deze hier nog een keer laten zien. Dan moet ik nog wel even kijken of het concept dan niet te duidelijk wordt, daar zal de opdrachtgever weer niet zo blij mee zijn

De INNODB engine en transactions zitten toch standaard in MySQL? Moet denk ik nog wel even uitzoeken wat de mogelijkheden zijn bij de gebruikte hoster.

Uiteraard heb ik ze betrouwbare/professionele hosting aangeraden, maarja...

[telefoontoestel]

In de regel geldt:

1 op 1 relatie = tabel met foreign key waarbij de zijde het beste bepaald kan worden door de meest gebruikte query.
1 op veel relatie = tabel met foreign key aan de veel kant
veel op veel relatie = tussentabel met foreign keys op de tussentabel.

Je geeft in je OP aan dat:
- de user meerdere gebouwen kan bezitten, maar dat een gebouw het bezit is van een user. Dat is dus een 1 op veel relatie waarbij de foreign key op gebouw moet komen.
- Een gebouw kan meerdere logentries bevatten. Dat is dus ook een 1 op veel relatie waarbij de foreign key op log moet komen.

Je tweede suggestie bevat de tabellen has_building en has_log. Die kun je dan gemakkelijk oplossen door een select te doen op respectievelijk building en log met als conditie respectievelijk de userid en de building id. Dan hoef je enkel nog een count of numrows te doen om te bepalen of er wel of geen eigendom/entries zijn.

Je eerste suggestie is dus de vorm die het meest bij je beschreven probleem past.

Wat betreft foreign keys. Het is gebruikelijk om die altijd naar de primarykey van de gekoppelde tabel te laten verwijzen. Daarvan weet je zeker dat deze uniek is en geen null waarde kan bevatten. Indien de primary key van een tabel breed ligt kun je de foreign key ook breed leggen over de gehele primarykey van de koppeltabel.

[ Voor 18% gewijzigd door telefoontoestel op 21-05-2014 12:57 ]

[Gomez12]

Wat ik eens heel goed zou navragen aan een domein expert is of een gebouw voor jullie doeleinden wel slechts maar 1 actieve eigenaar kan hebben. Ik ken eigenlijk bijna geen gebouwen die behalve wellicht voor het kadaster niet deelbaar zijn qua "eigenaar" / bewoner.

Heb je echt een ondeelbare actieve eigenaar dan alsnog zit je met optie 1 met het probleem dat je niets in de toekomst kan plannen, je moet dus echt de eigenaar gaan wisselen op het moment dat de sleuteloverdracht plaatsvind, ook al is dat wellicht op een zondag.

In principe zou ik het gewoon een n:n relatie maken met een koppeltabel en dan met checks / denormalisatie / batch zo regelen dat er in de building tabel een los veld active_owner gevuld wordt met de juiste eigenaar.
Ik ken de toepassing niet, maar veelal wil je toch metadata ook bewaren over de combo building en owner en die metadata wil je niet enkel aan de building hangen (want als building a morgen een nieuwe eigenaar krijgt kan het makkelijk zo zijn dat de metadata pas over 3 weken van eigenaar verandert)

Zoals ik al zei, ik ken de toepassing niet, maar logisch gezien kan ik eigenlijk geen situatie bedenken waarin ik geen koppeltabel voor zoiets wil hebben maar dat komt wellicht ook omdat ik weinig tot nooit iets te maken hebben met daadwerkelijke gebouweigenaren, maar meer met bedrijven in gebouwen (en dan heb je dus bedrijfsgebouwen, flats, 2-onder 1-kap woningen, boeren met een 2e huisje voor opa/oma achter op de grond etc etc)

[Crazy D]

Je noemt geldige redenen om wel degelijk een koppeltabel te gebruiken maar je moet terug gaan naar de basis: wat is de wens/eis van de klant. Als die zegt, er kan altijd maar 1 eigenaar zijn, zijn die punten op zichzelf geen reden om alsnog een koppeltabel te gebruiken. Het proces van het wijzigen van de eigenaar kun je ook anders oplossen, als dit wel op voorhand (bv nu alvast invoeren met datum 1 juni) moet kunnen. En afhankelijk van hoeveel gebouwen je in het systeem hebt en hoevaak deze van eigenaar wisselen, zijn er genoeg klanten die het geen enkel probleem vinden om het even op de dag zelf in te voeren (of de eerst volgende werkdag erna).

Een active owner veld zal alsnog gevuld moeten worden (of je maakt er een calculated column van).

Je moet nooit teveel bedenken wat ook allemaal zou kunnen, want dat is juist de manier waarop de klant vraagt om notepad en je uitkomt op een word processor...