Hvordan best modellere en fleksibel løsning for felt/verdi i en database?

[comichael]

Hvordan ville du modellert en DB for produktspesifikasjoner - flere hundre kategorier der alle har forskjellige felt, og mulighet for gruppering av felt?

Se feks produktspesifikasjonene på Komplett.no [eksempel].

-Key/value-løsning ("database i databasen")
-Field1, Field2.... Field100 => Alias for egentlig felt
-ADD TABLE/ADD FIELD
-Dokument-orientert database

Jeg har erfaring med key/value-løsningen, men det er forferdelig å jobbe med (rapportering etc), og gir elendig performance. Noen som har erfaring med fordeler/ulemper med de andre alternativene? Finnes det andre gode alternativer?

 

[RipZ]

Jeg ville hatt en database med følgende tabeller gitt at et felt alltid skal ligge under en bestemt gruppe (i en bestemt rekkefølge) og at kategoriene varrierer i stor grad (slik at vi ikke tar hensyn til arv av felter etc.):

Data for kategorier:
categories (id, name)
category_fields(cat_id, field_id)
fields (id, group_id, name)
field_groups(id, name)

Data for produktspesifikasjon:
products(id, cat_id, name)
product_fields(product_id, field_id, data)

Veldig simplifisert eksempel som viser tankegangen med dynamiske kategorier. Så lenge du legger inn indeks på alle nøkler så vil den være superrask. Legg merke til at det er en mange til mange relasjon mellom et felt og kategorier. Vi ønsker å kunne sammenligne produkter på tvers av kategorier gitt at de har de samme feltene.

 

[Pong]

Sleng alt i en tabell (f.eks. produkt_detaljer):
produkt_pk, kategori, felt, verdi (samt nullable sort i tilfelle).
kategori, felt og verdi alle varchar.

Idag modellerer vi vel ikke lenger for å spare plass, men for å gjøre det lett å rapportere og tilpasse. Det "koster" ikke så mye nå lenger å kjøre noen spørringer mot databasen, som før.
Men hvis du snakker om 10000-vis av artikler så er en fullverdig normalisert database (som ripz beskriver) vel å foretrekke. Ulempen med den (synes jeg) er at du fort får en del inner joins hvis du skal ha kategori-navn i samme resultset som feltet osv.

 

[RipZ]

Hvis man skal bygge en database, gjør det skikkelig. Jeg har sett alt for mange eksempler på sider som ikke skalerer.

Det er mye lettere å rapportere om man har en normalisert database siden man da er sikker på at dataen er konsistent.

Å slenge alt i en tabell er en utrolig dårlig løsning. Hvordan i all verden kan du argumentere for at "det er enkelt å tilpasse" en løsning ved bruk av kun en tabell? Hva er ulempen med joins?

Man har ingen kontroll over hvilke felter som er obligatoriske. Man har ingen mulighet til å lage et admin-panel hvor man får opp feltene man skal fylle inn for hver kategori. Man må faktisk basere seg på at den som legger inn et nytt produkt vet hvilke felter som skal fylles inn.

Hver gang man skal legge inn et nytt felt, må man gjøre endringer på tabellstrukturen. Om man skal ha støtte for hundrevis av kategorier, sier det seg selv at dette er en veldig dårlig løsning.

I dag modellerer vi fortsatt for at løsningene vi lager skal kunne skalere. Og vi ønsker ikke å gå inn å endre kode og databasestruktur hver gang vi legger til en ny kategori når det er snakk om flere hundre kategorier.

 

[Pong]

Må si jeg reagerer litt på at du påstår at løsningen min er "utrolig dårlig".
Du gir noen forklaringer på hvorfor (hvis jeg kan lese det slik ihvertfall), men nå synes jeg de var så-så.

Du nevner at det er ingen mulighet å fortelle hvilke felter som skal være obligatoriske - men det finner jeg heller ikke i din modell (uten at du tilpasser modellen (= legger til et felt) og programmerer inn en contraint). En admin-panel var vel heller ikke noe krav.

