[MSSQL] view maken op basis van willekeurig aantal kolommen

[dmnq]

Ik zal proberen om het zo duidelijk mogelijk uit te leggen.

Ik heb een tabel 'Producten' met verschillende producten geidentificeerd met 'ProductID'.
Ik heb een tabel 'Eigenschappen' met verschillende eigenschappen geidentificeerd met 'EigenschapID'
Ik heb een tabel 'ProductEigenschappen' waar per product 1 of meerdere eigenschappen kunnen worden opgegeven.

Bijvoorbeeld zo:

ProductID	EigenschapID	Waarde
1	1	10
1	2	A
1	3	23.60
1	4	een stukje omschrijving
2	1	20
2	2	C
2	3	12.80
2	4	een andere omschrijving

Nou is het de bedoeling dat ik de uit bovenstaande structuur het volgende genereer...
Een tabel (view) met de volgende opmaak:

ProductID	1	2	3	4
1	10	A	23.60	een stukje omschrijving
2	20	C	12.80	een andere omschrijving

Hoe pak ik dit aan? is hier een 'standaard'-oplossing voor?

[thomaske]

Ik denk niet dat dit mogelijk is. Je kan niet je data uit je tabel splitsen en onderverdelen in verschillende kolommen..

[GoodspeeD]

Het is wel mogelijk met wat getweak. Ik ben ff bezig met een oplossing in php.

[dmnq]

Het oplossen in een script (ASP of PHP) is geen probleem...maar ook geen oplossing.
Het probleem is namelijk dat er een tabel (view) moet zijn welke door een specificieke applicatie moet worden uitgelezen.

Waarom er gekozen is voor een dergelijke opslagstructuur heeft te maken met de verschillende productgroepen. Elke productgroep heeft zijn eigen 'set' van eigenschappen. Zo kan het dus voor komen dat het ene Product maar 2 eigenschappen heeft, terwijl de andere er 16 heeft.

Per keer worden alleen producten uit dezelfde categorie opgevraagd, dus hebben ze allemaal een waarde voor de verschillende kolommen.

[GoodspeeD]

Ja, maar als er alleen een view gecreëerd dient te worden, waarom voldoet een oplossing in PHP dan niet?

[dmnq]

GoodspeeD schreef op 16 December 2002 @ 14:03:
Ja, maar als er alleen een view gecreëerd dient te worden, waarom voldoet een oplossing in PHP dan niet?

Omdat er op deze server - naast IIS - een door mijn stagebedrijf ontwikkelde applicatie draait, welke een dergelijk platte tabel nodig heeft als invoer.

Ik kan dus niet eerst met ASP een dergelijke view laten opbouwen.

Ik moet dus een generieke view hebben... zeg maar

[Verwijderd]

Dit kan wel met meerder views. Je kan een view toch data van meerdere views laten opvragen?
Dan doe je:
create view view_1 as select productid, waarde from producteigenschappen as 1 where is_integer(waarde)
create view view_2 as select productid, waarde from producteigenschappen as 2 where is_string(waarde)

en dan select 1, 2 from view_1, view_2 where view_1.productid = view_2.productid
En dan moet je ook nog views maken voor alle andere gevallen.
De functies is_integer en is_string weet ik niet of die in mssql bestaan, maar er is vast wel een equivalent voor.

[dmnq]

Verwijderd schreef op 16 december 2002 @ 14:08:
Dit kan wel met meerder views. Je kan een view toch data van meerdere views laten opvragen?
Dan doe je:
create view view_1 as select productid, waarde from producteigenschappen as 1 where is_integer(waarde)
create view view_2 as select productid, waarde from producteigenschappen as 2 where is_string(waarde)

en dan select 1, 2 from view_1, view_2 where view_1.productid = view_2.productid
En dan moet je ook nog views maken voor alle andere gevallen.
De functies is_integer en is_string weet ik niet of die in mssql bestaan, maar er is vast wel een equivalent voor.

Hiermee ben je dus gebonden aan een bepaald aantal eigenschappen, terwijl dit aantal dynamisch moet zijn.

[Verwijderd]

dmnq schreef op 16 december 2002 @ 14:10:
[...]

Hiermee ben je dus gebonden aan een bepaald aantal eigenschappen, terwijl dit aantal dynamisch moet zijn.

Ok, maar je hebt in het eindresultaat toch ook altijd 4 voorwaarden?
kolom 1 = gehele getallen
kolom 2 = character
kolom 3 = double
kolom 4 = varchar

Je kan tussenviews uiteraard ook dynamisch maken . Hiervoor moet je een stored procedure maken. Maar dat is te lang geleden voor mij, dat weet ik niet meer precies hoe 't moet

[dmnq]

Verwijderd schreef op 16 December 2002 @ 14:14:
[...]

