datamodel met "meta-tags"

[Stefke]

Hopelijk staat deze in t juiste topic

Ik ben bezig met een (access)-database waarin een grote set data moet komen te staan, maar het probleem is dat al deze data verschillende vormen heeft. Het gaat om tabelwaarden die op basis van één of meer parameters gevonden moeten worden, dus iets als:

tabel

code:

waarde  lengte  breedte
10       A       1          
15       A       2
20       A       3
30       B       1
40       B       2
50       B       3

De juiste waarde is te vinden door te zoeken op 2 parameters, dus:
SELECT waarde FROM tabel WHERE param1 = A and param2 = 3 levert: 20

Nu is het probleem dat er tientallen, honderden sets zijn met 1 tot wel 10 parameters, en voor elke set zijn de parameters anders benoemd. En het aantal sets kan zomaar uitgebreid worden met nieuwe sets met andere parameters

Dan zijn er drie oplossingen:
1) alles volledig normaliseren in afzonderlijke tabellen: geen beginnen aan en te inflexibel


2)

code:

waarde  lengte      breedte     dichtheid   gewicht    etc
set1
10                               AB           x     
15                               AC           y
20                               AD           z
set2
30       B           1                        
40       B           2                 
50       B           3

Dit vind ik geen fijne oplossing want de hele structuur van de sets zit in de kolomnamen. Heel veel kolomnamen (honderden) en elke aanpassing aan een set betekent aanpassingen aan de structuur van de database.

3) Andere oplossing

code:

waarde  kolom1       kolom2      kolom3    etc
set1
10       A           1               
15       A           2            
20       A           3                
30       B           1                  
40       B           2                  
50       B           3
set2
zz       123         434          543
yy       432         767          656
vv       765         432          32