Skal du "bygge det skykkelig", så kan du droppe tabell categories for eksempel (jeg regner med at du legger til en secundary key på feltet name). Du antar at serveren jobber kjappere med tall enn tekststreng, men det går på bekostning at det blir lagt til en ekstra tabell. Da kan du likesågreit bytte ut cat_id med cat_name i tabell category_fields. Og kaller du det for "kategori", så finner du feltet i min 'produkt_detaljer' tabell.

Det (enkle) forslaget mitt klarer fint nye felt (eller kategorier) uten noen endring i strukturen. Det blir jo nye rows.

Ulempen med joins er at det blir en mye større sql-setning som en utvikler må lese når det skal mekkes på applikasjonen. Og AT det skal mekkes på en app er klart. Og AT en app skal dø en gang er klart (og da er sjansen stor for at dataene skal ut også - de pleier leve videre). Tid er penger, og tid i framtiden er enda mer penger.

Har du lest denne forresten? Ganske kul: Amazon.com: The Cathedral & the Bazaar: Musings on Linux and Open Source by an Accidental Revolutionary (0636920001089): Eric S. Raymond: Books

Det er garantert at en enkel løsning er utrolig dårlig i noen tilfeller, akkurat som en komplisert løsning kan være utrolig dårlig i noen andre tilfeller. Men jeg tror ikke man kan påstå at noe er utrolig dårlig punktum når man ikke vet noe om rammene.

