Lagring av tilhørende data i samme tabell eller i en relasjonstabell?

[Yawa]

Ved lagring av drink oppskrifter i en database; Så er muligheten til å velge hvilke ingredienser "jeg" har tilgjenglig først, for så å få presentert hva man kan lage en ønsket funksjonalitet.

 

Men hvordan dette bør lagres i databasen er jeg litt usikker på.

 

Burde jeg lagre ingrediensene til hver enkelt drink i en sepparat relasjons-tabell og linke de til den aktuelle drinken via drink id'en?

| ingredient_id  |  drink_id |
17		9
32		9
24		9
10		  10
15		10

 

Eller burde jeg ha et eget tekstfelt direkte i drink tabellen hvor jeg kommasepparerer ingrediens id'er?

| ingredients |
 17,32,24
 10,15

 

Det som er svært viktig er at at resultatet som vises faktisk samsvarer med hva som ble krysset av.

Litt usikker på hvordan dette gjøres. noen tips?

Endret 17. juli 2012 av Yawa

[etse]

Jeg ville kjørt 3 tabeller:

Drinker:

Id - Navn

 

Råvarer:

Id - navn

 

Ingredienser

DrinkId - RåvareId - Mengdde

 

Dette gjør det veldig fleksibelt. Om man plutselig skal lage en drink som bruker en råvare som man ikke har brukt tidliggere før, trenger man da ikke endre på database-designet. Bare å legge til en ny rad i råvare-tabellen.

 

Jeg er alltid for å lage fler tabeller for å vise relasjoner, fremfor komme-separering. Da det gir større fleksibilitet. Og om jeg skal bruke komme-separering føler jeg at man må ha en god grunn. (jeg har aldri tenkt på det). Det å få masse rader i databasen er sjeldent noe problem så lenge man setter opp greie nøkkler og indekser.

 

(selv har jeg en database med 10 tabeller med > 40.000 rader i hver tabell, som kjører på en sikkert 8 år gammel server. Denne har ingen problemer med kjøretid på spørringene enda. Dåg har jeg ikke mer enn cirka 20 brukere av databasen).

Endret 17. juli 2012 av etse

[tomsi42]

Er enig med deg der etse; med de tre tabellene,så er det lett ha oversikten.

[Yawa]

Har allerede en råvare tabell. Så vi har tenkt likt der. Og jeg ser helt klart fordelen med en sepparat tabell for alle ingredienser med tanke på ytterligere info som mengde etc.

 

Men hvordan kunne en optimal spørring se ut i mitt tilfelle?

Tenker da på resultatvisning ved et søk hvor jeg hadde krysset av for hva slags råvarer jeg hadde tilgjenglig (check-bokser).

 

et par tabeller må joines sammen, det er ok. Men slik som jeg ser det; hvis jeg avslutter noe slik som:

//  ingr = ingredienser tabellen
WHERE ingr.id IN (/*  komma-sepparert string med id'er  */)

... så garanterer ikke dette at resultatet vil være 100% riktig da denne spørringen returnerer alle drinker som inneholder disse ingrediensene? Det er jo mulig at noen av de drinkene også inneholder ytterligere noen ingredienser.

 

I mitt hode burde det være noe slik:

1. Søk etter alle drinker som inneholder "disse" ingrediensene.

2. Gå gjennom resultatet og filtrer vekk eventuelle drinker som krever ytterligere ingredienser.

3. Returner kun drinker som ikke krever flere ingredienser enn de jeg har krysset av for...

 

Men skal kikke mer på dette i mårra, må bare få mata tabellene med noe data som jeg kan teste på først slik at jeg får en bedre forståelse av hvordan jeg kan bygge opp en effektiv spørring...

[etse]

Så det du ønsker er at man skal kunne velge hvilke råvarer man har, og så få opp alle oppskrifter man kan lage med disse råvarene?

 

