[MySQL] Opslaan en ophalen van data met afwijkende stuctuur

[Verwijderd]

Ik ben momenteel bezig aan applicatie in PHP / MySQL waarbij er grote hoeveelheden documenten opgeslagen worden die, afhankelijk van het type document, een afwijkende structuur hebben.

Deze documenten kunnen door de gebruiker gefilterd / doorzocht worden en op basis hiervan worden er allerlei metrics en views gegenereerd.

De hoofd table bevat alle basisvelden die standaard zijn voor ieder document en per documenttype is er een extra table met een foreign key verwijzing naar de hoofdtable en de specifieke data voor dat
document type. Op het moment kent het systeem 5 verschillende types maar dit kan makkelijk oplopen tot 10 of meer.

voorbeeld :

table : document
-----------------------------------------------------------------------------------------------------------------
| document_id | documenttitle | documenttype_id | publishdate | documentcontent | author |
-----------------------------------------------------------------------------------------------------------------

table : document_twitter
--------------------------------------------------------------------------
| document_id | username | strid | touser | profileimg | geo |
--------------------------------------------------------------------------

Bij het querien van 1 document (of meerdere documenten van1 type) met alle specifieke gegevens en met eventuele condities en joins op andere tabellen is dit een makkelijk en efficient.

Nu ontstaat het probleem als ik grotere datasets wil querien met documenten van meerdere types waarbij alle specifieke data ook geladen moet worden, vaak afhankelijk van condities die ook betrekking kunnen hebben op andere tabellen die gejoined kunnen worden.

Op basis van de huidlge structuur heb ik zelf al een paar oplossingen bedacht, die allemaal hun pro's en cons hebben :

1 ) per mogelijk document type een LEFT JOIN toevoegen :

+ 1 redelijk simpele query die makkelijk te genereren is
- veel overhead aan columns met null values
- queries worden complexer en trager naarmate er meer types zijn.

2) Een query doen op de hoofdtable, uit de resulterende dataset de document_id's per type opslaan in een array en vervolgens per type de bijhordende rows uit de specifieke tabellen querien met een WHERE document_id IN condition :

+ geen overbodige columns;
- afhankelijk van de hoeveelheid types, meer queries
- meer parsen

3) per type een query uitvoeren met 1 join op de table die de specifiele data bevat

+ geen overhead aan data / columns
- meer queries
- meer parsen

Eigenlijk vind ik alle drie deze oplossingen niet echt mooi of efficient en gezien de hoeveelheid data en de hoeveelheid verschillende views / metrics die gegenereerd worden per scherm (soort dashboard achtige omgeving) met daarbovenop een aantal filtermogelijkheden.

Ik heb ook al gedacht aan een structuur waarbij alle specifieke data als serialized array/object in een column of losse table gepropt wordt, wat niet mijn voorkeur heeft omdat je dan condities op die specifieke data niet mogelijk is.

Ook heb ik gedacht om over te stappen naar MongoDb of een andere document based database maar dit zou toch aardig wat refactoring betekenen en omdat ik hier nog niet zoveel kennis van heb ook heel wat tijd kosten om me hier goed in te verdiepen.


Dus ... mijn vragen zijn eigenlijk :

- Hoe zouden jullie dit oplossen ?

- Zie ik mogelijkheden over het hoofd ?

[Freeaqingme]

Waarom gebruik je specifiek voor deze entiteiten mysql?

[Verwijderd]

De keuze voor MySql komt voornamelijk omdat de basis applicatie al op mysql draait en omdat ik hier zelf ook de meeste ervaring mee heb.

Maar in weze had dit iedere andere relationele database engine kunnen zijn eigenlijk.

[rnpjansen]

Voor de situatie die je hier beschrijft is eigenlijk geen oplossing mogelijk met relationele databases.
In een soortgelijke situatie zou ik eerst onderzoeken waarom dat die verschillende documenttypen er zijn en kunnen ze niet samengevoegd worden in één tabel zijn alle velden van belang??? Staat er geen overbodige data bij.

Je moet namelijk op gaan passen dat jezelf uiteindelijk niet nog meer in de problemen werkt, want voor je het weet zit je met 100 verschillende documenttypen die wil raadplegen.

[Verwijderd]

@Rnpjansen

Dat er geen 'holy-grail' oplossing met relationele databases is had ik al wel door, ergens zal er een consessie gemaakt moeten worden tussen performance, complexiteit, data normalisatie, mogelijkheden van het systeem etc

Die verschillende types zijn er nu eenmaal ... zie het als een shop met verschillende producttypes waarbij ieder type bepaalde specifieke eigenschappen heeft en een aantal eigenschappen die gemeenschappelijk zijn.

