[Postgres][SQL] hoe om te gaan met genormaliseerde data?

[107mb]

ik heb een vraag over omgaan met genormaliseerde data:

de volgende tabellen heb ik:

tblMateriaal

ID	materiaalnummer	omschrijving
1	18	hout
2	112	staal

tblMateriaalKenmerken

ID	materiaalnummer	kenmerk	kenmerkwaarde
1	18	lengte	1950
2	18	houtsoort	vuren
2	18	hoogte	Null

nu wil ik dus een view in Postgres maken die bepaalde kenmerken laat zien.

optie één:

SQL:

 
select distinct m.materiaalnummer, 
m.omschrijving,
case when mk.kenmerk = 'LENGTE' then mk.kenmerkwaarde end as lengte
from tblmateriaal m
left join tblmateriaalkenmerken mk on m.materiaalnummer = mk.materiaalnummer

het resultaat wat ik krijg is
18, hout, 1950
18, hout, Null

waarom krijg ik die Null? Er zijn vele kenmerken met Null-waarden, komt het daar vandaan?


een andere manier:

SQL:

select distinct m.materiaalnummer, 
m.materiaalcode,
(select mk.kenmerkwaarde from tblmateriaalkenmerken mk where m.materiaalnummer = mk.materiaalnummer and mk.kenmerk = 'LENGTE') as lengte
from tblmateriaal m

dit laat wel de goede waarde zien, maar ik moet nog meerdere tabellen joinen, en op deze manier wordt het een zooitje.

Kan iemand aangeven hoe men met joins icm deze tabelopbouw omgaat?

[Woy]

107mb schreef op dinsdag 20 oktober 2015 @ 16:24:
ik heb een vraag over omgaan met genormaliseerde data:

de volgende tabellen heb ik:

tblMateriaal

ID	materiaalnummer	omschrijving
1	18	hout
2	112	staal

tblMateriaalKenmerken

ID	materiaalnummer	kenmerk	kenmerkwaarde
1	18	lengte	1950
2	18	houtsoort	vuren
2	18	hoogte	Null

nu wil ik dus een view in Postgres maken die bepaalde kenmerken laat zien.

optie één:

SQL:

 
select distinct m.materiaalnummer, 
m.omschrijving,
case when mk.kenmerk = 'LENGTE' then mk.kenmerkwaarde end as lengte
from tblmateriaal m
left join tblmateriaalkenmerken mk on m.materiaalnummer = mk.materiaalnummer

het resultaat wat ik krijg is
18, hout, 1950
18, hout, Null

waarom krijg ik die Null? Er zijn vele kenmerken met Null-waarden, komt het daar vandaan?

Je doet nu een (left) join op materiaalnummer. In de voorbeeld-data die je toont zijn daan 3 rows die voldoen bij hout, en 0 bij staal. Dan doe je in je select een case die alleen de waarde selecteert als het kenmerk lengte is. Ik zou met deze data 4 rijen verwachten waar bij 1 rij de waarde lengte gevuld is, en de rest NULL.
Als je alleen attributen van het kenmerk lengte terug wil zal je dat ook als join conditie op moeten nemen.


Bij dit soort genormaliseerde data zul je deze meestal in je datalaag verwerken zodat de attributen bij het juiste object gezocht worden. Een andere optie is om alle attributen te selecteren en iets van PIVOT te gebruiken, maar dat is afhankelijk van welke database engine je gebruikt.

[107mb]

Woy schreef op dinsdag 20 oktober 2015 @ 16:34:
[...]

Ik zou met deze data 4 rijen verwachten waar bij 1 rij de waarde lengte gevuld is, en de rest NULL.
Als je alleen attributen van het kenmerk lengte terug wil zal je dat ook als join conditie op moeten nemen.

dit heeft met de distinct te maken


jouw opmerking over de conditie opnemen in de join was de oplossing:

SQL:

select m.materiaalnummer, 
m.materiaalcode,
mkl.kenmerkwaarde
from tblmateriaal m
left join tblmateriaalkenmerken mkl on m.materiaalnummer = mkl.materiaalnummer and mkl.kenmerk='LENGTE'

dit betekent dus wel dat ik meerdere joins naar dezelfde tabel moet opnemen indien ik andere gegevens uit de kenmerktabel wil halen.

[ Voor 36% gewijzigd door 107mb op 20-10-2015 17:41 ]

[Woy]

107mb schreef op dinsdag 20 oktober 2015 @ 17:13:
[...]
dit heeft met de distinct te maken

O ja logisch natuurlijk, die had ik over het hoofd gezien. Al zou ik dan ook nog een record voor staal met een null value voor lengte verwacht aangezien je een left join doet, en geen inner join.

dit betekent dus wel dat ik meerdere joins naar dezelfde tabel moet opnemen indien ik andere gegevens uit de kenmerktabel wil halen.

Ja als je ze met standaard SQL als columns wil krijgen zul je per column een join moeten doen. Sommige databases ondersteunen ook iets als PIVOT, waarbij dat niet nodig is.

In de meeste gevallen zal je de rows gewoon in een datalaag of presentatielaag omzetten naar columns/fields van een object.

[storeman]

Dit hoeft zeker niet! Je kunt dit gewoon in Postgres oplossen, omdat het zo'n fantastische DB is. Ik ga de query niet helemaal voor je uitzoeken/uitschrijven, maar je kunt dit doen met de TableFunc extensie. Die kun je eenvoudig installeren vanuit PGAdmin of met een simpele query.

Met behulp van de crosstab functie kun je vervolgens je tabel maken:
http://www.postgresql.org/docs/9.1/static/tablefunc.html

[Woy]

storeman schreef op dinsdag 20 oktober 2015 @ 20:20:
Dit hoeft zeker niet! Je kunt dit gewoon in Postgres oplossen, omdat het zo'n fantastische DB is. Ik ga de query niet helemaal voor je uitzoeken/uitschrijven, maar je kunt dit doen met de TableFunc extensie. Die kun je eenvoudig installeren vanuit PGAdmin of met een simpele query.

Met behulp van de crosstab functie kun je vervolgens je tabel maken:
http://www.postgresql.org/docs/9.1/static/tablefunc.html

Dat is dus exact het PIVOT gedeelte waar ik op hint, alleen weet ik niet exact hoe dat in Postgres werkt. Zoals ik ook opmerkte werkt dit voor elk DBMS weer anders ( Als het überhaupt ondersteund wordt ).

Toch blijft mijn ervaring dat je dit meestal beter/makkelijker buiten je database op kunt lossen ( Bij het inlezen van je records is het eenvoudig om te zetten van row naar column based, maar dat is natuurlijk wel afhankelijk van je use-case )

[ Voor 14% gewijzigd door Woy op 20-10-2015 21:28 ]

[storeman]

Die termen ben ik niet zo in thuis, dus dat had ik niet opgepikt.

Het draait al een jaar of vijf met die crontabs. Het is een combinatie van een stuk of vijf tabllen (type, type property, item en itempropertyvalue). Er staan zo'n 340.000 items in met in totaal zo'n 2,4 miljoen propertyValues.

Om de view met campers te bekijken, wat zo'n 60 rijen is, duurt het ophalen ongeveer 300ms. Niet heel vlot dus, echter als ik where condities erop loslaat, daalt de snelheid. Dit lijkt erop te duiden dat indexen wel goed gebruikt worden.