Eksemplet fra komplett.no er ikke noe problem å legge inn i produkt_detaljer (og komplett har bare sånn litt over 10.000 artikler (blir vel ikke mer enn en million poster? det var tøft med sql server 6 i '95, men er ikke veldig spesielt nå lenger). Og så kan det tenkes at de kjører en del caching mot nettserveren også. Noen som vet hvem som drifter for komplett?

 

[RipZ]

Du nevner at det er ingen mulighet å fortelle hvilke felter som skal være obligatoriske - men det finner jeg heller ikke i din modell (uten at du tilpasser modellen (= legger til et felt) og programmerer inn en contraint). En admin-panel var vel heller ikke noe krav.

Det er helt sant. Og meningen med strukturen var å vise tankegangen, ikke selve implementasjonen. Legger man til et felt, så har man det på plass.

Skal du "bygge det skykkelig", så kan du droppe tabell categories for eksempel (jeg regner med at du legger til en secundary key på feltet name). Du antar at serveren jobber kjappere med tall enn tekststreng, men det går på bekostning at det blir lagt til en ekstra tabell. Da kan du likesågreit bytte ut cat_id med cat_name i tabell category_fields. Og kaller du det for "kategori", så finner du feltet i min 'produkt_detaljer' tabell.

Så lenge man indekserer en tekststreng, er kostnaden av å bruke tekststrenger minimal, så der har du et poeng. Men det er naturlig å anta at en kategori vil ha mer data enn et navn, derfor bruker jeg en egen tabell.

Det (enkle) forslaget mitt klarer fint nye felt (eller kategorier) uten noen endring i strukturen. Det blir jo nye rows.

Men det gir ingen mening med kategorier når du på ingen måte kan garantere for at produktbeskrivelser deler noe som helst av data gitt en bestemt kategori. Man har faktisk ingenting som garanterer for at to produktbeskrivelser deler noen av de samme feltene. Du snakker om å gjøre ting enkelt å rapportere. Jeg kan ikke komme på et argument for at en slik struktur gjør det enklere å rapportere.

Ulempen med joins er at det blir en mye større sql-setning som en utvikler må lese når det skal mekkes på applikasjonen.

Tuller du nå? Lange SQL-setninger? Det blir som å si at det er lettere å lese en bok uten punktum, store bokstaver og mellomrom fordi det er mindre å lese. Enhver utvikler som jobber mot databaser vet hvordan man leser natural joins.

Og AT det skal mekkes på en app er klart. Og AT en app skal dø en gang er klart (og da er sjansen stor for at dataene skal ut også - de pleier leve videre). Tid er penger, og tid i framtiden er enda mer penger.

Her argumenterer du vel strengt tatt mot deg selv.

Har du lest denne forresten? Ganske kul: Amazon.com: The Cathedral & the Bazaar: Musings on Linux and Open Source by an Accidental Revolutionary (0636920001089): Eric S. Raymond: Books

Nei.

Det er garantert at en enkel løsning er utrolig dårlig i noen tilfeller, akkurat som en komplisert løsning kan være utrolig dårlig i noen andre tilfeller. Men jeg tror ikke man kan påstå at noe er utrolig dårlig punktum når man ikke vet noe om rammene.

Helt enig. Men når han snakker om flere hundre kategoriene, så er vel rammene lagt?

Når man bygger en tabellstruktur må man ta til følge at kunden ofte har en rekke implisitte krav. Da holder det ikke med en løsning som holder "akkurat" (eller "akkurat ikke") for de eksplisitte kravene.

 

[comichael]

Takker for innspill her. Dette er ikke en konkret case jeg jobber med, men et generelt problem som dukker opp i mange sammenhenger der databaseskjemaet ikke er spikret på forhånd.

Å modellere en database der feltnavn heter "felt" og "verdi" bryter med prinsippet om relasjoner og referanseintegritet, man er i gang med å bygge en database i databasen, og mister da fordelene som en relasjonsdatabase gir. Dette er egentlig et antipattern (EAV/entity-attribute-value, generic data model) og de stedene jeg har brukt en slik løsning har det vært ekstremt tungvint å jobbe med. Rapportering, søk, filtrering, validering, feltlengde etc er halsbrekkende i en slik modell. Og når man kommer opp i noen hundre tusen rader begynner man å merke performance-problemene, det går tregere og tregere, brukerne blir oppgitte og vil ikke bruke systemet.

Her er litt om EAV fra Wikipedia:

In the database world, developers are sometimes tempted to bypass the RDBMS, for example by storing everything in one big table with two columns labelled key and value. While this entity-attribute-value model allows the developer to break out from the rigid structure imposed by a relational database, it loses out on all the benefits, since all of the work that could be done efficiently by the RDBMS is forced onto the application instead. Queries become much more convoluted, the indexes and query optimizer can no longer work effectively, and data validity constraints are not enforced. Such designs rarely make their way into real world production systems, however, because performance tends to be little better than abysmal, due to all the extra joins required. Inner-platform effect - Wikipedia, the free encyclopedia

Hva med å lagre en XML-kolonne i databasen, som inneholder produktdetaljene?

A similar temptation exists for XML, where developers sometimes favor generic element names and use attributes to store meaningful information. For example, every element might be named item and have attributes type and value. This practice requires joins across multiple attributes in order to extract meaning. As a result, XPath expressions are more convoluted, evaluation is less efficient, and structural validation provides little benefit.

Èn stor tabell
Dette er "lillebroren" til EAV, og du møter samme problemene her.

Alias for Kolonne1, Kolonne2 etc?
Mange ender med denne løsningen. Den har jo sine begrensninger, feks hvor mange felt skal man legge inn, og hva skal feltlengden være?

Skjemaet endres -> faktisk endre skjemaet?
Et alternativ er å definere så mye du har tilgang til av felt initielt. Dvs gå gjennom alle produktark, og bygge databasen felt for felt. Når du så importerer data fra nye produktark, må nye felt legges inn on-the-fly vha SQL (ALTER TABLE). Dette har jeg ikke erfaring med, og det kunne vært interessant å høre hvordan en slik løsning fungerer i praksis.

Noe annet i bunnen enn en relasjonsdatabase?
Dokument-orienterte databaser blir ofte nevnt som alternativ til EAV: Document-oriented database. Noen som har erfaring med en slik løsning? Noe sier meg at dette er litt overkill i de fleste tilfeller.



Det er enormt mye info om dette emnet på nettet, her er et lite utvalg:

Inner platform effect, thedaylywtf:
The Inner-Platform Effect - The Daily WTF

Dynamisk DB-skjema, StackOverflow:
Dynamic Database Schema - Stack Overflow

AskTom @ Oracle:
Ask Tom "Query on design"

Problemene du møter på med EAV:
Daves guide to the EAV

One True Lookup Table:
DBAzine.com: One True Lookup Table

 

[Pong]

Et godt eksempel på en document-oriented database er Lotus Notes. Har jeg jobbet med siden 97 og det blir fort mye programmering (og treg). Da ville jeg heller prøvd å lagre xml i en rdbms - har hørt at det i praksis faktisk fungerer godt (siste ms sql versjon) - sikkert pga en masse indeksering.

Å lage en database i databasen blir fort ubrukelig ja - da blir det fort såpass med constraints at de ligner et rdbms inni rdbms'en.

Jeg ville uansett ikke bygd for mye constraints i selve datamodellen - ingenting er mer irriterende å ikke kunne legge inn data fordi modellen ikke tillater det (bedre at et sett med data validerer før man tillater *bruk* av dataene - fremfor å nekte at de blir lagt inn - jeg har alltid synest at selv-referencing er morsomt: når en fk henviser til samme tabell får man i teori ikke lagt inn en post som henviser til seg selv).

Har du undersøkt transform (/pivot /crosstab) muligheten? Ulempen er nok at den fort koster.

 

[RipZ]

Hei igjen,

Selv om det er mange foredeler med en relasjonsdatabase, har den også en del begresninger. Dette er et eksempel. EAV er vel mer et pattern enn et anti-pattern i tilfeller hvor man ønsker å lage en mengde klasser (kategorier) hvor hver klasse har et begrenset antall objekter (artikler). Det er jo et pattern som har blitt brukt i en mengde store applikasjoner tidliger.

Hvordan kan man si at relasjon og referanseintegriteten ikke holder for en EAV hvor modellen er beskrevet slik at man har muligheten til å legge inn en constraint som opprettholder dataintegritet?

Hva med å lagre en XML-kolonne i databasen, som inneholder produktdetaljene?

Hvis du mener serialisert XML har man ingen (effektiv) mulighet til å bruke databasen effektivt på dataen.

Skjemaet endres -> faktisk endre skjemaet?
Et alternativ er å definere så mye du har tilgang til av felt initielt. Dvs gå gjennom alle produktark, og bygge databasen felt for felt. Når du så importerer data fra nye produktark, må nye felt legges inn on-the-fly vha SQL (ALTER TABLE). Dette har jeg ikke erfaring med, og det kunne vært interessant å høre hvordan en slik løsning fungerer i praksis.

Kommer an på hvilket DBMS du bruker. Regner med de fleste DBMS lagrer data for en rad samlet. Det betyr at hver gang man kjører ALTER TABLE vil man måtte kopiere all data for å gjøre plass til nye felter. Til gjengjeld vil lesehastigheten være bedre enn et EAV-approach siden metadataen ikke nødvendigvis ligger samlet og derfor krever flere diskoperasjoner.

Så lenge man bruker en relasjonsdatabase ville jeg gått for EAV hvor kategoriene er beskrevet og brukt en wrapper-klasse i programmeringsspråket du bruker for å hente objektene.

Om tilfellet er at kategoriene i stor grad bruker de samme feltene, ville jeg sett på muligheten å laget grupper med felt som implementeres som egne tabeller, og brukt en tabell som beskriver hvilke grupper man har. Om man har et begrenset antall grupper, vil man kunne skrive SQL joins som gjør at all data for et produkt blir hentet radvis.

I situasjoner hvor man har et begrenset antall kategorier (typer), er det enkleste å gå for en OO-modell.

 

[comichael]

@Pong: Lotus Notes er nok uaktuelt (det koster vel flesk), men har sett litt på MongoDB o.l, og har litt sansen for ideen om at det er dataene som legges inn som definerer skjemaet. XML i Sql Server er definitivt noe jeg skal sjekke ut nærmere. Pivot/crosstab har jeg ikke hørt om før nå, interessant.

@RipZ: Mitt inntrykk er at mange er ekstremt negative til EAV m/fam. Jeg leter etter alternativer til en slik løsning, men begynner å innse at det i mange tilfeller ikke finnes gode alternativ. Ang. ALTER TABLE så er vel eneste måten å finne ut om det funker på å faktisk teste det ut, så der har jeg kanskje et hobbyprosjekt for våren