Filter search - best practice

[Steven]

Goededag,

Ik ben wat mogelijkheden aan het verkennen voor een applicatie, en een belangrijk onderdeel ervan is een filter functie. Je kent het wel, je krijgt eerst alle vakanties te zien. Dan kan je zeggen laat alleen Italie zien, daarna laat alleen vakantie tussen de 500-700 euro zien enz. enz.

Nu kan ik dat heel mooi en fancy zelf programmeren; maar ik vind het zo'n standaard probleem dat ik met niet kan voorstellen dat er geen best practice voor is. Dit gaan gebeuren in PHP icm MSSQL, maar opzich zou dat verder niet uit hoeven maken. Het gaat meer om het concept, dus een Java voorbeeld is ook goed. Op dit moment heb ik het volgende:

1. Er is een datamodel met een hele grote {productID, keyID, value} tabel. Hierin sla je bijvoorbeeld op {Italie, prijs, 530}. (Waarbij Italie en prijs stiekem verwijzingen zijn naar de ID's die daarbij horen). 80% van de keys hoeft nooit op gefilterd te worden.
2. De keys waarop gefilterd moet worden moeten configureerbaar zijn. Echter is dit een handmatig proces.
3. De database wordt slecht 1x/nacht geupdate

Mijn suggestie is nu om over deze tabellen een VIEW heen te leggen, waardoor er een virtuele tabel bestaat bestaande uit (bijvoorbeeld): {productID, prijs, land, aantal-dagen} Voordeel is dat deze virtuele tabel automatisch geupdate wordt bij aanpasseingen. Als je hier de juiste indexes op zet zou je heel makkelijk kunnen zeggen:

SELECT productID FROM filterTable WHERE prijs > 500 AND prijs < 700 AND land = Italie.


Dus eigenlijk twee vragen:
1. Kent iemand een best practice/framework/methode voor dit vraagstuk?
2. Ziet iemand problemen/verbeteringen bij bovenstaande aanpak.

[.oisyn]

Waarom zou je je datamodel zo ver doornormalizeren, ipv dat wat je voorstelt als view gewoon direct als tabel te gebruiken? Het enige wat je verder nog moet configureren (voor je applicatie, dus dat hoeft verder ook niet per se in de database zelf) is welke velden een tabel heeft en hoe je daarop kunt filteren. Ik zie het hele nut van die overnormalisatie eigenlijk niet.

[Steven]

Er moeten meerdere soorten datamodellen in gerepresenteerd worden waarbij de kolommen dagelijks kunnen veranderen. En om nou data-tabellen te hebben die elke nacht kunnen veranderen lijkt me verre van ideaal, zeker omdat dat betekent dat er tabellen komen van 200+ kolommen...

[.oisyn]

Steven schreef op donderdag 08 oktober 2009 @ 17:56:
Er moeten meerdere soorten datamodellen in gerepresenteerd worden waarbij de kolommen dagelijks kunnen veranderen.

Ik vind dat maar een rare eis eerlijk gezegd. Waarom zou zo'n tabel dagelijks veranderen?

[Steven]

.oisyn schreef op donderdag 08 oktober 2009 @ 17:58:
[...]

Ik vind dat maar een rare eis eerlijk gezegd. Waarom zou zo'n tabel dagelijks veranderen?

Data wordt geimporteerd en je weet nooit wat er aangeleverd wordt; kan bij elke productgroep weer anders zijn. Sterker nog, de database definitie wordt gewoon meegestuurd.

[.oisyn]

Dat is geen antwoord op mijn vraag. Een tabel in mijn eigen database kan ook iedere seconde veranderen. Ik hoef alleen maar de admin te openen. Mijn vraag ging natuurlijk over waarom hij zou veranderen. Waarom zouden ze elke dag een ander model aanleveren?

[Steven]

Er zijn hier meerdere soorten datamodellen. Stel het zijn er 20, en elk bestaat uit 100 kolommen. Stel dat er per bestand 5x/jaar een kolom bij of afgaat. Dat zijn er 100 per jaar, is gemiddeld elke 3 dagen een wijziging. En daarnaast zijn allemaal tabellen met 100 kolommen nou niet bepaald practisch, en dynamisch tabellen wijzigen lijkt me ook niet echt een succes?

PS: En dus terugkomend op het waarom; de wereld veranderd en dus ook de beschrijving van de producten

[ Voor 12% gewijzigd door Steven op 08-10-2009 18:09 ]

[NMe]

Een tabel met 100+ kolommen die ook nog eens met enige regelmaat veranderen is met aan zekerheid grenzende waarschijnlijkheid in de eerste plaats rampzalig slecht gemodelleerd. Het hele idee achter een database is juist dat de structuur statisch is en alleen de data wijzigt...

Dit soort ontwerpen leveren alleen maar extra problemen op, en je huidige systeem is daar een perfect voorbeeld van: in elk normaal genormaliseerd ontwerp had je de oplossing van .oisyn direct kunnen gebruiken.

[ Voor 28% gewijzigd door NMe op 08-10-2009 18:16 ]

[RobIII]

Ah, het bekende "database-in-een-database" probleem
Misschien moet je eens kijken naar HBase, BigTable, HyperTable en consorten. Misschien dat je daar wat aan hebt. De weg die je nu ingeslagen bent is, mark my words, doodlopend. Bedenk eens welk type je "value" gaat geven. Dit wordt geheid een string (want anders past niet "alles" er in). En bedenk dan eens hoe je selecties gaat maken als "alles met meer dan 2 of meer badkamers" of "alles met reisdatum na 15-12-2009"

[ Voor 52% gewijzigd door RobIII op 08-10-2009 18:28 ]

[.oisyn]

Steven schreef op donderdag 08 oktober 2009 @ 18:08:
Er zijn hier meerdere soorten datamodellen. Stel het zijn er 20, en elk bestaat uit 100 kolommen. Stel dat er per bestand 5x/jaar een kolom bij of afgaat. Dat zijn er 100 per jaar, is gemiddeld elke 3 dagen een wijziging.

Het is nog altijd 5x per jaar een wijziging per tabel. Bovendien hebben zogenaamd hypothetische gevallen geen betekenis. Het gaat om de praktijk. Heb je onderzoek gedaan naar hoe vaak zo'n wijziging wordt doorgevoerd?

Maar als ik het goed begrijp heb je voor jezelf al besloten dat dit de beste keuze is en wil je hier alleen nog een bevestiging horen van je idee?

RobIII schreef op donderdag 08 oktober 2009 @ 18:23:
En bedenk dan eens hoe je selecties gaat maken als "alles met meer dan 2 of meer badkamers" of "alles met reisdatum na 15-12-2009"

Eigenlijk is dat best simpel met inner joins
.edit: oh wacht, ik dacht even aan kolommen als tabellen. Maar alle kolommen van alle tabellen staan natuurlijk ook nog eens in dezelfde tabel gecodeerd . Daar is nog wel uit te komen maar dat wordt echt harig. Áls de kolommen nou echt zo dynamisch zouden zijn, dan zou ik daar wel gewoon tabellen van maken en zo linken aan de 'hoofdtabel', dan kunnen ze ook beter getypeerd worden.

En dan krijg je

SQL:

SELECT t.*
FROM travels t
INNER JOIN num_bathrooms b ON t.travel_id = b.travel_id
INNER JOIN traveldates d ON t.travel_id = d.travel_id
WHERE
    b.num >= 2 AND 
    d.traveldate > '15-12-2009'

[ Voor 39% gewijzigd door .oisyn op 08-10-2009 22:11 ]

[Steven]

RobIII schreef op donderdag 08 oktober 2009 @ 18:23:
Ah, het bekende "database-in-een-database" probleem

OKOK, jullie hebben gelijk. Ben misschien iets te stellig. En heb sommige voorbeelden hierboven ook een beetje versimpelt aangezien er eigenlijk ook nog aparte tabellen zouden zijn voor nummers, strings en datums. Eigenlijk is dit een wat rudimentairer probleem waar ik heel vaak tegen aanloop. Niet bij alles is je database structuur van te voren duidelijk, en uiteindelijk resulteert het in een database in een database. Is dit eigenlijk fout? Mijn boekenkast zegt er helaas weinig over

Maar goed er wordt dus gesuggereerd om de oplossing van .oisyn te gebruiken en het 'gewoon' in een brede database te zetten. Laten we het 'probleem' van het updaten even naast ons liggen dan botst dit met:

NMe schreef op donderdag 08 oktober 2009 @ 18:12:
Een tabel met 100+ kolommen die ook nog eens met enige regelmaat veranderen is met aan zekerheid grenzende waarschijnlijkheid in de eerste plaats rampzalig slecht gemodelleerd. Het hele idee achter een database is juist dat de structuur statisch is en alleen de data wijzigt...

Dit soort ontwerpen leveren alleen maar extra problemen op, en je huidige systeem is daar een perfect voorbeeld van: in elk normaal genormaliseerd ontwerp had je de oplossing van .oisyn direct kunnen gebruiken.

Er zijn gewoon 100 values bij een product en die hebben echt allemaal een 1:1 relatie en als je dat normaliseert dan krijg je uiteindelijk toch gewoon een brede tabel waarvan NMe aangeeft dat dat niet wenselijk is (en terecht). Dan is dat in dit geval toch geen oplossing? En dan heb ik net het probleem van het onverwachts aanpassen van het datamodel nog naast me neergelegd.


Ik heb zelf ook nog meer research gedaan; en de door mij voorgestelde oplossing met views is niet echt houdbaar. Views kunnen eigenlijk geen keys bevatten en daarnaast doen die dingen achter de schermen de query gewoon telkens opnieuw zolang hij niet in de querycache zit. Het is een soort makkelijke manier om grote subqueries te voorkomen. Een tabel soort die een permane


Dus wat ik in de discussie zou willen gooien:
1. Die Database in a database-paradox; kent iemand daar meer informatie over? Het Googled namelijk nogal rot
2. Hoe erg zijn extreem brede tabellen nu eigenlijk?
3. Hoe kan het, dat er voor dit probleem nergens literatuur te vinden is. Zoek ik misschien op de verkeerde term?

[RedRose]

Nu kan ik dat heel mooi en fancy zelf programmeren; maar ik vind het zo'n standaard probleem dat ik met niet kan voorstellen dat er geen best practice voor is. Dit gaan gebeuren in PHP icm MSSQL, maar opzich zou dat verder niet uit hoeven maken. Het gaat meer om het concept, dus een Java voorbeeld is ook goed. Op dit moment heb ik het volgende

Het is geen standaard probleem. Het is juist een heel specifiek probleem. De tussenstap tussen de data die jij binnenkrijgt en moet converteren naar een normaal database model, of een model waarmee je makkelijk kan filteren, die is compleet custom.

Dus ik zou mezelf ten eerste afvragen hoe de data binnenkomt en hoeveel verandering daar in zit. Vervolgens moet je gaan kijken hoe je dat kan converteren naar jouw db model. Dus niet van: 'Ik heb bijvoorbeeld een land, met een prijs en een bedrag', maar je moet juist in die tussenlaag een abstractie maken om die conversie altijd goed te kunnen doen. En dan pas zou ik gaan programmeren en / of rare dingen doen met je database.

[RobIII]

.oisyn schreef op donderdag 08 oktober 2009 @ 18:55:

Eigenlijk is dat best simpel met inner joins
.edit: oh wacht, ik dacht even aan kolommen als tabellen. Maar alle kolommen van alle tabellen staan natuurlijk ook nog eens in dezelfde tabel gecodeerd . Daar is nog wel uit te komen maar dat wordt echt harig.

Dit heb ik echt al honderden keren zien gebeuren en ze zijn allemaal, stuk voor stuk, geen enkele uitzondering daargelaten op hun bek gegaan en al brandend de diepte in gezonken. Ja, met wat creatief query-en en wat creatief programmeren kom je een héél eind. Totdat de performance je de nek om draait (alles is "string"), of de wat "ingewikkeldere" queries (waarbij ingewikkeld in deze context nog peanuts is want een 2 voorwaardes in een WHERE clause wordt al lastig) draaien je de nek om. En anders komen de constraints die je normaliter zou hebben je in je hol schoppen omdat je ze nu niet meer kunt gebruiken, en op geen enkele manier wat dan ook nog kunt afdwingen. En als je dat allemaal weet te voorkomen komt op een goede dag je DB zélf je een rotschop in je muil verkopen, out of the blue, gewoon... om je te jennen Harig is nog optimistisch zeg maar

[ Voor 9% gewijzigd door RobIII op 09-10-2009 19:27 ]

[kasper_vk]

Steven schreef op vrijdag 09 oktober 2009 @ 16:50:
[...]
Dus wat ik in de discussie zou willen gooien:
1. Die Database in a database-paradox; kent iemand daar meer informatie over? Het Googled namelijk nogal rot
2. Hoe erg zijn extreem brede tabellen nu eigenlijk?
3. Hoe kan het, dat er voor dit probleem nergens literatuur te vinden is. Zoek ik misschien op de verkeerde term?

1. Google eens op 'database design key value'
2. Niet heel erg per sé denk ik, maar wat databases betreft geldt altijd: hoe kleiner je data, hoe beter / sneller. En héél veel kolommen is wellicht wat lastiger heel klein te krijgen/houden.
Zoek vooral de documentatie over jouw specifieke DBMS eens door. Daar zullen ongetwijfeld limieten over aantallen kolommen en aanwijzingen in staan m.b.t. veel kolommen in een tabel.
3. Ja dus Zie 1.

[RikTW]

Ik ben sinds kort met eenzelfde soort probleem bezig: ik heb een bestaande tabel met daarin data opgeslagen in key-value pairs. Het terughalen van data uit deze tabel is (zoals verwacht ) behoorlijk lastig, dus ik was aan het kijken om op een makkelijke manier een platte tabelstructuur te extraheren uit de KVP tabel, d.m.v. een view.

Steven schreef op donderdag 08 oktober 2009 @ 17:42:
...
Mijn suggestie is nu om over deze tabellen een VIEW heen te leggen, waardoor er een virtuele tabel bestaat bestaande uit (bijvoorbeeld): {productID, prijs, land, aantal-dagen} bovenstaande aanpak.
...

Hoe zou je zo'n view kunnen maken dan? Bestaat hier een handige manier voor? (Ik ben niet zo'n SQL held )

