[MySQL] Join op meerdere velden, schaalbare catalogus

[Paul C]

Ik wil een catalogus gaan programmeren, maar was niet van plan een platte structuur te maken waarbij ieder product een naam en beschrijving heeft, maar meer zoiets als de pricewatch van tweakers. Categorieën met ieder hun eigen specificatie per product afgestemd op de categorie. Ieder product heeft dus een aantal specificatie velden die afhankelijk zijn van de categorie waarin het geplaatst is.

Ik ben de database aan het ontwerpen en had dit ongeveer zo gedacht:

Producten:
Product_ID
Categorie_ID
Product_Naam

Categorieën:
Categorie_ID
Categorie_Naam

Categorie_Specs:
Spec_ID
Categorie_ID
Spec_Naam

Spec_List:
Spec_ID
Product_ID
Spec_Waarde

Buiten dat ik natuurlijk nog wat meer informatie op wil slaan en dat ik numerieke en text data niet in eenzelfde datatype kwijt kan i.v.m. met sortering, zou ik hier alle kerngegevens in genormaliseerde vorm in kwijt moeten kunnen.

Nu wil ik per categorie natuurlijk een lijst kunnen maken van producten, bijbehorende specificaties en daarop ook kunnen sorteren en selecteren. Daarvoor moet ik dan wel alle specificaties in één query aan de Producten tabel kunnen joinen. Ik zie echter niet hoe ik dit vorm zou moeten geven. Een tabel meerdere keren op twee velden aan de Producten joinen? (MySQL 5.0)

Een andere optie zou zijn om niet te normaliseren, de producten tabel een aantal extra velden te geven en in een derde tabel de betekenis voor die velden per categorie op te slaan. Dit idee roept bij mij in eerste instantie walging op vanwege niet genormaliseerde data, maar begin ik nu steeds meer als een reële optie te zien. Niet zozeer vanuit de onmogelijkheid van bovenstaande optie, want ik geloof wel dat dat te implementeren is, desnoods in code, maar wel omdat dit misschien veel efficiënter is met CPU cycles en mogelijk ook zelfs schijfruimte (afhankelijk van implementatie en gebruik). Ik wil dit systeem juist omdat het flexibel en schaalbaar is in product spectra, maar het moet natuurlijk ook schaalbaar zijn qua prestaties.

Mijn vragen:
1). Hoe join bij de voorgestelde database structuur om de gevraagde gegevens te selecteren?
2). Kan ik beter, alles in beschouwing nemend, voor een andere database structuur kiezen?

[Voutloos]

Het probleem is dat jij per se 1 row per product wil zodat de mysql resultset exact hetzelfde eruit als de uiteindelijke tabel. Maar als je nou gewoon joint zoals je in gedachten had en die tabel dmv een assoc array bouwt ben je zo klaar.

Anders ga ja gauw richting gebruik van zaken als GROUP_CONCAT(), maar daar wordt het ook niet altijd mooier op.

[Confusion]

pcmadman schreef op donderdag 19 juni 2008 @ 02:36:
Daarvoor moet ik dan wel alle specificaties in één query aan de Producten tabel kunnen joinen.

Wat is er mis met twee queries doen: eentje om de producten op te halen en eentje om de specs op te halen?

[Voutloos]

Omdat je dan die 2e query per product moet doen en deze gewoon alsnog per product meerdere rijen terug gaat geven, oftewel: het kan dan net zo goed in 1 query.

[Janoz]

Het nadeel hiervan is dat je al snel tegen dit soort lastigheden aan gaat lopen.

Hoe variabel moeten de specificaties zijn? Moet dat ook beheerbaar zijn binnen de applicatie of is dat al bekend tijdens het schrijven van de applicatie?

In het laatste geval zou ik zelf een object georienteerd domain schrijven met overerving en dit vervolgens door de OR mapper op laten lossen.

[Confusion]

Voutloos schreef op donderdag 19 juni 2008 @ 09:00:
Omdat je dan die 2e query per product moet doen en deze gewoon alsnog per product meerdere rijen terug gaat geven, oftewel: het kan dan net zo goed in 1 query.

