Database design - Alles in één tabel of niet?

[Spooksel]

Voor een administratie applicatie ben ik bezig met het database design voor een nieuwe versie, de oude is ooit 5 jaar geleden gebouwd in Azie op basis van een vaag obscuur home-made framework wat echt gewoon _te slecht_ is om nog te kunnen blijven gebruiken. Daarnaast is het database design ook wel aan wat upgrades toe, dus is er besloten om opnieuw te gaan bouwen.

In grote lijnen is het wel duidelijk hoe we de data willen gaan structureren, maar er is nog een stukje waar ik mee zit.

In de oude situatie worden de gegevens van personen opgeslagen in twee tabellen 'persons' en 'person_details'. De eerste tabel bevat eigenlijk alle belangrijke informatie (naw, email, bankgegevens, ww, etc) en de tweede tabel is een koppeltabel waarin een hoop secundaire informatie staat over de persoon. Met name dingen als 'is het een cursist j/n', 'is het een docent j/n', 'wat is het uurtarief als docent', etc etc.

Het eerste wat ik eigenlijk al nooit aan deze setup heb begrepen is waarom er uberhaupt een tweede tabel is aangezien IEDERE persoon ALTIJD een record moet hebben in deze gekoppelde tabel, waarom niet al die informatie gewoon in één en dezelfde tabel zou je zeggen. Maargoed, het is nou eenmaal zo...

Maargoed, mijn twijfelstuk is nu of ik uberhaupt ruimte wil reserveren voor die secundaire data in de personen tabel. De informatie in kwestie komt op het totaal gezien misschien maar bij 1% van de records ook echt in beeld. Ik zit er bijvoorbeeld aan te denken om een 'datafields' tabel te maken waarin in een kolom of 4 maak waarin de extra data voor een persoon (en evt ook andere dingen) geplaatst kunnen worden die gewoon niet vaak voorkomen.

De setup voor deze tabel zou dan zijn:

• datafield_id (primary key)
• foreign_id (uniek icm foreign_table)
• foreign_table (uniek icm foreign_id)
• data

We hebben dan echter wel te maken met verschillende soorten data die opgeslagen kan worden, de ene keer zal het een text inhoud zijn, de andere keer een int en weer een andere keer een float of een boolean. Ik zit te denken aan een soort van 'json encoded' dataformaat waarbij ik kan definiëren hoe de data heet, van welk type het is en uiteraard de data zelf, bijvoorbeeld:

{“type”:”varchar”,”name”:”photo”,”content”:”upload/images/persons/10.jpg”}
{“type”:”boolean”,”name”:”is_tutor”,”content”:”1”}
{“type”:”date”,”name”:”is_tutor_since”,”content”:”2012-09-01”}

De controller die de data oproept bij het datafields model krijgt object terug waarin de data is geparsed naar een werkbaar formaat. Het idee is dus dat op deze manier er niet overbodig ruimte gereserveerd wordt in de database, nu kan het bijvoorbeeld zijn dat bij persoon_a men nog 10 additionele objecten terugkrijgt uit de datafields, bij persoon_b misschien zelfs 0 en bij persoon_c weer 4, etc.

Dus, de overweging is:
1. Gewoon een tabel met daarin alle informatie die bij een persoon hoort, een situatie waarbij een GROOT deel van de velden in 99% van de gevallen dus eigenlijk _NIET_ gebruikt gaat worden.
2. Een tabel met daarin de meest relevante data voor een persoon en een tweede tabel waarin de extra alleen worden opgeslagen indien ze nodig zijn.

Thoughts?

Edit: NB: Het gaat dus enkel om secudaire data eigenlijk. Geen data waarop personen gezocht worden of waar superveel mee gewerkt wordt in de eerste lijn. Maar data waarmee rapportages worden samengesteld, betaalbestanden, etc.

[ Voor 3% gewijzigd door Spooksel op 15-02-2015 21:44 ]

[Douweegbertje]

