Jag skall i en databas lagra uppgifter om produkter (tänk webshop, prisjämförelsesajt etc).
Man skall kunna söka på olika produktattribut och då söker man på en produktkategori i taget, tex "alla bilar med fyrhjuilsdrift och dieselmotor". Olika produkter har olika attribut och olika många attribut.

Jag ser tre olika sätt att lösa detta.

1. en tabell per produktkategori. Verkar omständigt då man måste lägga till en ny tabell för varje ny produktkategori samt lägga till fält om en kategori får ett nytt sökbart attribut.


2. En tabell med produktkategorier, en med produktattribut, en med produktattributvärden.
Då kan man enkelt lägga till nya produkter och attribut, men blir inte sökningen väldigt komplicerad (och långsam). Det blir väl en mass "JOIN" ?


3. En produkttabell med attributvärden (alltså de olika produkterna "Volvo V70", "Sony Cybershot W120", "Jas 39 Gripen") som har så många kolumner att de räcker för den produkt som har flest attribut (plus några fler för framtiden) men där de olika kolumnerna har olika betydelse beroende på vilken produktkategori den tillhör. Man skulle kunna ha tex 30 textfält och 30 numeriska fält (för att slippa typecast).

Man får då ha en mappingtabell där man definierar alla attribut för varje produktkategori (en tabell med produktkategorier har man också) och även anger vilken kolumn i tabellen med attributvärden som ett attribut tillhör. Tabellen skulle då se ut såhär:

1, antal_hjul, 3
1, motortyp, 4
1, antal_säten, 5


Vi antar att 1 pekar på tabellen "produktkategorier" (i det här fallet på kategorin "Bilar")
Bilar har då ett attribut som heter antal_hjul och det sparas i kolumnn 3 i produkttabellen, motortyp i kolumn 4 etc.


Nu kan man enkelt lägga till nya produktkategorier (då de ligger i en egen tabell) och nya produktattribut (ligger i mappingtabellen ovan) och har man bara tillräckligt många kolumner i attributvärdestabellen så är man ganska framtidssäkrad. Sökning borde även vara snabbare då man ganska fort kan få fram vilka attribut som hör ihop och det blir en enkel fråga till databasen även om tabellen blir väldigt stor.



Jag tycker denna approach är den som verkar vettigast, vad tycker ni? Var jag tillräckligt oklar :) ?

Tycker alternativ 2 verkar bäst. Alternativ 3 verkar väldigt dimmigt och som upplagt för oförutsedda problem.

Håller med, det enda flexibla och hållbara systemet i längden är alternativ två. Trean är absolut sämst skulle jag vilja säga.

Alternativ 2 låter som sagt som det bästa alternativet, dock kommer det som du var inne på innebära prestandaproblem vid stora datamängder. Detta går dock att lösa med hjälp av mellanlagring i "stöd"-tabeller för snabbare åtkomst. Dessa "stöd"-tabeller kan man sedan ladda om vid uppdatering av datat i dina huvudtabeller. Iofs kan man då tycka att det blir lite som alternativ 1, men jag tror ändå man tjänar på att blanda dessa alternativ på detta sätt. Men endast då man får problem med prestandan.

Ja nummer två är väl den som är mest politiskt korrekt, men det är som sagt prestandan när databasen blir stor som jag oroar mig för. Det går väl knappast att komma ifrån filesort i MySQL om man vill söka efter alla bilar som har dieselmotor och fyrhjulsdrift och sortera dessa på antal hästkrafter.

Om man vill söka på 3 attribut så blir det väl först att hitta de rader med rätt produktkategori_id samt attribut_id vars värde uppfyller kraven. Man måste då se vilka produkt_id som förekommer på 3 rader. Någon slags Count som man Grupperar på produkt_id och tar ut de som blir 3 (i detta fall). Då har man alltså de produkter som uppfyller kraven.

Om man nu vill visa vissa attribut i en lista för alla produkter som uppfyllde sökkraven så måste man hämta dessa igen för samtliga produkter man erhöll. Allt detta kan man naturligtvis göra i en och samma fråga, men det blir väl en SubQuery hur man än gör.

Om vi antar att vi nu har alla attribut för den mängd produkter som uppfyllde sökkraven så är det alltså ett produktid + attribut + värde per rad och det finns då inget enkelt sätt att sortera dessa rader på ett visst attribut. En sortering i SQL sker ju på en kolumn och kolumnen med attributvärden innehåller ju värden för ALLA attribut. Då får man alltså göra en fråga där man enbart hämtar och sorterar ett attribut för de aktuella produkterna som man sedan JOINar med det övriga resultatet (fast det blir ju kanske i omvänd ordning eftersom man inte vet vilka produkter som uppfyller sökkraven innan man kan sortera på sorteringsattributet).



Det är klart, det blir lite meckande med nr 3 men å andra sidan så kan man direkt ta ut de attribut man vill åt från de produkter man vill åt i en enda simpel SQL-sats:

SELECT ..(de kolumner jag vill ha).. FROM ..(en enda tabell).. WHERE ..(val1 = x AND val2 = y).. ORDER BY (val1)



lubic: Hur skall man lägga upp stödtabellerna menar du?

Nummer 2 är väl det enda riktiga.

Du behöver inte så mycket join, men förmodligen ett par where. Tänk också på att du vill ha fritext sökning, dvs du ska helst ha FULLTEXT på alla kolumner som ska vara sökbara.