SELECT navn
FROM drinker
WHERE NOT EXISTS
(
SELECT *
FROM ingredienser
WHERE drinkid = drinker.id
AND ravareid NOT IN ('1', '3', '5')
)

 

I eksempelet over finner jeg alle drinker man kan lage om man har råvarene med id 1, 3 og 5. Det vil si at drinker laget med f.eks. kun 1 og 3 blir funnet - men ikker drinker som trenger 1, 3 og 7

 

Edit:

 

Tenkte litt over dette, og vil tro denne spørringen vil være raskere:

SELECT navn
FROM drinker
WHERE id NOT IN
(
SELECT drinkid
FROM ingredienser
WHERE ravareid NOT IN ('1', '3', '4')
);

 

Fint om noen andre også kan se over om dette stemmer. Men den forrige er en nested-subquery, noe som betyr at subqueries må kjøres 1 gang for hver drinker.id fra den ytre querien. Mens denne queries kun trenger å kjøre subqueries 1 gang, for så å bare sjekke opp i mot resultatet hver gang.

Endret 18. juli 2012 av etse

[Crowly]

En grei måte å tenke er noe ala dette:

En drink kan inneholde flere råvarer, en råvare kan blir brukt i flere drinker (oppskrifter). Det blir ett mange til mange forhold.

 

Mange til mange forhold krever tre tabeller, som etse har vist over.

 

@etse: Hvorfor har du id som tekst? Brude vel være WHERE ravareid NOT IN (1, 3, 4)

Endret 18. juli 2012 av Crowly

[superlaban]

Jeg mener SQLene dine fungerer, men jeg vil ikke automatisk anta at NOT IN alltid er raskere enn NOT EXISTS. I hvert fall i Oracle versjon 11g som jeg har mest erfaring med er det ofte ett fett om man bruker EXISTS eller IN. Jeg blir ofte positivt overrasket av hvor gode SQL-optimaliseringer databasen gjør. Men det er jo også mye derfor vi bruker SQL og databaser i stedet for fil-hack.

[etse]

Jeg mener SQLene dine fungerer, men jeg vil ikke automatisk anta at NOT IN alltid er raskere enn NOT EXISTS. I hvert fall i Oracle versjon 11g som jeg har mest erfaring med er det ofte ett fett om man bruker EXISTS eller IN. Jeg blir ofte positivt overrasket av hvor gode SQL-optimaliseringer databasen gjør. Men det er jo også mye derfor vi bruker SQL og databaser i stedet for fil-hack.

Om man ser fort på kodene så er det ikke NOT IN / NOT EXISTS i seg selv som var hasighetsproblemet men det faktum at nested-querien i første eksempel brukte en variabel fra den ytre spørringen. Og dermed måtte kjøre en indre spørringen på nytt for hvert enkelt element i den ytrespørringen. Den nederste slipper unna dette.

 

Men googling rundt dette har vist meg at optimizeren er smart nok til å oppdage dette - og vet om dette med den første spørringen og dermed optimaliserer kjøringen slik at man ender opp med samme kjøretid med begge to. Så akkurat som deg, blir jeg overrasket over hvor smart den er

[affe90]

Støtter etse fult ut her, og vil gjerne bare skyte inn en teoretisk sak: Kommaseparerte verdier bryter med første normalform (1NF) til relasjonsdatabaser.

 

...så vet vi det.

[siDDis]

Normaliseringsregler er etter min meining ofte overdreve, med kommaseparerte verdiar så kan ein mangedoble ytelsen, men og gjere datauttrekk treigare. Det er mykje meir viktig å forstå kva og kvifor.

Kommaseparerte verdiar er ofte supert til databaser som BerkeleyDB eller Kyoto Cabinet.

[affe90]

Normaliseringsregler er etter min meining ofte overdreve, med kommaseparerte verdiar så kan ein mangedoble ytelsen, men og gjere datauttrekk treigare. Det er mykje meir viktig å forstå kva og kvifor.

