[MySQL] Database ontwerp product/ingredienten

[DimitryK]

Dag allemaal,

Voor een project ben ik bezig met het ontwerpen van een database, maar ik zit een beetje te twijfelen over de manier waarop ik dit wil aanpakken.

Binnen het project komen straks duizenden producten te staan, waaronder ook voedsel. Voor het voedsel is het belangrijk dat de ingredienten terug te zoeken zijn. Deze worden allemaal opgeslagen in de tabel "ingredients" en heeft twee attributen, namelijk "id" en "naam".

Nu wil ik de ingredienten gaan koppelen aan de producten en hier komt de twijfel. Ik zou de ingredienten kunnen opslaan als een array, waarbij je het volgende krijgt:

product_id	ingredients
1	3,15,36,37,41

Maar ik zou natuurlijk ook voor alle ingredienten een los record maken, waardoor voor bovenstaand voorbeeld 5 records zouden ontstaan.

id	ingredient
1	3
1	15
1	36
1	37
1	41

Snelheid is belangrijk bij de applicatie en mijn vraag is dan ook hoe jullie dit zouden oplossen en wat de meest efficiente aanpak is.

De applicatie wordt gebouwd in Laravel 4 en maakt dus gebruik van de Eloquent ORM. Misschien wel handig om even te vermelden.

Bij voorbaat dank.

[Barryvdh]

De 2de, dus 3 tabellen 'products', 'ingredients' en 'ingredient_product', waarbij de laatste dus een pivot table is, met alleen product_id en ingredient_id.
Dan in je model gewoon een belongsToMany relatie tussen beiden leggen.
Dan kan je het meest flexibel zoeken naar ingrediënten, en ook welke producten bij bepaalde ingrediënten passen.

[ Voor 19% gewijzigd door Barryvdh op 26-05-2014 11:22 ]

[Tk55]

De tweede manier is veel efficiënter. Een array maken in een cel (?) is totaal niet handig, omdat het opzoeken van een ingrediënt veel langzamer is.

Je ziet het heel makkelijk aan de tabellen die je gemaakt hebt. Stel je zoekt een product met ingrediënt 37. Dan vraag je aan de database: geef me een id waarin ingrediënt 37 voorkomt. Bij de eerste tabel zou je eerst voor elk product de waarde moeten splitsen, bij de tweede is het een makkie.

[DimitryK]

Zat er zelf ook al te denken aan de tweede manier. Ik was echter wel bang, dat wanneer ik dit voor duizenden producten zou doen, ik in de relatietabel tienduizenden records zou krijgen waardoor de queries erg langzaam worden. Of is dit verlies in snelheid echt minimaal? In ieder geval veel kleiner dan arrays splitten.

[ Voor 8% gewijzigd door DimitryK op 26-05-2014 11:28 ]

[Barryvdh]

DimitryK schreef op maandag 26 mei 2014 @ 11:28:
Zat er zelf ook al te denken aan de tweede manier. Ik was echter wel bang, dat wanneer ik dit voor duizenden producten zou doen, ik in de relatietabel tienduizenden records zou krijgen waardoor de queries erg langzaam worden. Of is dit verlies in snelheid echt minimaal? In ieder geval veel kleiner dan arrays splitten.

Test het even uit zou ik zeggen Misschien dat mijn laravel-debugbar dan handig is,
Ik denk dat het met paar duizend tabellen wel meevalt. Ik zou iig wel eager loading gebruiken. (Dus gewoon 'Product::with('ingredients')->paginate(30)')

[DimitryK]

Ooh nice, bedankt! Eager loading was sowieso al de bedoeling

[HMS]

