[SQL] Onbeperkte 'Carthesisch query' opslaan in DB

[Polderdijk]

Ik ben aan het stoeien met CROSS JOIN / Carthesisch query's (Programming FAQ - SQL) om een bepaald product te koppelen aan diverse (onbeperkte) productopties met haar eigen artikelnummers en prijzen.

Wat heb ik? Ik heb een tabel Producten, ProductOpties en ProductOptieWaardes Uiteraard bevat de tabel Producten alle producten uit de webshop. De ProductOpties bevat 2 kolommen, één Id kolom en een Naam. Deze kolom gebruik ik om diverse 'soorten' productopties aan te geven (kleur, afmeting, BH maat, etc.). En aan deze kolom hangt weer de ProductOptieWaardes welke uiteraard een eigen Id, en een ProductOptieId heeft, en een eigen naam. Zo kan je dus onder de ProductOptie kleur diverse kleuren opslaan.

Tabel Producten:

ProductId	ProductNaam	ProductArtikelNummer	ProductPrijs
1	Shirt diverse maten en kleuren	100001	25,00

Tabel ProductOpties:

ProductOptieId	ProductNaam
1	Maat
5	Kleur
7	Uitvoering

Tabel ProductOptieWaardes:

ProductOptieWaardeId	ProductOptieId	ProductOptieNaam
1	1	XXS
2	1	XS
3	1	S
4	5	Zwart
5	5	Wit
6	5	Blauw
7	7	Korte mouw
8	7	Lange mouw

Nu is het dus de bedoeling dat ik aan een Product (ProductId) een mix van bovenstaande ProductOpties kan toevoegen. Ik kan eigenlijk heel simpel een correct lijst middel CROSS JOIN aan elkaar knopen van de beschikbare productopties en deze netjes als een 'matrix' laten vertonen op de website:

SQL:

SELECT
       Maat.ProductOptieNaam AS nMaat,
       Maat.ProductOptieWaardeId AS iMaat,
       Kleur.ProductOptieNaam AS nKleur,
       Kleur.ProductOptieWaardeId AS iKleur,
       Uitvoering.ProductOptieNaam AS nUitvoering,
       Uitvoering.ProductOptieWaardeId AS iUitvoering
FROM
       ProductOptieWaardes AS Maat CROSS JOIN
       ProductOptieWaardes AS Kleur CROSS JOIN
       ProductOptieWaardes AS Uitvoering
WHERE
       Maat.ProductOptieId = 1 AND 
       Kleur.ProductOptieId = 5 AND 
       Uitvoering.ProductOptieId = 7
ORDER BY
       iMaat, 
       iKleur, 
       iUitvoering

Hieruit krijgen we dan een prachtig tabel:

nMaat	iMaat	nKleur	iKleur	nUitvoering	iUitvoering
XXS	1	Zwart	4	Korte mouw	7
XXS	1	Zwart	4	Lange mouw	8
XXS	1	Wit	5	Korte mouw	7
XXS	1	Wit	5	Lange mouw	8
XXS	1	Blauw	6	Korte mouw	7
XXS	1	Blauw	6	Lange mouw	8
XS	2	Zwart	4	Korte mouw	7
XS	2	Zwart	4	Lange mouw	8
XS	2	Wit	5	Korte mouw	7
XS	2	Wit	5	Lange mouw	8
XS	2	Blauw	6	Korte mouw	7
XS	2	Blauw	6	Lange mouw	8
S	3	Zwart	4	Korte mouw	7
S	3	Zwart	4	Lange mouw	8
S	3	Wit	5	Korte mouw	7
S	3	Wit	5	Lange mouw	8
S	3	Blauw	6	Korte mouw	7
S	3	Blauw	6	Lange mouw	8

Met bovenstaand resultaat kan je dus zo met ASP een prachtige matrix maken met bovenstaande combinatiemogelijkheden. Aan deze combinatie wil ik dus een aantal variabelen in opslaan. Dus bijvoorbeeld XS, Blauw, Korte mouw heeft artikelnummer 10002 en heeft een prijscorrectie van € 1,00 vergeleken met het 'moeder'-product (tabel Producten), en een checkbox wel/niet voorraad/bestelbaar.

Tot zover ben ik gekomen, alleen kom ik er absoluut niet meer uit als ik bovenstaand formulier weer wil opslaan in de database en te koppelen aan een ProductId. Het is de bedoeling dat je aan elk artikel onbeperkt opties kan koppelen, en uiteraard elk product kan weer andere ProductOpties koppelingen hebben.
Eerst had ik in gedachte om een koppeltabel te maken met daarin ProductId en daarnaast een legio aan kolommen waar ik de eventuele ProductOptieWaardeId in op kan slaan met de daarbij behorende variabelen.
Echter ben je hier dus altijd gelimiteerd aan het aantal kolommen dat je toevoegd, en het lijkt me ook niet echt database logisch om het op deze manier aan te pakken.

Ook heb ik laatst een mooie parent/child oplossing gemaakt om een onbeperkte categoriestructuur te kunnen creeëren, maar dit lijkt me niet echt handig voor deze oplossing en kost aardig wat performance van ASP om hier een nette 'structuur' van te maken en dus niet geschikt.