Een koppeltable is wel redelijk te noemen. Ik gebruik dat vrijwel altijd voor user gegevens.
User_id met vervolgens wat dingen als IP, login, e-mail etc en een koppel tabel met alles wat je maar wilt.
Ik wil dat graag gescheiden houden omdat het voor mij gezien andere data is. Een account vs account informatie.

Dat is even het stukje omtrent een koppel tabel al moet je dit misschien niet zo noemen, aangezien je dan meer in de richting moet denken van;

user_id | typedata | data
1 | voornaam | Piet

Terwijl het bij mij vooral één extra table is met

user_id | voornaam | achternaam |

Wij hebben voor een aantal zaken wel een koppel tabel zoals het eerste voorbeeld, maar dat is gewoon een varchar(255) en een extra kolumn over 'wat' voor data het nu is (kenteken, rekeningnummer, id, tekst, etc.. etc..)
Zo'n afweging moet je maken op de hoeveelheid data en waarvoor je het wilt gaan gebruiken. IMO is er niet echt een best practise zo in het algemeen, maar meer gebaseerd op HOE je het wilt gaan gebruiken.

[Spooksel]

Wij hebben een zoekveld standaard in de header van de applicatie zitten, als het ware een 'quick search' (met resultaten terwijl men typed) voor de personen. Een administratief medewerker kan dan op vrij veel dingen een persoon zoeken, denk aan:
- voornaam
- achternaam
- straatnaam
- postcode
- woonplaats
- email

Dit soort informatie komt daarom ook juist niet in zo'n externe datatabel, omdat het a) informatie is die in 99% van de gevallen altijd wel aanwezig is en b) het zijn ook geindexeerde kolommen voor snellere resultaten. Enkele voorbeelden waarvoor ik bijvoorbeeld geen vaste kolom meer zou willen:

1. Een foto referentie. Men kan een foto uploaden en de referentie naar waar die is opgeslagen komt dan in dit veld, dit wordt echter alleen bij docenten gebruikt die <1% van het aantal records zijn.
2. De gebruikte taal van een persoon. Er is nu een kolom met daarin bij _iedere_ persoon de language_id die gebruikt wordt, welke in 99,9% van de gevallen '1' is, oftewel Nederlands. Ik wil de uitvoertaal van het systeem gewoon standaard NL maken en enkel bij personen die het anders willen dan opslaan wat het wel moet zijn.

Met betrekking tot dit wat jij zegt:

Douweegbertje schreef op zondag 15 februari 2015 @ 21:51:
user_id | typedata | data
1 | voornaam | Piet

Terwijl het bij mij vooral één extra table is met

user_id | voornaam | achternaam |

Gebruik jij dan ook verschillende soorten data? varchar/int/float/date/etcetc? Kijk, het dataveld wordt gewoon een 'TEXT', maar hoe zou jij dan aangeven als wat voor soort data de inhoud behandeld moet worden?

[ Voor 17% gewijzigd door Spooksel op 15-02-2015 22:00 ]

[Verwijderd]

wat is precies je reden om die velden NIET op te nemen in je persons tabel? De oplossing die je schetst kom ik tegen in standaard pakketten die flexibiliteit wil bieden aan de verschillende klanten die ze hebben. Het klinkt nu een beetje als moeilijk doen (kort door de bocht). Het kost je een hoop extra werk, en wat is je voordeel?

[NMe]

Spooksel schreef op zondag 15 februari 2015 @ 21:42:
De setup voor deze tabel zou dan zijn:

• datafield_id (primary key)
• foreign_id (uniek icm foreign_table)
• foreign_table (uniek icm foreign_id)
• data

Ah, kijk...de klassieke database-in-een-database.

Ik zou het niet doen, het gaat je dan ineens veel meer werk kosten om de data op te bouwen omdat je niet meer simpelweg kan joinen op tabellen aangezien de tabelnamen ineens als varchar in een andere tabel staan...