Ok, maar je hebt in het eindresultaat toch ook altijd 4 voorwaarden?
kolom 1 = gehele getallen
kolom 2 = character
kolom 3 = double
kolom 4 = varchar

Je kan tussenviews uiteraard ook dynamisch maken . Hiervoor moet je een stored procedure maken. Maar dat is te lang geleden voor mij, dat weet ik niet meer precies hoe 't moet

Dat niet... De ene serie producten heeft bijvoorbeeld 6 eigenschappen. De andere sie heeft er 12. Dus het aantal kolommen in de doeltabel is variabel.

En de datatypen van de verschillende kolommen zijn ook elke keer anders.

[ Voor 8% gewijzigd door dmnq op 16-12-2002 14:17 . Reden: aanvulling ]

[Goodielover]

Ik heb een dergelijk DB ontworpen en compleet gerealisserd voor een erg grote organisatie in NL.

Je hebt twee opties:
1. join x-keer de eigensschaptabel aan de producttabel
dus

SQL:

SELECT p.id
      ,e1.waarde naam
      ,e2.waarde omschrijving
      ,.....
FROM   product p
      ,eigenschap e1
      ,eigenschap e2
      ,.....
WHERE  p.id = e1.p_id
and    e1.type = 1
and    p.id = e2.p_id
and    e2.type = 2
and    .......

of
2. je gebruikt een max-decode constructie (of een max-if-constructie)
dus

SQL:

SELECT p.id
      ,max(if(e.type,1,e1.waarde,'')) naam
      ,max(if(e.type,2,e1.waarde,'')) omschrijving
      ,.....
FROM   product p
      ,eigenschap e
WHERE  p.id = e.p_id
and    e.type in (1,2,....)
GROUP BY p.id

Tweede statement is kompakter en je hoeft minder tabellen te joinen.
Opbouwen van het statement kan ook veel gemakkelijker dynamisch.
Performance technisch scheelt het niet veel
Duidelijk?

Erg veel dynamischer dan dit kan niet omdat je altijd aan een vast aantal kolommen voor een view zit.
Je oplossing kan dus wel generiek zijn, maar je view niet.
Of je moet een aantal lege velden aan het einde van de view niet erg vinden voor de gevallen waarin je minder eigenschappen hebt.

[ Voor 17% gewijzigd door Goodielover op 16-12-2002 14:28 ]

[Verwijderd]