Heeft er iemand een idee hoe je dit nou mooi in het vat/database moet gieten?

[ Voor 0% gewijzigd door Polderdijk op 29-05-2008 11:54 . Reden: JOIN Inhoud naar Uitvoering aangepast ]

[bigbeng]

Echt netjes krijg je dit volgens mij nooit omdat je een matrixstructuur probeert op te slaan in de database. Volgens mij kom je met een parent-child nog het verste, omdat je hiermee het "variabele kolommen" probleem niet hebt.

Is het de bedoeling dat alle verschillende opties altijd kunnen, of zijn er nog zaken die elkaar uitsluiten? Ik kan me zo voorstellen dat bijvoorbeeld een optie korte mouw vs lange mouw (vergeet dit voorbeeld) komt die nooit tegelijkertijd gaan optreden, maar individueel wel. Dat kun je dus weer niet met 1crossjoin doen, daar zul je twee crossjoin queries en een union voor moeten gebruiken.

En de performance hit, die valt op zich nog wel mee (afhankelijk van het aantal opties), je zult de ASP pagina (of database layer) echter wel dynamisch je query laten samenstellen en mogelijk moet je eerst een query doen die de verschillende mogelijke opties van een product ophaalt voor kolomnamen zoals Kleur en dergelijke.

Leuk idee zo'n crossjoin, maar volgens mij haal je jezelf aardig wat op de hals

[ Voor 2% gewijzigd door bigbeng op 29-05-2008 11:25 . Reden: Wakker worden :) ]

[SchizoDuckie]

Ik snap uit je TS nog niet helemaal wat je nou precies wil opslaan? Wil je nou je selectiecritera opslaan of de resultaat-tabel

In het eerste geval zou ik echt heeel lomp een object met variabelen serializen en in een kolom in de db opslaan. Anders ga je eerst een (of meerdere zelfs) super uitgebreide query doen om je selectiecriteria te vinden daarna via je favo serverside taal de query opbouwen en daarna nog een keer die query weer uitvoeren.

In zo'n geval is een serializebaar object imo een goede optie, je hebt in 1x je bak met gegevens bij elkaar, het is onbeperkt uitbreidbaar, en het is uiteindelijk toch data die zich niet in 1x in je datamodel laat vangen.

[pedorus]

Het lijkt me dat je een soort subproducten hebt, die 1 of meer opties hebben, bij een product horen, een prijs(opslag) hebben en al dan niet leverbaar zijn. Tussen subproducten en ProductOptieWaardes krijg je dan een many-to-many relatie met een simpele koppeltabel.

Die query van de crossjoin klopt niet helemaal (Inhoud in from moet zijn Uitvoering denk ik). De crossjoin kan helpen bij het maken van de tabel subproducten en de koppeltabel, als steeds alle combinaties mogelijk zijn.

[Polderdijk]

SchizoDuckie schreef op donderdag 29 mei 2008 @ 11:25:
Ik snap uit je TS nog niet helemaal wat je nou precies wil opslaan? Wil je nou je selectiecritera opslaan of de resultaat-tabel

Het is de bedoeling dat de selectie vanuit de CROSS JOIN in een ander tabel wordt opgeslagen mits er minimaal een artikelnummer is ingevoerd.

Je moet dus zien dat ik een HTML tabel heb die er ongeveer zo uitziet:

nMaat	iMaat	nKleur	iKleur	nUitvoering	iUitvoering	Art.Nr	Prijs
XXS	1	Zwart	4	Korte mouw	7	10001	0
XXS	1	Zwart	4	Lange mouw	8	10002	0
XXS	1	Wit	5	Korte mouw	7	10003	0
XXS	1	Wit	5	Lange mouw	8
XXS	1	Blauw	6	Korte mouw	7
XXS	1	Blauw	6	Lange mouw	8
XS	2	Zwart	4	Korte mouw	7	10004	-1,25
XS	2	Zwart	4	Lange mouw	8	10005	-1,25
XS	2	Wit	5	Korte mouw	7
XS	2	Wit	5	Lange mouw	8
XS	2	Blauw	6	Korte mouw	7	10020	+1,00
XS	2	Blauw	6	Lange mouw	8	10021	+1,00
S	3	Zwart	4	Korte mouw	7
S	3	Zwart	4	Lange mouw	8
S	3	Wit	5	Korte mouw	7
S	3	Wit	5	Lange mouw	8
S	3	Blauw	6	Korte mouw	7
S	3	Blauw	6	Lange mouw	8

Nu wil ik dus alleen de records waarin minimaal een artikelnummer zijn (dus pas besteld kan worden) opslaan in de database zodat ik er later in de shop een pulldown van kan maken met de verschillende combi's en daarbij de meer/minder prijs.

En uiteraard moet het dan op z'n manier opgeslagen worden dat later altijd de matrix weer opgehaald kan worden. Eventuele nieuwe productopties moeten dan direct ook meegenomen worden.