Overigens zijn er best redenen de verzinnen waarom een one-to-one-relatie handig kan zijn. Het zou kunnen dat het gebruikte DBMS een raar lock-systeem heeft voor rijen en dan zou het zomaar eens handig kunnen zijn om data die veel gewijzigd wordt in een andere tabel te zetten dan data die vooral veel gelezen wordt. Of misschien is het handig de data gescheiden te houden vanwege je DAL. Hoe dan ook is het alternatief eerder om de data dan maar allemaal in die ene persons-tabel te stoppen en niet om een database in een database te gaan bouwen.

Wat Douweegbertje in "Database design - Alles in één tabel of niet?" zegt is trouwens fundamenteel anders omdat hij een tabel voorstelt die alleen gekoppeld wordt met persons en niet potentieel met andere tabellen, wat jij wel voorstelt in je topicstart. Overigens ben ik ook geen voorstander van de oplossing van Douweegbertje omdat dat ook wat zaken complexer maakt dan nodig en indexen leggen op die velden lastiger wordt, maar die oplossing is in elk geval beter houdbaar dan jouw eigen voorstel.

[ Voor 19% gewijzigd door NMe op 16-02-2015 13:13 ]

[P_Tingen]

Als de overweging is dat de velden onnodig ruimte innemen omdat de gegevens maar bij <1% van de mensen voorkomt dan zou ik dat zo aan de kant zetten; opslag maak ik me bij databases geen zorgen over. Rare constructies bedenken om ruimte te besparen kost veel meer ergernis op de lange duur dan de ergernis over lege columns.

Ook zou ik de tabellen samenvoegen, tenzij er een hele goede reden is waarom het nu zo is. In de applicatie waar ik nu aan werk heb ik dat bijvoorbeeld. Ik heb een available_lot tabel met beschikbare voorraad en een available_lot_details tabel met aanvullende gegevens. De relatie is 1:1 waarbij de details tabel evt niet kan bestaan. De reden hierachter is dat de available_lot tabel automatisch wordt bijgewerkt met actuele voorraden. Het kan best zijn dat een voorraad verplaatst wordt; er worden dan records uit de tabel verwijderd. Als de voorraad later dan weer teruggelegd wordt, "verschijnt" mijn record weer en dan wil ik de details nog steeds weten. Soortgelijke reden zou hier misschien ook achter kunnen liggen maar daar zul je zelf achter moeten zien te komen.

[Spooksel]

NMe schreef op maandag 16 februari 2015 @ 01:24:
[...]Overigens zijn er best redenen de verzinnen waarom een one-on-one-relatie handig kan zijn. Het zou kunnen dat het gebruikte DBMS een raar lock-systeem heeft voor rijen en dan zou het zomaar eens handig kunnen zijn om data die veel gewijzigd wordt in een andere tabel te zetten dan data die vooral veel gelezen wordt. Of misschien is het handig de data gescheiden te houden vanwege je DAL. Hoe dan ook is het alternatief eerder om de data dan maar allemaal in die ene persons-tabel te stoppen en niet om een database in een database te gaan bouwen.

Mjah, dat is iets wat ik in de huidige setup ook altijd wel vreemd gevonden heb. Het zijn nu dus twee tabellen, maar de informatie uit beide wordt altijd gecombineerd opgehaald voor de opbouw van het show/edit scherm voor een persoon. En bij het opslaan wordt alle data in de controller weer netjes opgesplitst en weggeschreven naar beide tabellen

Er komen ook verder niet echt situaties voor in het hele systeem waarbij je enkel wat data van een persoon nodig zou hebben uit de tweede tabel en niet uit de eerste. Dus ik ben nu echt altijd wel met joins bezig terwijl dat helemaal niet nodig is.

Dus ja, in dat opzicht kan alle data zeker wel gewoon naar één tabel toe. Maargoed, alles gewoon lekker in één tabel houden ipv weinig gebuikte velden af gaan splitsen is dus het advies Daar gaan we dan voor.

Het enige wat ik er uit trek dan zijn de notities bij een persoon, deze wilde ik sowieso al om gaan zetten naar een 1-n relatie met tracking van wie een notitie gemaakt heeft en wanneer