Kommaseparerte verdiar er ofte supert til databaser som BerkeleyDB eller Kyoto Cabinet.

Informativt, og der lærte jeg noe nytt. Men da spør jeg deg om en ting: Fra ditt ståsted, synes du ikke funksjonaliteten blir latterlig redusert om man skal begynne med manipulering av data? Sett at man skal kjøre en UPDATE eller INSERT-setning, så blir jo kommaseparerte verdier et problem?

[siDDis]

Det er både og....

 

Trenger du betre ytelse så er det raskare å bytte heile heile den kommaseparerte strengen(så lenge den er ikkje er gigantisk svær) enn å oppdatere fleire kolonner.

Det som er verre er når du skal gjere uttrekk, for om du ikkje trenger heile den kommaseparerte strengen så må du allikavell hente heile ved uttrekk, også parse den etterpå. Det er det du som utviklar/arkitekt må tenkte over. Kva som er best er bare du som kjenner til. Men som regel er kommaseparert data bare å finne i key-value databaser og ikkje i relasjonsdatabaser.

 

Om databasen skulle knele ein dag, så er lurt å kjenne til alternativer for å auke ytelsen. Eg brukar alt frå filer, til relasajonsdatabaser. Dei har begge sin plass.

[tomsi42]

@siDDis, har du et praktisk eksempel som hvor du har brukt en kommaseparert streng?

[jag007]

@siDDis,

Trådstarter har behov for å søke på ingredienser, for å se hvilke drinker som kan lages. Da er det smart å ha en indeks på ingrediensene for en drink, dette gir god ytelse på spørringer, og det er vel rimelig å anta at det blir mer spørringer enn endringer i en database med oppskrifter på drinker?

 

Til trådstarter, så anbefaler jeg på det sterkeste å bruke kolonner og fremmednøkler/indekser slik de er ment å bruke, da får du hjelp av de innebygde mekanismene i basen til å sikre at relasjoner mellom de ulike tabellene er gyldige. (med fremmenøkkel mellom ingrediens i drink og ingrediens i ingredienstabell vil det ikke være mulig å slette ingrediens som brukes i en drink,( men dette kan overstyres eksplisitt)).

Du kan også bruke egnede verktøy for å generere applikasjon fra datamodellen din.

[siDDis]

Et praktisk eksempel der man ofte kan sjå kommaseparerte strenger er når man må lagre data lynkjapt. Den kjappaste måten å lagre data på er å skrive kommaseparerte strenger til fil (der man lager ein binær datablokkbuffer som skrives) . Men kommaseparerte strenger kan også hjelpe til på skriveytelsen i ein database, og då vil du heller ikkje ha ein indeks på den tabellen. Uttrekk derimot vil går treigt og bli komplekst. Det kjem heilt ann på kva du har tenkt å gjere. Det er fordeler og ulemper, og det er lurt å være klar over dei. Struktur gjer det meir oversiktleg, men har og ein ytelseskostnad.

[Yawa]

Jeg har gått for å ha alle råvarer (ingredienser) avskilt i en egen tabell hvor jeg kan definere type samt legge til en beskrivlse og bilde etc.

Videre har jeg en tabell som lister alle drinker.

Til slutt har jeg en tabell som håndterer ingredienser og linker de mot riktig drink.

drink_id  |  ingredient_id

 

Jeg har også en tabell som inneholder glass typer. I drink tabellen har jeg glass id'en satt direkte inn...

Endret 23. august 2012 av Yawa

[G2Petter]

SELECT navn
FROM drinker
WHERE NOT EXISTS
(
SELECT *
FROM ingredienser
WHERE drinkid = drinker.id
AND ravareid NOT IN ('1', '3', '5')
)

 

En liten kommentar til denne: det vil være litt kjappere (men normalt ikke noe du merker med mindre du f.eks. kjører spørringer i en løkke) å kjøre SELECT 1 i stedet for SELECT *. Å hente ut en konstant er kjappere enn å lese fra disk/index.