[jayzee]

Hej,

sitter här med ett ganska stort projekt och skulle behöva lite hjälp med SQL-databas schema. Jag är ute efter en "best practices" och flexibel lösning.

Vi har en flerspråkig sajt där en användare skall kunna regga sig och ange en hel drös information om sig själv såsom hobbys, utseende osv... Denna information kan dock vara flervalsalternativ eller bara en sak (se bifogad bild). Användartabellen är klar, nu återstår "attribut" tabellen med all detta data - för tillfället innehåller attribut-tabellen endast en foreign key (userId) som pekar till användaren i users tabellen. Vad är bästa sättet att lagra all denna info i attribut tabellen men ändå göra det dynamiskt nog att man senare kan lägga till fler alternativ? Att lägga ett fält för varje alternativ känns som overkill då det finns EN MASSA val... Tack i förhand.

[Clarence]

Det vanligaste sättet, i brist på andra bra alternativ, att lösa ditt problem är en EAV-tabell (Entity-attribute-value). I ditt fal skulle din entity vara user, attribute definierat i en separat tabell med namn/id/datatyp/svarsalternativ osv och value självklart det valda värdet. Sedan lagrar man alltså en rad i denna tabell för varje attribut en användare lägger till.

Lösningen är väldigt flexibel och det är därför den används. Problemet är att många frågor blir rejält mycket slöare än de oflexibla alternativen. För det mesta går detta dock rätt så enkelt att optimera med både denormalisering och cachning (och innan dess givetvis SQL-optimering).

[Conny Westh]

Jag håller med Clarence i det mesta, jag anser dock inte att prestanda skulle vara ett problem (i de flesta fall). Sätter man rätt index så går detta blixtsnabbt.

Tabell: TBL_User
- PK_User
- Namn

IndexU1: PK_User
IndexU2: Namn


Tabell: TBL_Attribute
- PK_Attribute
- AttributeText
- AttributeType (Intressen, Livsstil m.m. kan även ligga i en egen tabell för att uppnå ännu högre grad av flexibilitet)

IndexA1: PK_Attribute
IndexA2: AttributeText



Tabell: TBL_UserAttribute
- FK_User -> TBL_User.PK_User
- FK_Attribute -> TBL_Attribute.FK_Attribute

IndexUA1: FK_User, FK_Attribute
IndexUA2: FK_Attribute, FK_User


PK => Primary Key
FK => Forreign Key

PK brukar ju alltid bli indexerat med automatik i relationsdatabaser, men det är vettigt att skapa kombinationsindex manuellt så man få upp prestanda ordentligt i databasen.

Det är väl ingen som använder ISAM längre då även MySQL har InnoDB idag med riktig relationshantering (även om InnoDB har vissa brister ännu, så är den betydligt flexiblare än ISAM).

Det här funkar alldeles utmärkt för binära egenskaper dvs Ja/Nej, Finns/Finns inte. Men man kan ju använda en Unsigned int av olika sort finns ju 16, 32, 64 bitars varianter och slå på/av en bit i taget för att spara minne, men det blir betydligt krångligare logik, men vid extremt stora databaser så kan det vara en väg att gå, även om jag i det längsta skulle undvika det om jag har krav på flexibilitet i attributfloran, det passar bättre nr man har fasta/förbestämda attribut.

[Clarence]

Håller också med Conny i det mesta, och en eloge för att alltid ha energi att skriva ordentliga strukurer i svaren

Stora skillnaden är att jag skulle lägga up det som:
EAV table: entity_id (int), attribute_id (int), value (varchar)
(Entity id är alltså användar-id i fallet med user_eav-tabellen)

Attribute table: attribute_id (int), attribute_name (varchar), attribute_type etc.

Gällande var man lägger index är jag av uppfattningen att man måste veta mer om datan och användningen för att föreslå en bra lösning. Använder man t ex InnoDB ska man vara riktigt noga med PK för att datan kommer klustras efter det och alla sekundärnycklar kommer innehålla hela primärnyckeln. Har man en liten datamängd och mycket läsningar är täckande index väldigt effektiva. Har man mycket writes och inte är sårbar för små brister i dataintegritet så ger FKs dålig prestanda.

Kan man använda kolumner och fåräknat antal tabeller istället för EAV får man MYCKET bättre prestanda. Någon som hört någon klaga på Magento? Deras modeller är extremt normaliserade enligt EAV ... därför krävs 10 gånger kraftigare servrar men systemet har en enorm flexibilitet.

[jayzee]

Tack för svaren, skall titta närmare på detta. Bara för att klargöra, databasservern är senaste 5.5 Percona Server med deras innodb implementation.

[Clarence]

Då är det väldigt fördelaktig att du använder PKs på ett sätt att du får sekventiella reads eftersom datan klustras på PK. Ska alla attribut vara unika över userid och attribut, eller kan kombineras i ett fält, så lägg en komposit PK på userid, attribute i EAV-tabellen. Prestandaskillnaden blir väldigt stor när du ska hämta många attribut för en användare.

Ska mycket attribut läggas till är det också väldigt effektivt att hålla sig borta från foreign keys. Så länge du kollar av att attribut finns, tar bort attribut-värden när attribut eller användare tas bort så är risken för kvarbliven data liten. Vill du ha det väldigt rent kan du i ett cronjobb kolla efter och radera den.

Även att göra dig av med flush av transaktions-logg vid varje skrivning ger en rejäl förbättring av skrivprestandan (flush_log_at_trx_commit).

Vidare är även buffer-poolens storleks relation till datamängden väldigt viktig - men det vet du säkert redan. Däremot PK-användningen är största optimerings-punkten som ofta glöms eller används fel när man vant sig vid myisam.

[Conny Westh]

Man ska akta sig för att optimera innan det finns ett behov av det, annars blir det lätt en suboptimering. Jag skulle aldrig undvika FK av prestanda i första skedet, först om det blir väldiga prestandaproblem skulle jag kika på det.