[SQL] Hoe ver door normaliseren?

[DimitryK]

Dag allemaal,

Momenteel ben ik bezig met het ontwerpen van de database structuur voor mijn platform. Tijdens dit proces liep ik tegen een probleem aan. Om even de situatie te schetsen:

Stel ik heb een database met auto's. Voor ieder merk heb ik alle modellen en deze modellen hebben technische specificaties. De database structuur hiervoor is nu als volgt:

manufacturers

• id
• name

models

• id
• name
• year
• manufacturer_id

properties

• id
• key
• name

model_properties

• property_id
• model_id
• value

Op het eerste gezicht werkt het best aardig. Als er nieuwe properties bij komen voor bepaalde modellen is het geen probleem om deze toe te voegen. Echter, in de model_properties tabel is nu nog veel redundantie. Stel we kijken naar het aantal versnellingen, dan zijn er makkelijk een paar duizend modellen die 5 versnellingen hebben.

Natuurlijk kan dit dan ook weer verder genormaliseerd worden, maar hoe ver moet je hierin gaan en wat kan ik in het huidige geval het beste doen? Ik kan er wel nog een tabel bij maken, met daarin de verschillende waarden, maar is dit dan efficient?

Dus dat het bijv dit wordt:

model_properties

• property_id
• model_id
• value_id

model_property_values

• id
• value

Graag hoor ik wat jullie kijk is op dit probleem! Bij voorbaat dank voor de moeite

[ Voor 7% gewijzigd door DimitryK op 29-04-2016 21:46 . Reden: Extra voorbeeld ]

[CurlyMo]

Je normaliseert zover door zolang het voor jouw doel nog functioneel is. Bedenk ook dat je altijd nog kan denormaliseren via views. Daarmee kan je in feite 'datasets' bovenop je (sterk) genormaliseerde structuur maken die voor je applicatie handzamer zijn om mee te werken.

[DimitryK]

CurlyMo schreef op vrijdag 29 april 2016 @ 21:48:
Je normaliseert zover door zolang het voor jouw doel nog functioneel is. Bedenk ook dat je altijd nog kan denormaliseren via views. Daarmee kan je in feite 'datasets' bovenop je (sterk) genormaliseerde structuur maken die voor je applicatie handzamer zijn om mee te werken.

Denormalisatie is geen probleem, maar de data moet wel werkbaar blijven. Niet dat als er uiteindelijk 1 ding in de database aangepast moet worden je door de verre normalisatie hier problemen mee krijgt (door de bomen het bos niet meer zien). Daar ben ik juist bang voor...

[CurlyMo]

Je model moet altijd 'logisch' zijn. Iemand die je ERD ziet zou moeten kunnen zien hoe de relaties lopen en deze moeten zonder of met geringe documentatie te begrijpen zijn (mits deze persoon de context kent). Je normalisatie moet dusdanig complex/simpel zijn dat juist een wijziging zo min mogelijk complexiteit met zich mee brengt.

In jouw voorbeeld is het alleen zinnig om een model_property_values te maken wanneer deze een 1-n relatie heeft met model_properties. Bij een 1-1 relatie is het onzinnig.

[ValHallASW]

Je moet je ook afvragen of je überhaupt een properties-tabel wilt hebben: waarom maak je niet gewoon per property een kolom aan? Wikipedia: Inner-platform effect

[CurlyMo]

ValHallASW schreef op vrijdag 29 april 2016 @ 22:02:
Je moet je ook afvragen of je überhaupt een properties-tabel wilt hebben: waarom maak je niet gewoon per property een kolom aan?

Omdat je properties dan niet meer dynamisch schalen.

[DimitryK]

CurlyMo schreef op vrijdag 29 april 2016 @ 21:55:
Je model moet altijd 'logisch' zijn. Iemand die je ERD ziet zou moeten kunnen zien hoe de relaties lopen en deze moeten zonder of met geringe documentatie te begrijpen zijn (mits deze persoon de context kent). Je normalisatie moet dusdanig complex/simpel zijn dat juist een wijziging zo min mogelijk complexiteit met zich mee brengt.

In jouw voorbeeld is het alleen zinnig om een model_property_values te maken wanneer deze een 1-n relatie heeft met model_properties[/b]. Bij een 1-1 relatie is het onzinnig.

Veel properties delen dezelfde waarde, bijvoorbeeld type motor, aantal versnellingen etc. Het gros is dus 1-n.

ValHallASW schreef op vrijdag 29 april 2016 @ 22:02:
Je moet je ook afvragen of je überhaupt een properties-tabel wilt hebben: waarom maak je niet gewoon per property een kolom aan? Wikipedia: Inner-platform effect