pedorus schreef op donderdag 29 mei 2008 @ 11:25:
Het lijkt me dat je een soort subproducten hebt, die 1 of meer opties hebben, bij een product horen, een prijs(opslag) hebben en al dan niet leverbaar zijn. Tussen subproducten en ProductOptieWaardes krijg je dan een many-to-many relatie met een simpele koppeltabel.

Niet helemaal. Het is namelijk bijna altijd dat alle combinaties mogelijk zijn. Het is dus niet zomaar een subproduct mijn de benodigde eigenschappen. Dit zou een stuk lastiger beheerbaar zijn, maar ook voor mijn doeleinde niet juist.

[pedorus]

Polderdijk schreef op donderdag 29 mei 2008 @ 12:22:
Niet helemaal. Het is namelijk bijna altijd dat alle combinaties mogelijk zijn. Het is dus niet zomaar een subproduct mijn de benodigde eigenschappen. Dit zou een stuk lastiger beheerbaar zijn, maar ook voor mijn doeleinde niet juist.

Huh, in het bovenstaande voorbeeld zijn bijvoorbeeld XXS Zwart en XS Blauw wel mogelijk, maar XXS Blauw niet. Klopt het voorbeeld niet?

In mijn idee levert die voorbeeldtabel dus 7 entries op in SubProducten(Artikelnummer,ProductId,Prijscorrectie,Beschikbaar), en 7*3 entries op in SubProductOptie(Artikelnummer,ProductOptieWaardeId). Met de juiste query krijg je dan in 1x de voorbeeldtabel terug (ik geeft toe, wel bijvoorbeeld met veel left joins).

[Bosmonster]

Polderdijk schreef op donderdag 29 mei 2008 @ 12:22:
[...]

Nu wil ik dus alleen de records waarin minimaal een artikelnummer zijn (dus pas besteld kan worden) opslaan in de database zodat ik er later in de shop een pulldown van kan maken met de verschillende combi's en daarbij de meer/minder prijs.

Waarom zit je moeilijk te doen met dat matrix dan, als je gewoon de artikelnummers en bijbehorende eigenschappen wilt? Of snap ik je probleem niet?

[EfBe]

Die cross-joins kloppen niet, want je creeert dan m*n tuples. Je moet een inner join gebruiken met dezelfde table.

Je model is overigens een DB in een DB en dat levert altijd query problemen op. Een query is verder een instrument om data uit een database te krijgen, niet een instrument om UI's mee te maken. Je moet dus zoeken naar de query die je alle informatie geeft op een zo efficient mogelijke wijze. DAARNA ga je op de client (de code die de query aanroept) de data consumeren en er iets van bakken, bv een matrix van info in een webpage.

Je ziet het al aan de manier hoe je je gegevens opslaat: je hebt een lookup die een string verbindt aan een property value. (kleur, maat etc.). Dit is onzin. Een product heeft x aantal properties, en dat zijn gewoon values. Je kunt daar eventueel een 'type' aan hangen, maar dat is voor de data irrelevant: immers die waarde wordt alleen gebruikt in de UI.

Je data die terugkomt uit de database zou dus product moeten joinen met die property table via een inner join en that's it. Via order by clauses kun je alles in de juiste volgorde krijgen.

Maar, als je dat allemaal voor elkaar hebt, zul je erachter komen dat je een data element mist: product. Je hebt nu alle mogelijke eigenschappen gedefinieerd, maar je hebt in je winkel een eindig aantal product typen. Bv 10 verschillende T-shirt typen. Per product type, die een unieke code hebben, heb je een serie properties die bij dat product horen: de kleuren, de maten etc.

Wanneer je dan een PRODUCTTYPE uit de database haalt, haal je dan ook de properties op die bij je producttype horen. En dat doe je dus niet met een cross-join want je hoeft alleen maar een inner join te doen met producttype en de properties voor dat producttype.

Dat komt dan als een lijst aan bij je BL en UI en daarmee ga je aan het werk.

Het is overigens helemaal niet erg om per producttype een serie tabellen te maken. Ongeletterde digibeten willen vaak een volledig flexibel systeem en toch super performance en wat al niet, maar dat kan gewoonweg niet: als jij logica wil toevoegen mbt kledingmaten, dan MOET jij weten wat maten values zijn, en DUS moet je daar een attribute/entity voor definieren. Verder is het wellicht logisch dat je logica inbouwt die betrekking heeft op kleding en nutteloos is mbt kabels en asbakken. Die logica komt alleen in jouw programma als je het toevoegt. Die ongeletterde digibeet snapt dat niet, en eist een flexibel systeem. Maar, omdat de logica nodig is, moet je dus het systeem aanpassen, de user kan dat zelf niet. En DUS is het niet erg om er dan ook de tabellen bij te plaatsen die werken met de logica, e.g. kleding tabellen etc.

Wat is er bv op tegen om een Maat table lookup te maken en bv een Kledingstuk tabel met een FK naar Maat ? Is dat erg? Je MOET het zo doen wil je kunnen redeneren over wat de data voorstelt, bv wanneer je logica wil inbouwen die met kledingstukken werkt in jouw store front.

[ Voor 23% gewijzigd door EfBe op 29-05-2008 15:04 ]