Als je indexes goed staan (koppeltabel met FK's is volgens mij standaard geindexed, geen idee of dat in MySQL ook zo is) gaat een database niet moeilijk doen om 'maar' enkele tien duizenden records.

[DimitryK]

HMS schreef op maandag 26 mei 2014 @ 11:52:
Als je indexes goed staan (koppeltabel met FK's is volgens mij standaard geindexed, geen idee of dat in MySQL ook zo is) gaat een database niet moeilijk doen om 'maar' enkele tien duizenden records.

Geen idee, maar kan het wel opzoeken. Met Laravel is relaties creeren al erg makkelijk, dus misschien ook niet geheel relevant.

En dit is mijn eerste grote applicatie waarbij ik een, in mijn ogen, 'zeer grote' database ga gebruiken, dus dan lijkt tienduizenden records veel

[Barryvdh]

DimitryK schreef op maandag 26 mei 2014 @ 11:54:
[...]


Geen idee, maar kan het wel opzoeken. Met Laravel is relaties creeren al erg makkelijk, dus misschien ook niet geheel relevant.

En dit is mijn eerste grote applicatie waarbij ik een, in mijn ogen, 'zeer grote' database ga gebruiken, dus dan lijkt tienduizenden records veel

Jawel, dat is wel relevant. Met Laravel maak je inderdaad wel relaties tussen modellen, maar daar weet MySQL niks vanaf. In je database moet je ook je foreign key relaties/indexes opgeven. Dat scheelt veel in performance.
Dat kan je gewoon in je migrations invoeren, dat zie je ook wel als je een explain doet op je queries straks.

PHP:

$table->integer('ingredient_id')->unsigned()->index();
$table->foreign('ingredient_id')->references('id')->on('ingredients')->onDelete('cascade');

$table->integer('product_id')->unsigned()->index();
$table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');

En als je dat niet snapt, moet je het even googlen, scheelt best veel

[DimitryK]

Barryvdh schreef op maandag 26 mei 2014 @ 12:02:
[...]

Jawel, dat is wel relevant. Met Laravel maak je inderdaad wel relaties tussen modellen, maar daar weet MySQL niks vanaf. In je database moet je ook je foreign key relaties/indexes opgeven. Dat scheelt veel in performance.
Dat kan je gewoon in je migrations invoeren, dat zie je ook wel als je een explain doet op je queries straks.

PHP:

$table->integer('ingredient_id')->unsigned()->index();
$table->foreign('ingredient_id')->references('id')->on('ingredients')->onDelete('cascade');

$table->integer('product_id')->unsigned()->index();
$table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');

En als je dat niet snapt, moet je het even googlen, scheelt best veel

Oooh netjes. Zal het zeker gaan gebruiken. Als ik het goed begrijp worden alle gerelateerde entries verwijderd met de 'cascade' functie?

[Barryvdh]

DimitryK schreef op maandag 26 mei 2014 @ 12:08:
[...]


Oooh netjes. Zal het zeker gaan gebruiken. Als ik het goed begrijp worden alle gerelateerde entries verwijderd met de 'cascade' functie?

Als er een product/ingredient verwijderd wordt, wordt ook de rij in de koppeltabel verwijderd ja, maar dat is ook de bedoeling lijkt me. Je kan hem voor andere relaties ook op 'set null' zetten (als de kolom nullable is), of 'restrict' om verwijderen te voorkomen.

[DimitryK]

Barryvdh schreef op maandag 26 mei 2014 @ 12:19:
[...]


Als er een product/ingredient verwijderd wordt, wordt ook de rij in de koppeltabel verwijderd ja, maar dat is ook de bedoeling lijkt me. Je kan hem voor andere relaties ook op 'set null' zetten (als de kolom nullable is), of 'restrict' om verwijderen te voorkomen.

'Restrict' gebruik je dan dus om te voorkomen dat een product verwijderd wordt wanneer je een ingredient verwijderd.

[Barryvdh]

Nee, met restrict voorkom je dat een ingrediënt verwijderd wordt, zolang je nog een product hebt met dat ingrediënt volgens mij.
Met cascade verwijder je dus alle koppelingen van het betreffende ingredient, niet de producten zelf. Anders krijg je dus een tabel met allemaal koppelingen die niet meer bestaan.

[Gomez12]

Cascaded deletes in je db moet je maar net een fan van zijn, ik ben dat niet...

Het risico van cascaded deletes bij grotere databases (qua tabellen / kolommen) is dat je het overzicht verliest en dat je dan 1 foutje maakt en dat dan je hele database mee cascade, zonder cascaded deletes heb je een risico op inconsistente data maar dat heb ik liever dan dat ik de hele database meetrek en alles verwijder. Maarja ik verwijder dan ook in principe niets uit mijn database en markeer het enkel maar als deleted...

[Barryvdh]

ALS je het niet echt verwijderd, heb je ook geen last van de cascades he
Maar je moet het idd niet zomaar doen, maar voor een koppeltabel is cascade toch wel logisch?

[DimitryK]

Gomez12 schreef op maandag 26 mei 2014 @ 19:43:
Cascaded deletes in je db moet je maar net een fan van zijn, ik ben dat niet...

Het risico van cascaded deletes bij grotere databases (qua tabellen / kolommen) is dat je het overzicht verliest en dat je dan 1 foutje maakt en dat dan je hele database mee cascade, zonder cascaded deletes heb je een risico op inconsistente data maar dat heb ik liever dan dat ik de hele database meetrek en alles verwijder. Maarja ik verwijder dan ook in principe niets uit mijn database en markeer het enkel maar als deleted...

Alles wat daarvoor gevoelig is heeft een softdelete meegekregen. Het blijft dus in de database staan.

[Gomez12]

Barryvdh schreef op maandag 26 mei 2014 @ 20:15:
ALS je het niet echt verwijderd, heb je ook geen last van de cascades he

Soft-deletes zijn in mijn ervaring juist de killing combo met cascaded deletes...

Met soft-deletes wil je toch een jaarlijks oid opschoon-gedeelte hebben wat alle soft-deletes ouder dan 2 jaar bijv wegmietert. En dan kom je in een situatie dat je die maintenance scripts nooit gebruikt en ze extreem foutgevoelig zijn geworden.

Maar je moet het idd niet zomaar doen, maar voor een koppeltabel is cascade toch wel logisch?

Zoals ik al zei, ik ben er uberhaupt geen fan van.Alhoewel ik me de case bij koppeltabellen wel kan voorstellen als je een basis-gegevens verwijdert, maar ik heb bijv vaak te maken met dat koppelingen niet zozeer verwijderd moeten worden, maar dat basis-gegeven 1 wegmoet en alles wat daaraan gekoppeld was moet naar basisgegeven 2 gaan verwijzen, zonder cascades maakt de volgorde niet uit (zolang ik maar aan het einde een check heb of er geen orphans zijn) terwijl ik met cascades opeens een verplichte volgorde moet gaan volgen.

Maar ook bij een koppeltabel vind ik het opzich gevaarlijk, de gedachtengang is heel logisch, als ik een basisgegeven verwijder moeten ook alle koppelingen ernaartoe weg, maar de gevolggedachte die ik "vaak" zie is dan weer als een basisgegeven geen inkomende koppelingen meer heeft kan het basisgegeven ook weer weg (het principe vereist dan wel weer iets meer triggers als enkel cascaded delete maargoed, ik heb het al enkele keren geimplementeerd zien worden)

En dan krijg je dus zoiets :
a -> 1-2 -> b
b -> 2-3 -> c
c-> 3-4 -> d
b-> 2-5 -> e
a-> 1-3 -> c
en dan gooi je a weg en dan volgt de rest vanzelf want er blijven geen inkomende verbindingen meer over.
En uiteraard gaat dit 100% goed en is het geen enkel probleem want je hebt dit via de applicatie 100% dichtgetimmerd, maar nu moet een stagair even via sql een noodgreep op de database uitvoeren want a is corrupt en dan ga je opeens van een klein probleem naar een groot probleem.
Niet de 1e keer dat ik het zie gebeuren...

Let wel, in een ideale wereld met ideale programmeurs is dit geen probleem, maarja daar leef ik in ieder geval niet in dus in mijn wereld worden er fouten gemaakt en dan heb ik het liefste dat die fouten zo min mogelijk impact hebben in plaats van dat ze hele databases meetrekken.

[Barryvdh]

Ja maar is dat niet meer het probleem van die 2 anderen dingen die je noemt:
1. 'stagair even via sql een noodgreep op de database uitvoeren'
2. 'het principe vereist dan wel weer iets meer triggers als enkel cascaded delete'

1. mag natuurlijk nooit voorkomen op de productie database en 2 doe je dan toch nog echt bewust.

Maar je moet er wel eventjes over nadenken als je de database ontwerpt inderdaad.

[Crazy D]

Ik zou niet weten waarom je de database niet zou laten gebruiken voor iets waar die goed in is. Als je een foreign key aanlegt naar iets, en je verwijdert dat iets, heb je sowieso een ongeldige constraint. Je zult dus sowieso iets moeten doen (of leg je ook geen FK's aan?).

Dan is het alleen de vraag wat er moet gebeuren: op NULL stellen, of het hele record verwijderen. Dan hangt het er vanaf wat het voor gegeven is.

Als je een product verwijderd, is het logisch om de koppeltabel met ingrediënten leeg te gooien. Je moet er vanuit gaan dat iemand die het product weggooid, of een enorme prutser is waar je met geen mogelijkheid de boel voor kunt dichttimmeren (...), of het is functioneel een gebruiker die weet wat hij doet... Dan vind ik het onzin om eerst alle ingrediënten stuk voor stuk los te laten koppelen, om daarna pas het product zelf te kunnen verwijderen. Als het product ergens anders wordt gebruikt (bv in een order), dan kan het product niet verwijderd worden.

Als je een ingredient wilt weggooien, zou je de koppeltabel wat mij betreft niet automatisch leeg moeten gooien. Je gooit een product ook niet weg omdat een orderregel waar het product in stond, wordt weggegooid.

In je applicatie zou je natuurlijk wel een check kunnen bouwen, en als je een product hebt weggegooid, en de ingrediënten die waren gekoppeld aan dat product, worden verder nergens gebruikt, een melding geven en de keuze bij de gebruiker neerleggen om eventueel direct die ingrediënten weg te gooien maar dat zou ik zeker niet op database niveau afhandelen.

(Ik ontwikkel vooral op Exact producten en Exact doet juist helemaal niets op database niveau, daar gaat juist alles volledig via business logica in de repository, en als ik iets zwaar irritant vind is het dat dus wel. Geen scripts om te kunnen controleren of iets verwijderd of vernummert kan worden, op database niveau kun je de boel mooi verkloten (al zegt Exact dan, had je niet aan de DB moeten komen...), maatwerk tabellen met verwijzingen naar standaard entiteiten worden niet zonder meer herkent, etc.)

[Barryvdh]

Crazy D schreef op dinsdag 27 mei 2014 @ 09:12:
Als je een ingredient wilt weggooien, zou je de koppeltabel wat mij betreft niet automatisch leeg moeten gooien. Je gooit een product ook niet weg omdat een orderregel waar het product in stond, wordt weggegooid.

Dat is toch ook niet het geval als je cascade? Als je een ingrediënt zou verwijderen, verwijder je niet alle gekoppelde producten, maar alleen dat ene ingredïent uit alle producten waar het in voor kwam.
Of reageerde je op die extra triggers?
Maar het is misschien inderdaad niet handig dat je ingrediënten verwijderd die nog bij producten horen, omdat ze dan incompleet zijn.

In dat geval zou je dan dus een 'restrict' op de ingredient_id FK zetten zodat je het niet per ongeluk verkeerd gaat als je in je applicatie een check vergeet. En dan in je applicatie zelf controleren, de vraag stellen en dan eerst de relaties leeggooien? (Of wel de cascading delete, maar dan zorgen dat je het in de applicatie zelf goed afvangt)