'k Vind de tabel-structuur sowiso niet zo duidelijk. Je kan beter bij product-categorie beheer een database-tabel genereren. Bijvoorbeeld zo:
create product_computer (
id int
harde schijf varchar(100),
processor (varchar(100),
...)

product_auto (
id int,
motorinhoud varchar(25),
bandenmaat varchar(25)
...)

en dan een tabel
product:
id naam
1 auto
2 motor

Dan kan je gewoon een select * laten van een product_* doen, en je krijgt nog logische veldnamen erbij ook, ipv nummers

Een nog coolere oplossing is een tabel product_fields, waarin je van elk product_id de velden zet:
productid fieldname type
1 motorinhoud varchar
5 opmerkingen text

[ Voor 26% gewijzigd door Verwijderd op 16-12-2002 14:32 ]

[dmnq]

Dit werkt niet zo. Het moet dus achteraf (door de gebruikers) mogelijk zijn om eigenschappen aan producten toe te voegen (on-the-fly).

In de tabel eigenschappen wordt opgeslagen wat voor datatype de eigenschap is.

[Verwijderd]

Is het ook goed als de client-applicatie een stored_procedure aanroept, ipv een query uitvoert? Daarin kan je nml wel alles automatisch doen

[Verwijderd]

Dan kan je aan de hand van die eigenschappen-tabel toch de juiste view genereren?

[dmnq]

Stored procedure ook niet. Je kan alleen een platte tabel als invoer geven.

[Verwijderd]

Dus wat je wil is: select * from view_producteigenschappen where productID=1, en dan automatisch alle kolommen krijgen?

[dmnq]

Verwijderd schreef op 16 December 2002 @ 14:45:
Dus wat je wil is: select * from view_producteigenschappen where productID=1, en dan automatisch alle kolommen krijgen?

ja

[Goodielover]

Luister:
Wat jij wilt kan niet!!
Een view heeft een vast aantal kolommen!
Als je per se wilt dat de gebruiker de view kan aanpassen, dan moet deze gebruiker dus DDL-privileges krijgen of je moet de view eens in de zoveel tijd automatisch opnieuw genereren uit de beschikbare eigenschappen.

Voor de definitie van de view kan je 1 van de 2 technieken gebruiken die ik eerder heb genoemd.

[ Voor 2% gewijzigd door Goodielover op 16-12-2002 14:49 . Reden: typo's ]

[Verwijderd]

Waarom geen dynamische tussentabellen? Dat is gewoon een erg simpele oplossing, en (denk ik) ook de netste

edit:
ik bedoel de hierboven genoemde oplossing. van product_fields, product_auto en product_computer. de laatste twee kan je dynamisch laten generen en aanpassen bij producten categorie beheer. Dat is ook de snelste, hoef je geen ingewikkelde joins te doen die veel performance kosten.

[ Voor 58% gewijzigd door Verwijderd op 16-12-2002 14:54 ]

[Goodielover]

Zelfde probleem. gebruiker heeft DDL-privilege nodig en je hebt nog steeds een query nodig om de tussen tabel te vullen. Laat dit nou net dezelfde SQL-code zijn als van de view

[dmnq]

Het is allemaal lastiger dan ik dacht

[Verwijderd]

Dan heb je idd ddl-privileges nodig. Is dat een probleem, dmnq?

[dmnq]

Ja ben bang van wel...

[Goodielover]

Is er ergens in een tabel vastgelegd welke eigenschappen bij welke categorie thuishoren?
en hebben die dingen daar nog een volgnummer?

[dmnq]

Ja er is een tabel CategorieEigenschappen. Zelfde kolom als ProductEigenschappen, maar dan met CategorieID ipv ProductID

[Goodielover]

zit daar een volgnummer bij om de eigenschappen te presenteren bijvoorbeeld?
Wie houdt die tabel bij dan? Doen dat ook de klanten zelf?

[ Voor 29% gewijzigd door Goodielover op 16-12-2002 15:04 ]

[dmnq]

In de tabel CategorieEigenschappen staat dus gewoon dit:

CategorieID	EigenschapID
1	1
1	2
1	3
1	4
2	1
2	2
2	3
2	5
3	4
3	7
3	9

Klanten kunnen dus zelf een 'set' van eigenschappen maken die per categorie verschillend is.
Wanneer er een nieuw product wordt aangemaakt, wordt de 'set' van eigenschappen automatisch in de database geplaatst op basis van de 'set' binnen de gekozen categorie.

[ Voor 22% gewijzigd door dmnq op 16-12-2002 15:08 . Reden: aanvulling ]

[Goodielover]

Dus je kan de wijziging die de klant aanbrengt in deze tabel gebruiken om de view opnieuw te genereren. Mogelijk kan je dit wel met een DB-trigger of en stored procedure oid doen.

[dmnq]

Dat zou idd kunnen...

[Goodielover]

Ik vond de gelijkenis tussen jou en een gebruiker ook al groot. Je komt met de oplossing en niet met het probleem. Nu is het probleem duidelijk en komen er ook ineens oplossingen naar boven die jij nog niet bekeken. Daarom graag bij je eerste vraag iets meer context.

Wat op dit moment nog open staat is bijvoorbeeld of het een flat file interface is of een SQL-tabel interface. Dit kan ook nog verschil maken in je oplossing. Bij een flat file kan je ook gewoon alle eigenschappen concateneren (met een max van 20 eigenschappen oid) lege velden raak je dan gewoon kwijt doordat er wordt geconcateneerd met een lege string.

[dmnq]

wat bedoel je met: "Ik vond de gelijkenis tussen jou en een gebruiker ook al groot. "???

[Goodielover]

Ik dacht toen ik je verhaal aan het begin las, wat wil hij nou precies. Je kwam met een oplossing (dynamische views) voor een probleem dat pas duidelijk werd in de loop van de discussie. Dat is gedrag dat ik ook bij mijn klanten/gebruikers aantrof toen ik systemen ontwikkelde.

Is verder niets mis mee hoor. Toont alleen maar aan dat je zelf ook al behoorlijk ver was met het nadenken over de oplossing en inderdaad op een probleempunt zat.

[dmnq]

Ok thanks....

Ik heb zitten denken. Misschien is het wel wat om toch voor elke categorie een aparte view te laten genereren.

[Goodielover]

Lijkt mij ook een goed idee. Dan ziet de databse er voor de gebruikers uit als een normaal opgezette database.
Denk je wel even aan toegangsrechten op die views, of gaat iedereen als dezelfde user naar de DB. Als je de view dropt en opnieuw aanmaakt, zijn alle grants op die view verdwenen.

[dmnq]

De applicatie haalt de gegevens op... dus dat is in principe dus 1 user.

Bedankt voor de hulp allemaal. Ik denk dat ik er wel uit kom

[Verwijderd]

Heb je wel eens naar User Defined Functions gekeken? Deze zijn flexibeler dan sprocs, en kunnen ook een tabel als returnwaarde geven. Ook kun je UDF's gewoon in een SELECT query opnemen .