[Verwijderd]

Voor dit type filters lijkt me een datawarehouse geschikt.
Onwijs veel info ook over te vinden

[MMUilwijk]

Verwijderd schreef op donderdag 15 oktober 2009 @ 16:56:
Voor dit type filters lijkt me een datawarehouse geschikt.
Onwijs veel info ook over te vinden

Waarom wil je hiervoor een Data Warehouse optuigen? Dat lijkt me nog al overkill. Of bedoel je de toepassing van een star / snowflake model om de data in op te slaan. Daar kan ik mij nog iets bij voorstellen. Het probleem van de wijzigende datastructuur ga je daar ook niet mee oplossen, sterker nog, je haalt je alleen maar meer problemen op je hals, gezien het feit dat je ETL dan ook dusdanig flexibel moet worden.

[D-Raven]

RikTW schreef op zaterdag 10 oktober 2009 @ 21:13:
Ik ben sinds kort met eenzelfde soort probleem bezig: ik heb een bestaande tabel met daarin data opgeslagen in key-value pairs. Het terughalen van data uit deze tabel is (zoals verwacht ) behoorlijk lastig, dus ik was aan het kijken om op een makkelijke manier een platte tabelstructuur te extraheren uit de KVP tabel, d.m.v. een view.


[...]

Hoe zou je zo'n view kunnen maken dan? Bestaat hier een handige manier voor? (Ik ben niet zo'n SQL held )

Ga eens kijken naar pivoteer query's

[RikTW]

D-Raven schreef op vrijdag 16 oktober 2009 @ 09:44:
[...]


Ga eens kijken naar pivoteer query's

Thanks, dit lijkt inderdaad behoorlijk in de buurt te komen van wat ik wil. Ik heb een paar voorbeelden bekeken, het vervelende is alleen dat je vantevoren de uiteindelijke "kolommen" (die uit de "keys" moeten komen) moet kennen. Met een stukje code (pl/sql, transactSQL, ..) moet het wel lukken waarschijnlijk, maar een query waar dit in lukt ben ik nog niet tegengekomen.