Niet alle modellen hebben alle properties, sommigen hebben ze allemaal (pakweg 50), maar anderen hebben er maar 10 die bekend zijn. Als ik voor iedere property een extra kolom aan zou maken, dan zou ik eveneens enorm veel redundantie hebben, maar ook erg veel lege velden. Eveneens weet ik uit ervaring dat naarmate die tabel groeit het nogal even kan duren om een nieuwe kolom toe te voegen als dat nodig mocht zijn.

[ Voor 5% gewijzigd door DimitryK op 29-04-2016 22:06 ]

[CurlyMo]

Als er 1-n relaties zijn dan is het zinnig om twee tabellen te maken om redundantie te voorkomen.

[ajakkes]

Zaken die er altijd of bijna altijd bij horen, opslaan in een model-property-value is niet echt efficiënt. Zoals: brandstof, versnellingen, aantal deuren, vermogen.

Maar overige opties zoals cabriolet e.d. kan wel efficiënt zijn.

Ik ken een database model dat object types, property types, objecttype-propertytype, object-objecttype heeft waardoor de properties gekoppeld worden. Hier hangen dan weer object-propertytype-values aan.

Zeer flexibel. Maar ook onoverzichtelijk en zwaar.

[Orion84]

die property value lostrekken is natuurlijk vrij zinloos. In plaats van een value attribuut krijg je dan een ID attribuut, en een extra tabel, het aantal records wordt er niks lager op je voegt alleen extra complexiteit toe.

Dat is alleen nuttig als die property value uit meerdere attributen zou bestaan (of eventueel wanneer daar hele lange strings in kunnen komen die significant meer ruimte innemen dan een ID).

Hetzelfde voor het merk trouwens, tenzij je van een merk nog extra info op wil gaan slaan (land van oorsprong of zo), kan je het net zo goed als kolom opnemen in models.

[Edwin88]

DimitryK schreef op vrijdag 29 april 2016 @ 22:05:
Niet alle modellen hebben alle properties, sommigen hebben ze allemaal (pakweg 50), maar anderen hebben er maar 10 die bekend zijn. Als ik voor iedere property een extra kolom aan zou maken, dan zou ik eveneens enorm veel redundantie hebben, maar ook erg veel lege velden. Eveneens weet ik uit ervaring dat naarmate die tabel groeit het nogal even kan duren om een nieuwe kolom toe te voegen als dat nodig mocht zijn.

Ik zou gaan voor de mooie middenweg. Dingen als aantal PK's, aantal wielen, merk, aantal deuren, enzovoort zijn dingen die vrijwel in elke entry van toepassing zijn. Lege tabellen is niet zo erg in de uitzonderingsgevallen.

Ik denk dat je qua performance meer moet inleveren als je dat soort algemene data opslaat in aparte tabellen. Daar is een relationeel database model niet op geoptimaliseerd!

En, mocht je ooit een column extra toevoegen: dan duurt dat toch wat langer. Want hoe vaak doe je dat? 1 x in het komende jaar?

Je kan ook veel makkelijker filteren op je waardes: als iemand modellen met 3 deuren wilt is een simpele where statement voldoende.

[DimitryK]

Edwin88 schreef op zaterdag 30 april 2016 @ 09:07:
[...]


Ik zou gaan voor de mooie middenweg. Dingen als aantal PK's, aantal wielen, merk, aantal deuren, enzovoort zijn dingen die vrijwel in elke entry van toepassing zijn. Lege tabellen is niet zo erg in de uitzonderingsgevallen.

Ik denk dat je qua performance meer moet inleveren als je dat soort algemene data opslaat in aparte tabellen. Daar is een relationeel database model niet op geoptimaliseerd!

En, mocht je ooit een column extra toevoegen: dan duurt dat toch wat langer. Want hoe vaak doe je dat? 1 x in het komende jaar?

Je kan ook veel makkelijker filteren op je waardes: als iemand modellen met 3 deuren wilt is een simpele where statement voldoende.

Daar had ik in het begin ook al zo over gedacht. Het zorgt alleen wel voor dezelfde redundantie als nu...

[Gomez12]

Orion84 schreef op zaterdag 30 april 2016 @ 08:11:
Hetzelfde voor het merk trouwens, tenzij je van een merk nog extra info op wil gaan slaan (land van oorsprong of zo), kan je het net zo goed als kolom opnemen in models.

Merk vind ik dan wel weer een moeilijke omdat die veelal commercieel gevoelig ligt qua schrijfwijze waardoor er aan de output-kant veelal maar 1 variant van mag zijn en dan kom ik veelal toch uit op sla maar 1x het merk op in een losse tabel