100 types zal niet zo snel voorkomen maar ik sluit het niet uit dat er 10 tot 20 moelijk zullen zijn.

het 'gevaar' van 1 tabel met alle velden is dat dit moeilijk te schalen is en gezien niet alle klanten (die eigen DB's krijgen) dezelfde types gebruiken krijg je dan een wildgroei aan columns.

Er zal vast data bij zitten die minder gebruikt zal worden maar echt overbodig zal deze niet zijn omdat de applicatie alleen maar blijft groeien qua mogelijkheden.

[RobIII]

* RobIII dropt voorzichtig de NoSQL bom

[ Voor 5% gewijzigd door RobIII op 05-06-2012 22:57 ]

[Freeaqingme]

RobIII schreef op dinsdag 05 juni 2012 @ 22:57:
* RobIII dropt voorzichtig de NoSQL bom

Ik vond dat ik dat al heel voorzichtig deed

100 types zal niet zo snel voorkomen maar ik sluit het niet uit dat er 10 tot 20 moelijk zullen zijn.

Dergelijke uitspraken ken ik. In den beginne waren het er 3, die konden nog wel worden gehardcode. Daarna 10, 30, 50, 100, 1000, en uiteindelijk waren er tienduizenden types/eigenschappen/permutaties. Uiteindelijk bestaat er niet zoiets als een 'fixed number'; in de praktijk zullen het er op den duur altijd meer blijken te worden.

offtopic:
Oke, zeer specifieke usecases daar gelaten. Wie hier kan er toekomstvoorspellen om met 100% zekerheid te zeggen dat dit hier niet het geval is?

Als je nu uitgaat van 20 a 30 dan zou een RDBMS met 30 aan elkaar gejoinde en ge-unionde tabellen wellicht nog volstaan. bij 100 lijkt dat volledig onwerkbaar. Hoewel 30 nu realistisch lijkt zou ik eerder inzetten op 3000 (altijd 2 nullen groter dan je oorspronkelijk dacht. Hier bestaat een hele goede blogpost over, maar heb geen idee waar ik die terug kan vinden).

Als je het vervolgens over zoveel verschillen hebt, lijkt inderdaad een nosql oplossing praktischer dan een typische rdbms oplossing. Waar je met een RDBMS op afstevent in een scenario als deze is [url=//en.wikipedia.org/wiki/Entity-attribute-value_model]EAV[/url]. EAV is bad, wellicht de meest voorname reden hiervoor is dat je geen indexen kan gebruiken; en dat is waar een RDBMS op staat - of valt.

In dat opzicht is dit verhaal (evenals google acties op EAV) wellicht interessant leeswerk: http://stackoverflow.com/...ros-cons-and-alternatives

[ Voor 16% gewijzigd door Freeaqingme op 05-06-2012 23:26 ]

[Verwijderd]

@RobIII

Ik heb er zelf ook al aan zitten denken ... het heet niet voor niets 'document based' storage En lijkt ergens wel een holy grail oplossing te zijn voor dit probleem. Meeste queries worden al gegenereerd door een losse abstractielaag dus het omzetten van ansi-sql naar mongo-sql zal niet heel moeilijk gaan.

Ik zat zelf te denken aan een (tijdelijke) hybrid oplossing waarbij eigenlijk alles nog steeds in het oude datamodel opgeslagen wordt en daarna gerepliceerd wordt naar mongodb,

dit is niet ideaal maar zorgt er wel voor dat de bestaande backend nog even kan draaien zoals deze is en ik ondertussen het user/klanten gedeelte aan kan passen aan de nieuwe omgeving .... dit is immers het gedeelte waar de bottleneck ligt

een eventuele andere oplossing zou kunnen zijn :
http://dev.mysql.com/doc/...tml#function_extractvalue

Niet mij voorkeur gezien ik een fan ben van XML.

@Freeaqingme

Dat aantal types is wel op echte usecases gebasseerd, echter ben ik zelf ook van mening dat 5 joins /unions al best veel is laat staan 10 tot 20.

Verder moet ik nog even alle schetsen ,schema's doorlopen om te zien of het essentieel is om conditioneel op die extended data te kunnen querien ... als het puur voor weergave is dan zou het eventueel lukken met een (wat minder fraaie maar nu even snellere) oplossing met serialized data

edit : EAV is in mijn ogen zeker niet de oplossing en eigenlijk is ook niet echt aan de orde in dit geval omdat ieder type wel een vast aantal attributes heeft (wat in de realiteit natuurlijk betekent dat er wel wijzigingen kunnen komen maar niet heel veel )

[ Voor 8% gewijzigd door Verwijderd op 05-06-2012 23:45 . Reden: aanvulling ]