Jag tror inte det kommer gå så segt. Så är min site uppbyggd, har bara 350.000 möjliga kombinationer än. Det är värre om du har en produkt som har så många, och väldigt många produkter. Om du också uppdaterar dina tabeller ofta kan det vara bra att cron'a lite optimize på tabellerna för bra speed. och bra index i övrigt såklart.

Frågan med sortering på ett attribut måste väl JOINas med frågan på alla attribut?

Jag förstår inte riktigt problemet du har med sorteringen. Sorteringen görs ju efter att raderna sorterats ut (och då rimligtvis snabbt i minnet såvida man inte får stora resultat).

Du har nog rätt, det kanske inte blir så komplext som jag trodde.
Frågan är vad som räknas som stora resultat, jag har ingen egentligen uppfattning om vad som är mycket.

Antag att man tar ut 50 produkter och 5 av deras attribut = 250 rader som joinas med 50 rader sorteringsattribut och sedan sortering på dessa i två steg, först på det attribut som produkterna skall sorteras efter och sedan på attributen så att det blir ordnat för varje produkt. (Då har man innan även genomfört det första söksteget för att hitta releventa produkter)

Jämför detta med alternativ 3 då man tar ut 50 rader som kan sorteras med databasens index direkt.

Relativt sett så är det en ganska stor skillnad tycker jag, men frågan är om skillnaden mäts i tusendelar eller sekunder...


Om vi säger att man väljer alternativ 2, då antar jag att man väljer tex en varchar som datatyp och sedan gör en typecast om man vill söka numeriskt (alla bilar med fler än 200 hästkrafter). Hur påverkar det prestanda?

Det beror väl på i vilken riktning du typkastar. Om du omvandlar värdet i databasen så måste normalt sätt varenda värde i hela tabellen omvandlas redan innan testen i WHERE kan genomföras vilket förstås är väldigt tidskrävande. Det är oftast sådana missar som drar ner prestandan på databaser (eller saknade eller felaktiga index) och inte antalet tabeller eller några join:ar (det är i alla fall min uppfattning).

Om du omvandlar det i ett skriptspråk och inte i databasen så rör det säkerligen om ett fåtal värden som måste omvanlas med liten prestandaeffekt.

Generellt sätt ska värdena i databasen vara av rätt typ.

OK, så förslag nr 2 kommer ge problem om attributvärdena kan vara både strängar och numerer eftersom de lagras i samma kolumn. Det kommer knappast gå att flytta type-castningen till scriptspråket då det kommer ske typecast i näsan varenda fråga, tex "alla bilar med fler än 200 hästar".

Kanske man skulle ha tvåan som grund och sedan skapa en tabell enligt modell nr 3 som hjälptabell?

Två tabeller: numreriskaAttributVärden och textAttributvärden.

(Vilket även har fördelen att du på en gång vet att attribut som finns i numreriskaAttributVärden kan jämföras numreriskt.)

Ja det var ett bra förslag!

Finns en hel del som sagts om Entity-Attribute-Value-modellen som du antagligen vill läsa innan du gräver dig ett alldeles för djupt hål:
http://en.wikipedia.org/wiki/Entity-Attrib...ute-Value_model
http://www.dbforums.com/database-concepts-...eople-hate.html
http://weblogs.sqlteam.com/davidm/articles/12117.aspx
http://tonyandrews.blogspot.com/2004/10/ot...n-mistakes.html

EAV-modellen (dvs, din nummer två) är väldigt flexibel, men den tenderar också att bli väldigt komplex.

Tack DeSoto, intressant läsning, men jag är inte helt på det klara med vad de olika designtyperna innebär.

EAV är det alltså en tabell enligt:

SAAB 9-5, motor, diesel
SAAB 9-5, antaldörrar, 5
VOLVO v70, motor, diesel


och 3NF som de nämner (3:e normalformen) skulle då vara att man har en tabell med produkter, en med attribut (produkt_id som foreign key) samt en tabell med attributvärden (attribut_id som foreign_key).

Fast det stämmer inte enligt det du skrev...?


OTLT (one true lookup table) verkar inte vara som mitt 3:e alternativ om jag fattat det rätt och läst vissa kommentarer till en av artiklarna du länkade till. Det verkar vara ett attribut per rad om jag fattat det rätt? Hur skulle OTLT se ut i mitt fall, med produkter och attribut?

Har du själv några åsikter i frågan?

Det om OTLT hängde med mest för att det är intressant läsning. :)

Kärnan i ditt problem är att du vill tillåta dina användare ändra databasens uppbyggnad, vilket i vissa fall kan vara ungefär som att ge ett raketgevär till en fem-åring.

Men du har två (eller ja, tre, där den tredje är "Exploded schema") val. Du kan utveckla ditt system så att när kunden lägger till ett fält så körs verkligen en ALTER TABLE-fråga. Eller så väljer du EAV-modellen, och skapar i stort sett en databas inuti en databas. Dvs, allt jobb som lagts ner av olika databastillverkare för att ta fram verktyg för dataintegritet, m.m. slängs bort, och du måste själv utveckla de verktygen till din databas inom en annan databas.

Jag föredrar att inte använda EAV-modellen, helt enkelt för att jag accepterat att den nivån av databaskunnande är utanför min expertis. För att implementera en bra och hållbar EAV-modell vill jag påstå att man måste vara en ganska rutinerad databasadministratör.