[JaQ]

Het is m.i. erg onhandig om een key-value oplossing in een relationele database te gaan maken. Ik weet dat het kan, maar dat is geen reden om het te doen. Het schijnt dat NoSQL databases ondertussen redelijk volwassen zijn

[Zoijar]

JaQ schreef op zondag 01 mei 2016 @ 00:59:
Het schijnt dat NoSQL databases ondertussen redelijk volwassen zijn nog steeds allemaal shit zijn als je redelijk gestructureerde data betrouwbaar op wilt slaan (en je minder dan zeg 2-3 petabyte aan data hebt, fair is fair)

ftfy

[ Voor 10% gewijzigd door Zoijar op 01-05-2016 08:57 ]

[ACM]

JaQ schreef op zondag 01 mei 2016 @ 00:59:
Het is m.i. erg onhandig om een key-value oplossing in een relationele database te gaan maken. Ik weet dat het kan, maar dat is geen reden om het te doen. Het schijnt dat NoSQL databases ondertussen redelijk volwassen zijn

Volgens mij is het in dit soort scenario's vrij gebruikelijk om een nosql-database toe te voegen, maar niet per se om die ook leidend te laten zijn. Als je bovenstaande model via bijvoorbeeld Elastic Search gedenormaliseerd doorzoekbaar maakt, dan ben je al die performance-vragen kwijt, maar sla je het in je leidende relationele database wel nog steeds triviaal uitbreidbaar op.

Edwin88 schreef op zaterdag 30 april 2016 @ 09:07:
Ik zou gaan voor de mooie middenweg. Dingen als aantal PK's, aantal wielen, merk, aantal deuren, enzovoort zijn dingen die vrijwel in elke entry van toepassing zijn. Lege tabellen is niet zo erg in de uitzonderingsgevallen.

Het probleem van de optie om een deel wel en niet te normaliseren, is dat je uiteindelijk een hybride model aan het opzetten bent dat dan ook weer extra complexiteit met zich mee brengt.

Je kan natuurlijk wel besluiten dat bepaalde eigenschappen uberhaupt niet van hetzelfde soort zijn; zoals anderen al aangeven zijn er bepaalde zaken die wel en niet als onderdeel van een auto worden gezien. Zie bijv de informatie die de RDW als basisinformatie toont versus de lijst accessoires die bij een auto is geleverd.

Idealiter zou je daadwerkelijk de relationele aspecten leidend laten zijn bij de keuzes; is iets een verplicht onderdeel van een model, dan zou het ook in dat model een kolom horen te krijgen; maar dan bij repetitieve waardes wel zoveel mogelijk in waardetabellen plaatsen (bijv type auto: 'stationwagon', 'cabriolet'). Bij waardes waarvan het niet logisch is dat ze gedeeld worden door anderen (imho iig vermogen en gewicht) hoort imho niet zo'n waardetabel.

Alle aanvullende eigenschappen zouden dan een in een key-value tabel kunnen (of een key-valueid tabel).
Ook daar zou ik trouwens wel voorkomen dat onlogische opties een verplicht extra veld opleveren (wederom vermogen en gewicht als voorbeeld).

Als het eigenlijk allemaal niet zo boeiend is dat sommige eigenschappen meer of minder gebruikelijk zijn, dan zou je kunnen overwegen het allemaal volgens het key-value/valueid model op te zetten. Die complexe variant lijkt toch wel nodig, waarna je dan in ieder geval alsnog maar één systeem hoeft te gebruiken ipv twee door elkaar.
Als snelle doorzoekbaarheid noodzakelijk is, dan is het sowieso verstandig te kijken of je een aanvullende gedenormaliseerde view kan aanmaken, bijvoorbeeld via de route van een nosql document store (zoals elastic search).

[ Voor 6% gewijzigd door ACM op 01-05-2016 17:33 ]

[DimitryK]

Heb dit weekend nog eens goed nagedacht en denk dat ik mijn project ga bouwen volgens een Microservices Architecture. Dan kan ik de "lastige" dingen zoals deze vraag aanpakken met NoSQL en andere dingen met een relationele database.

[Gomez12]

DimitryK schreef op maandag 02 mei 2016 @ 10:11:
Heb dit weekend nog eens goed nagedacht en denk dat ik mijn project ga bouwen volgens een Microservices Architecture. Dan kan ik de "lastige" dingen zoals deze vraag aanpakken met NoSQL en andere dingen met een relationele database.

Tja, maak je het daarmee niet enkel nog maar lastiger?

Je begon met 1 lastige relationele database, nu ga je naar 3 systemen en allemaal zullen ze hun eigen lastige puntjes hebben...