Dat hangt nogal van het gebruik af. In de praktijk zal er meestal eerst een categorie gekozen worden en is het voldoende de producten in die categorie op te halen en de specs bij de categorie op te halen.
Daarna worden de specs per product pas interessant en kan je de lijst uitdunnen met een nieuwe query met bijbehorende where clauses op spec. Natuurlijk, je kan dat in de browser doen, maar als je het fatsoenlijk abstraheert, kan je dat later ook makkelijk aanpassen als blijkt dat je db server het niet trekt. Hoe meer logica je aan de serverkant/db-kant houdt, hoe overzichtelijker je javascript-meuk blijft. Daar moet je IMHO pas logica instoppen als dat performancewise nodig is, niet eerder. Het queryresultaat in pakweg de PHP cachen en de operaties daar uitvoeren... ik betwijfel dat dat beter performed dan de database.

[Voutloos]

Browser, Javascript, Cachen in PHP, WTF? Ik zeg niets van dat alles, ik zeg enkel dat het in 1 query kan, dus ik houd het probleem juist in de DB, met een enkele query. Dat in die query al dan niet een where op de categorie moet staan zal mij een worst wezen, dat kan desgewenst gewoon.

[Confusion]

Je moet dan in de resultset nog bij elkaar gaan zoeken welke specs bij een bepaald product horen en je houdt het probleem dus zeker niet in de DB. Daarom begon Janoz ook al over een OR mapper: bij gebruik van een OR mapper hou je het probleem zeker niet in de DB.

Als ik zo snel even door de pricewatch blader, dan heeft die volgens mij ook niet alle eigenschappen waaraan een product voldoet in de javascript zitten. Bij iedere 'Zoek' wordt er een callback naar de server gedaan. Dan kan hij de laatste resultaten uit zijn cache halen en daar de zoekcriteria op loslaten, of de zoekcriteria in een query stoppen en een nieuwe query uitvoeren. Het zou me niets verbazen als ze het laatste doen, in plaats van het eerste en in dat geval is die ene query dus niet de beste oplossing.

Dit probleem is niet triviaal en nogal afhankelijk van de gebruiksomstandigheden. Je smileygebruik vind ik jammer.

[ Voor 92% gewijzigd door Confusion op 19-06-2008 16:46 ]

[Paul C]

Voutloos schreef op donderdag 19 juni 2008 @ 08:17:
Het probleem is dat jij per se 1 row per product wil zodat de mysql resultset exact hetzelfde eruit als de uiteindelijke tabel. Maar als je nou gewoon joint zoals je in gedachten had en die tabel dmv een assoc array bouwt ben je zo klaar.

Anders ga ja gauw richting gebruik van zaken als GROUP_CONCAT(), maar daar wordt het ook niet altijd mooier op.

Joinen zoals ik in gedachten had is MySQL een tabel laten produceren die er precies zo uit ziet zoals ik 'm uiteindelijk op de pagina wil hebben. Daarbij m'n code alleen nog de opmaak toe laten voegen. Het joinen in de software doen kan wel, maar dat vind ik bepaald geen nette oplossing.

GROUP_CONCAT is dat in deze niet een lelijke manier om te joinen op een multi-column primary key?

Confusion schreef op donderdag 19 juni 2008 @ 08:52:
[...]

Wat is er mis met twee queries doen: eentje om de producten op te halen en eentje om de specs op te halen?

Ik doe natuurlijk ook wel twee queries. Eentje waar een lijstje met de specs per categrorie uit komen, anders weet ik natuurlijk ook niet wat ik als kolomhoofden moet gebruiken. Aan de hand hiervan kan ik dan ook dynamisch een querie bouwen die een tabel van de producten met specificaties bouwt. Sorry, hier had ik duidelijker in moeten zijn.

Voutloos schreef op donderdag 19 juni 2008 @ 09:00:
Omdat je dan die 2e query per product moet doen en deze gewoon alsnog per product meerdere rijen terug gaat geven, oftewel: het kan dan net zo goed in 1 query.

Dat zou wel de lelijkste methode zijn, dan nog liever per kolom een querie en die via een assoc array joinen in m'n code.

Janoz schreef op donderdag 19 juni 2008 @ 09:20:
Het nadeel hiervan is dat je al snel tegen dit soort lastigheden aan gaat lopen.

Hoe variabel moeten de specificaties zijn? Moet dat ook beheerbaar zijn binnen de applicatie of is dat al bekend tijdens het schrijven van de applicatie?

In het laatste geval zou ik zelf een object georienteerd domain schrijven met overerving en dit vervolgens door de OR mapper op laten lossen.