Voordeel: elke set waarden kan zoveel parameters gebruiken als er kolommen zijn. Nadeel: als het aantal kolommen niet toereikend is voor een nieuwe set betekent dat structurele aanpassingen. Groter nadeel vind ik dat de kolommen geen duidelijke naam hebben en je telkens moet gaan zoeken welke kolom ook al weer welke parameter vertegenwoordigd (zou op te lossen zijn met een "configuratietabel" waarin het systeem de juiste kolom aan de juiste parameter koppelt.
De juiste waarde vind je door SELECT waarde FROM tabel WHERE kolom1 = X AND kolom2 = Y and kolom3 = Z (waarbij je moet weten welke kolom waarvoor staat)

Nu heb ik een andere oplossing voor ogen, nl. met "meta-velden"

Tabel1:

code:

waardeID waarde
1      10
2      15
3      AC
4      AB

tabel2

code:

metaID  waardeID  metaveld     metavalue
set1
1        1         lengte       23
2        1         breedte      10
3        2         lengte       45
4        2         breedte      20
set2
5        3         dichtheid    543
6        3         gewicht      23
7        3         materiaal    B
8        4         dichtheid    123
9        4         gewicht      12
10       4         materiaal    A

Dit levert een ultraflexibele structuur op, immers, de "kolomnamen" zijn nu records geworden en ik kan nu zonder enige invloed op de databasestructuur een nieuwe set opnemen met eender welke aantal parameters of parameterveldnamen.

Het nadeel is echter dat zoeken in deze structuur wat lastiger is en ook kan deze "virtuele" tabel niet meer aan bijv. een userform gekoppeld worden. Dat laatste is niet zo'n probleem, alleen ben ik niet helemaal tevreden met de manier waarop ik nu mijn "waarde" op kan zoeken.

Ik moet nl. de twee tabellen aan elkaar knopen en in dezelfde kolom gaan zoeken naar één waarde die aan alle parameters voldoet.

code:

SELECT tabel1.Waarde, tabel2.MetaKey, tabel2.MetaValue
FROM tabel1 INNER JOIN tabel2 ON tabel1.WaardeID = tabel2.waardeID

Een

code:

WHERE (metaveld="param1set1" AND metavalue=X)  AND (metaveld="param2set1" and metavalue=y)

levert niets op, want geen enkele record voldoet daaraan. Een metaveld is nl. maar één waarde, dus niet param2 en param1

Een

code:

WHERE (metaveld="param1set1" AND metavalue=X)  OR (metaveld="param2set1" and metavalue=y)

levert meerdere records op, want er voldoen meer "waarden" aan één van beide eisen.

Er is echter maar één waarde die aan allebei de voorwaarden voldoet, maar hoe vind ik die?


Ik hoop dat het een beetje duidelijk is wat de bedoeling is.

Misschien dat het helpt om duidelijk te maken wat ik bedoel: een site als Wordpress is gebaseerd op een dergelijk datamodel: alle tabellen zitten verstopt in een dergelijke structuur en de "relatie" zijn verwijzingen naar kolommen binnen de tabellen zelf en in de basis zijn er maar twee tabellen: de "waarden" en de "metavelden" (post+postmeta)

Ik heb een mogelijke oplossing, maar daar ben ik niet zo enthousiast op (nl. van alle gevonden waarden tellen aan hoeveel van de gezochte parameters deze voldoet. Alleen degene met de hoogst aantal "hits" is de waarde die ik zoek)

[ Voor 3% gewijzigd door Stefke op 27-02-2013 17:22 ]

[Donderpoes]

Ik heb zelf nog nooit nagedacht over een situatie als deze.
Ik heb het volgende gemaakt:

Tabel meta:

id	value
1	AB
2	AC
3	AD
4	AE
5	AF
6	10
7	20
8	30
9	40

Tabel metaValues:

metaId	metaName	metaValue
1	length	50
1	width	45
1	height	23
2	length	110
2	density	34
2	depth	45
2	size	233
3	width	3435
3	density	23
3	size	345
4	density	233
5	length	20
5	height	4334
6	height	12
6	density	32
7	length	50
8	density	24
9	height	34
9	density	454

Ik laat deze query erop los:

code:

SELECT 
    m.id
    ,m.value
FROM 
    metaValues mv 
INNER JOIN 
    meta m 
    ON 
        m.id = mv.metaId 
WHERE 
    (mv.metaName = "length" 
        AND mv.metaValue = 50) 
    OR (mv.metaName = "width" 
        AND mv.metaValue = 45) 
    OR (mv.metaName = "height" 
        AND mv.metaValue = 23) 
GROUP BY 
    mv.metaId
HAVING 
    COUNT(mv.metaId) = 3

Dan krijg ik dit als result:

code:

id value
1  AB

Dan moet je bij HAVING het aantal parameters opgeven waar je resultaat aan moet voldoen. Ik geef 3 parameters op in de where clause, dus geef ik bij having 3 op.

Ik krijg nu value AB met id 1 terug. Omdat hij aan alle 3 de voorwaarden voldoet. Stel ik had er nog een rij instaan die aan dezelfde parameters voldoet, maar nog een parameter extra zou hebben en die wil je niet terugkrijgen omdat je geen 4 parameters hebt opgegeven kan je ook nog dit stukje aan de query toevoegen:

code:

AND
    (SELECT COUNT(id) FROM metaValues WHERE metaId = mv.metaId) = 3

Hierin geef je dan op hoeveel parameters een waarde moet hebben.

Bijvoorbeeld:

code:

SELECT 
    m.id
    ,m.value
FROM 
    metaValues mv 
INNER JOIN 
    meta m 
    ON 
        m.id = mv.metaId 
WHERE 
    (mv.metaName = "length" 
        AND mv.metaValue = 50) 
GROUP BY 
    mv.metaId
HAVING 
    COUNT(mv.metaId) = 1
AND
    (SELECT COUNT(*) FROM metaValues WHERE metaId = mv.metaId) = 1

Geeft terug:

code:

id  value
7   20

Zonder

code:

AND
    (SELECT COUNT(*) FROM metaValues WHERE metaId = mv.metaId) = 1

geeft de query terug:

code:

id  value
7   20
1      AB

Disclaimer
Ik weet niet of je er wat aan hebt.
Ik weet niet of dit zo werkt in een access database (dit is MySQL).
Ik weet niet of dit de performance ten goede komt.
Ik weet niet of er een betere manier voor is.
Ik weet niet of deze code bugfree is.

Ik ben eigenlijk nog nooit met iets als dit in aanraking gekomen, maar vind het wel een leuk vraagstuk dus ga er nog even verder over nadenken.

[ Voor 7% gewijzigd door Donderpoes op 27-02-2013 19:33 ]

[pedorus]

stefijn schreef op woensdag 27 februari 2013 @ 17:13:
Er is echter maar één waarde die aan allebei de voorwaarden voldoet, maar hoe vind ik die?

Meerdere keren joinen op dezelfde tabel (gebruik een alias). Maar bekijk even deze 2 resources:
http://www.slideshare.net...-antipatterns-strike-back
Wikipedia: Inner-platform effect

M.a.w. lijkt het mij logisch om (geautomatiseerd) voor iedere set een tabel aan te maken, NULL-waardes voor lief te nemen, of gebruik te maken van een nosql-database met een flexibel model (couchdb, cassandra, enz.).

[RobIII]

Ik zou ook naar een NoSQL kijken. Met Access wordt het drama, zéker als je een beetje multiuser e.d. wil gaan doen. M.a.w: hoe vast zit je aan Access? Wat je nu aan 't doen bent is een database-in-een-database aan 't bouwen en dat gaat je geheid gezeik opleveren. Zoals je zelf al aangeeft (oplossing 3 bijv) ben je appels en peren in eenzelfde kolom aan 't frotten, loop je te zeulen met irrelevante velden bij records (oplossing 2), ben je veel te inflexibel (oplossing 1) en voor DonderPoes' oplossing (die je overigens met een extra tabel wel nog wat netter zou kunnen maken, zie EAV model in pedorus' post) moet je al gauw naar alle "values" in varchars (wat weer klote queried als je een int of datum ofzo wil hebben, want dan kun je weer geen datetime functies gebruiken om maar wat te noemen) of alsnog per value van elk type 1 veld hebben. Dit is niet één maar al tig keer voorbij gekomen hier.

[ Voor 76% gewijzigd door RobIII op 27-02-2013 20:41 ]

[Stefke]

Nog bedankt voor jullie reacties. Ik kom er binnenkort op terug maar t is een beetje druk, dit onderdeel van de database is nog even buiten beeld

Donderpoes schreef op woensdag 27 februari 2013 @ 19:05:

Ik heb ongeveer deze oplossing al in gedachten, alleen krijg ik in MSAccess niet in één keer een query die jij maakt die én filtert én het aantal "hits" oplevert. Dat lukt wel in 2 stappen (een query die filtert en daarop een query die de "hits" telt en dan is de record die ik zoek degene met het hoogste aantal hits - of een specifiek aantal).

Een andere oplossing is een kruistabelquery maken op deze data, dan krijg je (ongeveer) de tabellen terug waar de data uit bestaat kun je daar op de "normale" manier op filteren

Nogmaals bedankt, I'll be back

[BikkelZ]

RobIII schreef op woensdag 27 februari 2013 @ 20:32:
Ik zou ook naar een NoSQL kijken.

Ik denk dat het inderdaad een case is voor NoSQL. Eigenlijk zit integriteit je meer in de weg dan dat het nog wat oplevert.

Hoe groot is de kans dat je ooit nog een keer deze data naar een "integere" SQL datastructuur wilt overzetten, zoals de optie die je meteen van de hand wees in je openingspost?

[Stefke]

Ik begrijp niet precies wat je bedoelt, maar als het er om gaat dat de database ooit naar een SQL of mySQL-database (server) moet, dat is een reeele optie maar op dit moment niet aan de orde.

Als je doelt op optie 1) die ik aandroeg, dat gaat zeker nooit gebeuren (normalisatie)

Voor de beeldvorming: de data betreft normen voor een heel scala aan verschillende werkzaamheden, waarbij de duur van de werkzaamheden afhangt van één of meer "parameters", dus bijv. het plaatsen van een pijpleiding van 1 meter kost een uur, van 2 meter kost 2 uur. Er is dus "ergens" een tabelletje waarin staat hoeveel het plaatsen van een pijpleiding kost afhankelijk van de lengte. De uitkomst is over het algemeen een tijdsduur.
Zo zijn er honderden tabelletjes voor honderden verschillende werkzaamheden aan verschillende "producten" varierend van een paar tot tientallen variaties binnen een tabel.

Aangezien er "zomaar" een nieuw product bij kan komen moet het ook mogelijk zijn om nieuwe werkzaamheden toe te voegen met een andere structuur. Vandaar dat is een flexibele oplossing nodig heb.
Imo is de oplossing die ik nu voor ogen heb "ideaal" daarvoor, alleen is het werken met data in deze vorm in Access wat minder handig omdat de standaardfunctionaliteit het niet echt ondersteund, bijv. het koppelen van recordsets aan forms

Op zich niet zo'n probleem, daar kan ik wel VBA voor schrijven. Daarbij zal de userinterface alleen met deze data in "contact" komen waar het het toevoegen van nieuwe normen + bijbehorende waarden betreft. Ik hoop dat met een paar stukken standaard VBA af te kunnen vangen ongeacht bijv de hoeveelheid parameters per norm.
Als de data in het systeem staat is deze "redelijk" statisch, 95% of meer van de acties op deze data zal het opzoeken van een norm zijn o.b.v. parameters, door het systeem.

Van noSQL heb ik geen kaas gegeten, binnen het projectbudget ga ik daar niets mee realiseren. Wel zag ik op de noSQL-wikipedia pagina deze verwijzing staan
Wikipedia: Attribute–value pair

Volgens mij is dit wat ik nu probeer te maken..

Het enige "probleem" wat ik heb is hoe ik het beste de normen op kan laten zoeken, ik was benieuwd of er wellicht (binnen Access-mogelijkheden!) nog andere mogelijkheden zijn die ik over het hoofd zie.

Als ik er aan toe ben zal ik hier wat meer info plaatsen

[ Voor 5% gewijzigd door Stefke op 12-03-2013 13:54 ]

[D-Raven]

Ik raad je sterk aan om andere oplossingen na te jagen dan het EAV model. Het lijkt perfect te passen voor je probleem. Maar het levert zoveel gezeik op in the long term. Het nadeel van het EAV model, wat ROBIII ook al aanhaalde is dat je grotendeels je type safety kwijtraakt op je velden, dus alle ingebouwde mogelijkheden om op datums te zoeken, te vergelijken met decimalen e.d., raak je kwijt.. EAV is een beetje de poor mans no-sql oplossing, je hebt een klein beetje van de flexibiliteit, plus een hele hoop frustratie..

Wat wil je bereiken met je database? je wilt de data erin hebben zitten, en het liefst in een model welke matchds met je eigen mentale model, of beter gezegd, met het model waarin de applicatie ermee moet werken.
Zover uit jou posts afgeleid kan worden gaat je dit niet lukken met een standaard sql database. Volgens mij wat je wilt is een data structuur welke icm een simpel algoritme aan de hand van de eigenschappen van een pijpleiding (om even in het voorbeeld te blijven) jou kan vertellen hoe lang de installatie duurt. Bedenk hoe dit model eruit moet zien? onafhankelijk van of je het in sql opslaat of niet, bedenk gewoon, puur, de meest ideale datavorm. Hiervan identificeer je vervolgens wat hem uniek identificeerbaar maakt, zodat je hem ook nog n keer kan terughalen (einige simplificatie), en dat gaat je database in.

Er zijn diverse noSql oplossingen beschikbaar voor diverse platformen. Sommige zijn tot op zekere hoogte gratis, en de meeste zijn nog stompzinnig eenvoudig te gebruiken ook.
Kan me best voorstellen dat je zegt: Ik heb niet genoeg ervaring met noSQL om hier nu, voor dit project, mee aan de slag te gaan. Maar ga het dan op zn minst onderzoeken, maak n klein test projectje.
Een goed technologisch vooronderzoek is vaak de basis voor ieder serieus project, en zou dus geen probleem moeten zijn.. imho...