[ Voor 8% gewijzigd door Spooksel op 16-02-2015 10:07 ]

[CurlyMo]

Mocht er toch een goede reden zijn om twee tabellen te hebben in 1% van de gevallen, dan kan je voor die overige 99% een view gebruiken. Je INSERT, UPDATE en DELETE queries draai je dan ook gewoon op je view welke vervolgens via triggers opgesplitst worden naar de twee tabellen.

[ Voor 53% gewijzigd door CurlyMo op 16-02-2015 11:55 ]

[mcDavid]

Ik denk dat de voornaamste vraag die je je moet stellen is: Hoe flexibel moet je applicatie zijn?
Als je een redelijk afgebakend aantal datavelden hebt en het toevoegen of wijzigen hiervan altijd gepaard zal gaan met de uitrol van een nieuwe versie van je applicatie, zou ik er lekker één grote tabel van maken.
Als het onderdeel van de functionaliteit van de applicatie wordt dat er velden toegevoegd of verwijderd kunnen worden, zou ik voor een flexibeler setup gaan met wat NMe een "database in een database" noemt.

[The Eagle]

Ik kan je wel vertellen dat dit idee ook door de wereldmarktleider in HR ERP gebruikt wordt
En op zich is het niet eens een heel gek idee, omdat je mensen al kunt categoriseren en opnemen in een systeem, zonder dat je er een hele riedel aan gegevens van nodig hebt.
Paar interessante linkjes:
http://peoplesoftconcept....soft-91-person-model.html
http://srini-peoplesoft.b...2012/05/person-model.html

En als je op "peoplesoft person model" google't kom je ook een hoop tegen.
Ik heb destijds de overstap meegemaakt van een "normaal" HR ERD naar het person model, en het is echt ideaal. Qua applicatie is het slechts een kwestie van de juiste tabellen goed combineren.

[CurlyMo]

Ik ooit een administratie database gemaakt voor een uitgeverij. Het idee was:
- A krijgt een tijdschrift op afleveradres van A en factuuradres is ook van A.
- A krijgt een tijdschrift op afleveradres A maar betaald door B
- A en B krijgen een tijdschrift op afleveradressen A en B betaald door C
Enz.

Een gebruiker kon dus geen, één of meerdere afleveradressen hebben en een gebruiker kon ook een factuuradres hebben die eventueel anders was dan het afleveradres. In dat geval was het dus wel handig om een aparte adressen tabel te hebben los van de gebruikers account tabel.

[Dekaasboer]

CurlyMo schreef op maandag 16 februari 2015 @ 12:25:
Ik ooit een administratie database gemaakt voor een uitgeverij. Het idee was:
- A krijgt een tijdschrift op afleveradres van A en factuuradres is ook van A.
- A krijgt een tijdschrift op afleveradres A maar betaald door B
- A en B krijgen een tijdschrift op afleveradressen A en B betaald door C
Enz.

Een gebruiker kon dus geen, één of meerdere afleveradressen hebben en een gebruiker kon ook een factuuradres hebben die eventueel anders was dan het afleveradres. In dat geval was het dus wel handig om een aparte adressen tabel te hebben los van de gebruikers account tabel.

Precies waar ik ook aan zat te denken.