Het hele idee is nou juist dat iedere categorie een andere set variabelen kan hebben. Dit moet in de applicatie instelbaar zijn. Bijvoorbeeld processoren hebben nou eenmaal geen rotatiesnelheid zoals een hardeschijf dat heeft en ook geen ISBN nummer zoals boeken, of inhoud zoals shampoo, of...

Confusion schreef op donderdag 19 juni 2008 @ 12:09:
[...]

Dat hangt nogal van het gebruik af. In de praktijk zal er meestal eerst een categorie gekozen worden en is het voldoende de producten in die categorie op te halen en de specs bij de categorie op te halen.
Daarna worden de specs per product pas interessant en kan je de lijst uitdunnen met een nieuwe query met bijbehorende where clauses op spec. Natuurlijk, je kan dat in de browser doen, maar als je het fatsoenlijk abstraheert, kan je dat later ook makkelijk aanpassen als blijkt dat je db server het niet trekt. Hoe meer logica je aan de serverkant/db-kant houdt, hoe overzichtelijker je javascript-meuk blijft. Daar moet je IMHO pas logica instoppen als dat performancewise nodig is, niet eerder. Het queryresultaat in pakweg de PHP cachen en de operaties daar uitvoeren... ik betwijfel dat dat beter performed dan de database.

In de browser is natuurlijk in de code, daar houd ik niet van. Buiten het feit dat ik nog steeds geen javascript ken en dit in PHP op zou lossen.


Ik heb een beetje lopen proberen. Daarvoor heb ik een test database gevult met wat test data.
één categorie: categorie_id=1
deze heeft twee specs, wel te weten spec_id's '1' en '2'
drie producten met product_id's '1', '2'' en '3'
Dit is de inhoud van de speclist:

code:

Spec_id Product_id  Waarde
1   1       123
2   1       5556667777
1   2       327
2   2       666777888
1   3       158
2   3       777888999

Dit werkt prima:

SQL:

SELECT p.naam, s.waarde 
FROM `producten` as p 
JOIN spec_list as s
ON s.spec_id = 1 AND s.product_id = p.product_id 

Maar dan komt het probleem als ik speclist nog een keer wil gaan joinen om de lijst met spec_id=2 te krijgen. Ik heb al geprobeert om de techniek voor multitable joinen toe te passen en spec_list een ander alias te geven, maar dit werkt niet.

Hoe breid ik bovenstaande querie uit zodat ik spec_list nog een keer kan joinen, maar nu met spec_id = 2?

[Janoz]

Euhm, gewoon nog een keer joinen? Wel even een andere alias gebruiken natuurlijk....

[Voutloos]

Of join nog steeds 1x (zonder spec_id in de on clause), maar join ook met Categorie_Specs zodat je de spec naam erbij krijgt.

[ Voor 15% gewijzigd door Voutloos op 19-06-2008 19:01 ]

[Paul C]

Dat is precies wat niet werkt:

SQL:

SELECT p.naam, s.waarde 
FROM `producten` as p 
JOIN spec_list as s, spec_list as t
ON s.spec_id = 1 AND s.product_id = p.product_id 
AND  t.spec_id = 2 AND t.product_id = p.product_id 

#1064 - Er is iets fout in de gebruikte syntax bij 'ON s . spec_id = 1 AND s . product_id = p . product_id AND t . spec_id = 1 AND ' in regel 1

[Voutloos]

Janoz zegt:

SQL:

LEFT JOIN spec_list as s ON s.spec_id = x AND s.product_id = p.product_id
LEFT JOIN spec_list as s2 ON s2.spec_id = y AND s2.product_id = p.product_id
LEFT JOIN spec_list as s3 ON s3.spec_id = z AND s3.product_id = p.product_id
...

(maar dan met betere aliassen. )

Uiteindelijk doe jij dit ook ongeveer, maar had die laatste join gewoon netter opgeschreven moeten worden. Schrijf altijd netjes het join type voluit gevolg door de relevante join condities, maw. vermijd de karige ',' join operator.

Of het alternatief:

SQL:

LEFT JOIN spec_list as s ON s.product_id = p.product_id
LEFT JOIN categorie_specs as cat ON cat.spec_id = s.spec_id

[ Voor 21% gewijzigd door Voutloos op 19-06-2008 19:15 ]

[Paul C]

Ja, daarom heet jij dus voutloos!

Het werkt, hiermee is volgens mij alles opgelost. Bedankt!

[Janoz]

pfff... Mag Voutloos ineens met de eer strijken...