Zelf werk ik met ERP en HR systemen. Je gaat normaliseren vanwege performance en 1:n relaties, security, en efficiënte updates.
Elementen die je eigenlijk altijd in seperate tabellen wil hebben:
-adressen\ contactgegevens
-bankrekeningen
-dienstverbanden
-posities
-functies
-blobs (foto's, data)

Naast dat je bijvoorbeel meerdere adressen voor meerdere functies kan vastleggen kan je ook je data historisch vastleggen.

[Spooksel]

We zijn tot nu toe altijd redelijk flexibel geweest met het uitbreiden van onze applicatie nav wensen van de klanten, hetgeen oa ook enorm geholpen heeft bij het aantrekken van nieuwe klanten

Als we een prospect was die zoiets had van 'Ja, maar mijn huidige pakket heeft ook een veldje waar ik X of Y kan invullen bij een cursist... hebben jullie dat ook?' Dan kijken wij eerst of we het zouden kunnen opvangen met iets wat al aanwezig is (was vaak zo) en anders, prima... dan voegen we dat toch toe voor je! 'Wat moet het doen? Moet het in een excel uitdraai erbij komen, you got it!'

Maargoed, dat soort flexibiliteit ging dus wel hand in hand met 'een databaseveldje erbij hier, een databaseveldje erbij daar'. Al met al zijn het wel typisch van die dingen waarbij het 'database in een database' idee best goed zou werken denk ik, want het gaat wederom nooit om data die betrokken is bij cruciale processen in de applicatie.

[Gomez12]

Spooksel schreef op maandag 16 februari 2015 @ 12:35:
Al met al zijn het wel typisch van die dingen waarbij het 'database in een database' idee best goed zou werken denk ik, want het gaat wederom nooit om data die betrokken is bij cruciale processen in de applicatie.

Database in een database is altijd een slecht idee, het klinkt heel leuk in het begin maar na 1 a 2 jaar sla je jezelf voor de kop hoe je dit ooit hebt kunnen opzetten

[NMe]

Nouja, voor elk "slecht" ontwerp is er wel een toepassing te verzinnen waar het handig is. Maar de kans dat je nét die ene toepassing moet bouwen is redelijk nihil.

[cyberstalker]

Ik weet niet welke database je gebruikt, maar zowel bijvoorbeeld postgresql als mariadb ondersteunen ook dynamic columns. Dan sla je de belangrijkste gegevens gewoon in kolommen op en de zaken die maar sporadisch gevuld danwel gelezen worden in een dynamic column.

Op die manier hou je alle gegevens in een enkele table zonder dat je een overmaat aan nauwelijks gebruikte kolommen krijgt.

[Spooksel]

Het gaat hier om MySQL

[Johnny]

Gewoon extra kolommen toevoegen, dit heeft een verwaarloosbaar effect op de performance. Het is ook extreem flexibel omdat je altijd meer kolommen kan blijven toevoegen zonder het risico iets te slopen (tenzij je het heel bont maakt). Alle andere oplossingen voegen meer complexiteit toe zonder duidelijk voordeel.

Als programmeur is het beheersen van complexiteit de je grootste uitdaging, maak dingen zo simpel mogelijk, niet zo flexibel mogelijk.

[ Voor 18% gewijzigd door Johnny op 16-02-2015 14:51 ]

[NMe]

Johnny schreef op maandag 16 februari 2015 @ 14:48:
Gewoon extra kolommen toevoegen, dit heeft een verwaarloosbaar effect op de performance. Het is ook extreem flexibel omdat je altijd meer kolommen kan blijven toevoegen zonder het risico iets te slopen (tenzij je het heel bont maakt). Alle andere oplossingen voegen meer complexiteit toe zonder duidelijk voordeel.

Als programmeur is het beheersen van complexiteit de je grootste uitdaging, maak dingen zo simpel mogelijk, niet zo flexibel mogelijk.

Nouja, dit advies is natuurlijk ook niet altijd goed. Dit werkt namelijk alleen maar onder het voorbehoud dat je éérst goed genormaliseerd hebt. Nou doe jij dat vast wel en kwam het daarom niet in je op om dat voorbehoud erbij te benoemen, maar het lijkt me wel handig om die opmerking erbij te zetten voor de topicstarter en eventuele lezers die hier later via de search bij uitkomen.

Overigens is de impact van een extra kolom sowieso redelijk miniem, helemaal als je iets van ORM gebruikt. Even je code aanpassen en je database verandert wel mee (conversie van formaat A naar formaat B even buiten beschouwing gelaten ).

[mcDavid]

Spooksel schreef op maandag 16 februari 2015 @ 12:35:
We zijn tot nu toe altijd redelijk flexibel geweest met het uitbreiden van onze applicatie nav wensen van de klanten, hetgeen oa ook enorm geholpen heeft bij het aantrekken van nieuwe klanten

Als we een prospect was die zoiets had van 'Ja, maar mijn huidige pakket heeft ook een veldje waar ik X of Y kan invullen bij een cursist... hebben jullie dat ook?' Dan kijken wij eerst of we het zouden kunnen opvangen met iets wat al aanwezig is (was vaak zo) en anders, prima... dan voegen we dat toch toe voor je! 'Wat moet het doen? Moet het in een excel uitdraai erbij komen, you got it!'

Maargoed, dat soort flexibiliteit ging dus wel hand in hand met 'een databaseveldje erbij hier, een databaseveldje erbij daar'. Al met al zijn het wel typisch van die dingen waarbij het 'database in een database' idee best goed zou werken denk ik, want het gaat wederom nooit om data die betrokken is bij cruciale processen in de applicatie.

Met "flexibel" doel ik echt op toepassingen waarin de gebruiker zelf, per record, velden moet kunnen toevoegen of verwijderen naar gelang dat nodig is. Ik kan me voorstellen dat je niet graag ALTER TABLE queries in je businesslogic hebt. Maar zo te horen is dat niet van toepassing. Als je er voor een extra veld ook extra functionaliteit bij moet bouwen, zal dat toch een update van de applicatie betekenen. Ik zou dan niet allerlei ingewikkelde/onhandige constructies gaan toepassen om te voorkomen dat je daarbij ook je database schema moet updaten.

[Dets]

Als ik het zo lees vraag ik me af wat er mis met het ouderwets normaliseren tot de derde normaalvorm. Dan kijken wat je er van gemaakt heb, toegangspadanalyse doen en daarna denormaliseren voor oa. performance etc.

Verder als de set niet uitbreidbaar hoeft te zijn in dezelfde tabel opnemen. Alle attributen zijn afhankelijk van dezelfde sleutel.
Als de set uitbreidbaar moet zijn ontstaat er van zelf een extra sleutelelement en wordt het een nieuwe tabel, je krijgt dan een 1-op-n relatie.

[ Voor 39% gewijzigd door Dets op 16-02-2015 20:43 ]

[CurlyMo]

Als je serieus overweegt om een ALTER TABLEs of iets dergelijks toe te laten, dan zou ik kiezen voor de implementatie zoals beschreven door: Douweegbertje in "Database design - Alles in één tabel of niet?"

[Verwijderd]

The Eagle schreef op maandag 16 februari 2015 @ 12:17:
Ik kan je wel vertellen dat dit idee ook door de wereldmarktleider in HR ERP gebruikt wordt
En op zich is het niet eens een heel gek idee, omdat je mensen al kunt categoriseren en opnemen in een systeem, zonder dat je er een hele riedel aan gegevens van nodig hebt.
Paar interessante linkjes:
http://peoplesoftconcept....soft-91-person-model.html
http://srini-peoplesoft.b...2012/05/person-model.html

En als je op "peoplesoft person model" google't kom je ook een hoop tegen.
Ik heb destijds de overstap meegemaakt van een "normaal" HR ERD naar het person model, en het is echt ideaal. Qua applicatie is het slechts een kwestie van de juiste tabellen goed combineren.

Voor dat doel is het inderdaad prima toepasbaar. Maar er zit wel een verschil in een standaard pakket dat flexibel moet zijn (vanwege duizenden klanten met eigen wensen) of een volledig maatwerk systeem. Ligt dus iets genuanceerder imo.

[EfBe]

Maak eerst een entity relationship model of abstract entity model in bv NIAM en projecteer dat dan naar een tabellenstructuur. Even hier en daar wat kolommen toevoegen valt in de categorie knoeien, want zonder een abstract entity model is er geen theoretische basis om kolom X even bij tabelletje T toe te voegen. Abstract entity models projecteren automatisch naar een table structuur in de 3e normaalvorm.

Hoeft helemaal niet moeilijk te zijn, je kunt vrij snel een modelletje maken op papier.

Het verbaast me eerlijk gezegd een beetje dat hier bijna alleen maar de JBF methodiek wordt toegepast.