Gemengde datatypen en consistentie

[HarmenM]

Op dit moment heb ik in een webapplicatie verschillende contenttypen (artikelen, stroomdiagrammen, etc.). Elk contenttype heeft een eigen tabel, omdat er naast de overlap (titel, een eigen ordering, auteurId, categoryId) grote verschillen zijn. Nu wil ik toe naar een situatie waarin deze verschillende content typen gemengd kunnen worden met elkaar.

Dit zou conceptueel de meest ideale situatie zijn:

              -----------------
              | Content       |
              |  - entryId    |
              |  - title      |
              |  - categoryId |
              |  - ordering   |
              |               |
              |  - contentType|
              |  - itemId     |
              -----------------
                      |
      -----------------------------------
      |               |                 |
-------------  --------------  ------------------
| Articles  |  | Flowcharts |  | Questionnaires |
|  - itemId |  |  - itemId  |  |  - itemId      |
 -------------  -------------  ------------------

Nu is dit niet zo heel moeilijk in de business laag te verwerken, maar ik zit te zoeken naar een manier om dit ook consistent en integer te houden in een RDBMS, zoals MySQL.
Een foreign key op itemId zou een gedachte zijn, maar de data is in één van de drie content tabellen aanwezig, en niet in allemaal. Triggers gebruiken als gate keepers om de data sane te houden heb ik ook overwogen, maar is in het specifieke geval van MySQL lastig, omdat het niet nog niet mogelijk is om errors/execepties te throwen (signal/resignal zit in de nog niet gereleasede 5.5).

Even los het specifieke type database, zijn er ideeën hoe dit aan te pakken, hoe zouden jullie dit doen? Is het mogelijk om de data op databaseniveau consistent en integer te houden, of is dit typisch iets wat in de businesslaag moet worden geregeld?

[muksie]

Wat je wilt heet inheritance / overerving. De meeste RDBMS systemen hebben hier geen native ondersteuning voor. Je kunt het wel zelf onder brengen, daar zijn verschillende manieren voor. Zie bijvoorbeeld hier voor de verschillende mogelijkheden.

Overigens zou je met een aparte constraint ervoor kunnen zorgen dat er altijd maar een foreign key naar ieder content item verwijst.

[RobIII]

Verschillende entiteiten, verschillende tabellen. Simple as that. Waarom je het in 1 tabel zou willen proppen is beyond me (behalve over-normalisering).

Een boek-titel is heel iets anders dan een nieuwsartikel-titel. Het is allebei een titel maar daar houdt het op.

[ Voor 27% gewijzigd door RobIII op 03-09-2010 10:29 ]

[HarmenM]

Het gaat mij in deze situatie specifiek hoe dit op te lossen op database niveau. In de businesslaag is alles prima te verwerken en aan elkaar te relateren. Maar, er komt een moment dat het de database in gaat en dan zal het in een RDBMS gescheiden worden opgeslagen.

Er ligt een conceptuele relatie, omdat de records in de content-tabel verwijzen naar een record in één van de mogelijke specifieke tabellen. Maar, hoe krijg je deze conceptuele relatie gegoten naar een technische relatie, zoals een constraint of een validatie.

Het is mogelijk om per contenttype een foreign key te leggen naar itemId, maar dit legt mijns inziens de relatie de verkeerde kant op. Andersom de relatie leggen is in ieder geval in MySQL niet mogelijk (mijn testopstelling), omdat er niet meerdere foreign keys op één kolom kunnen liggen. Logisch, want een foreign key houdt geen rekening met de conditie van het content type.

@RobIII:
Ik wil het niet in één tabel proppen, dat zou bijzonder onlogisch en onwenselijk zijn. Het zou echter wel wenselijk zijn om deze verschillende content typen als één bron te beschouwen, waardoor het mogelijk is om lijsten te creëren met deze verschillende content typen in één lijst. Zoals het ook in een bestandssysteem is: tekstdocumenten en plaatjes kunnen in één directory staan, ook al zijn het verschillende typen data.

Ik zoek nu naar een benadering van dit principe: de datastructuren zijn verschillend en losstaand van elkaar, maar ze moeten wel in lijsten met elkaar geïntegreerd worden. Businesswise geen probleem, maar voor mij wel een uitdaging om op dataniveau consistentie te kunnen garanderen.

[ Voor 28% gewijzigd door HarmenM op 03-09-2010 10:37 ]

[mulder]

Je zult bij object dan de Id en de Type moeten opslaan ipv alleen het Id.

[JaQ]

HarmenM schreef op vrijdag 03 september 2010 @ 09:47:
Nu is dit niet zo heel moeilijk in de business laag te verwerken, maar ik zit te zoeken naar een manier om dit ook consistent en integer te houden in een RDBMS, zoals MySQL.
Een foreign key op itemId zou een gedachte zijn, maar de data is in één van de drie content tabellen aanwezig, en niet in allemaal. Triggers gebruiken als gate keepers om de data sane te houden heb ik ook overwogen, maar is in het specifieke geval van MySQL lastig, omdat het niet nog niet mogelijk is om errors/execepties te throwen (signal/resignal zit in de nog niet gereleasede 5.5).

Even los het specifieke type database, zijn er ideeën hoe dit aan te pakken, hoe zouden jullie dit doen? Is het mogelijk om de data op databaseniveau consistent en integer te houden, of is dit typisch iets wat in de businesslaag moet worden geregeld?

Dit gaat je niet lukken in een database zonder fatsoenlijke procedurele taal.

[HarmenM]

JaQ schreef op vrijdag 03 september 2010 @ 10:54:
[...]

Dit gaat je niet lukken in een database zonder fatsoenlijke procedurele taal.

Dat er een gedeelte logic achter gaat hangen dat is natuurlijk geen probleem. Waar ik echter bang voor ben is inconsistentie van de data. Een goed datamodel kan dienen als achtervang om data consistent te houden. Vandaar dit topic met de vraag in welke mate er mogelijkheden zijn om naast de business logic een onderliggend datamodel te creëren die faciliteert in de integriteit van de data.

[RobIII]

HarmenM schreef op vrijdag 03 september 2010 @ 10:29:
waardoor het mogelijk is om lijsten te creëren met deze verschillende content typen in één lijst. Zoals het ook in een bestandssysteem is: tekstdocumenten en plaatjes kunnen in één directory staan, ook al zijn het verschillende typen data.

SQL:

Select title from book
Union
Select title from news

En als je helemaal uit je pan wil gaan maak je een view.

Maar vertel eens waarom je dit per sé in 1 tabel wil hebben?
_{Met wat triggers, constraints en wat andere foefjes kom je een heel eind; ik zeg ook niet dat 't onmogelijk is. Maar de kans is groot dat je dit helemaal niet wil}

[ Voor 26% gewijzigd door RobIII op 03-09-2010 11:17 ]

[SPee]

Ik ga met Mulder mee: 1 tabel met Id/type (evt. met zijn eigen PK)
Dan kun je daar naartoe verwijzen.

Maar eerst zou ik kijken of ik in de originele tabel niet 2/3 kolommen kan toevoegen die naar de verschillende andere tabellen verwijst. (Dus jouw ene FK opdelen in 3)
Dat scheelt weer een 'onnodige' tabel, waarmee je het moeilijker overzichtelijk maakt.

[HarmenM]

@RobIII waarom ik hier een oplossing voor zoek? Ik werk hier aan een webapplicatie die van één content item gegroeid is naar meerdere content items. Elk type heeft een eigen content manager, eigen lijstjes, eigen ordering, maar alles hangt wel in dezelfde set categorieën.
Het idee is dat er een content manager moet komen die een geïntegreerd geheel biedt. De gebruikers van de webapplicatie kunnen dan content toevoegen en gemixte lijstjes maken met diverse soorten content. Voorbeeld:

Handboek Kwaliteitsnormen

1. Stroomdiagram 1
2. Artikel 1
3. Artikel 2
4. Vragenlijst 1
5. Stroomdiagram 2

De scheiding die nu in de applicatie voelt voor de eindgebruiker artificieel, omdat hij zich afvraagt waarom de content items niet gemengd kunnen worden. Daarom wil ik dit probleem opheffen, omdat de content conceptueel als één geheel te beschouwen is. Technisch gezien echter ben ik me er zeer van bewust dat het één het ander niet is en zeker niet zonder meer één geheel is.
In de business logic is dit alles te realiseren, zonder enig probleem. Maar, ik zou graag niet willen inboeten in integriteit rondom het datamodel, omdat daarmee de kracht van een RDBMS niet wordt benut.

Vandaar deze topic, om eens te vragen hoe jullie tegen deze situatie aankijken...

[JaQ]

HarmenM schreef op vrijdag 03 september 2010 @ 10:59:
Dat er een gedeelte logic achter gaat hangen dat is natuurlijk geen probleem. Waar ik echter bang voor ben is inconsistentie van de data. Een goed datamodel kan dienen als achtervang om data consistent te houden. Vandaar dit topic met de vraag in welke mate er mogelijkheden zijn om naast de business logic een onderliggend datamodel te creëren die faciliteert in de integriteit van de data.

Als je een dergelijk datamodel consistent wilt houden, dan kan je niet anders dan met een procedure taal werken (oftewel: check constraints). MySQL is daar simpelweg niet toereikend in.

alhoewel: ik lees wel wat ranzigheid waarbij mensen met een trigger een check uitvoeren en bij een invalid waarde een divide by zero error veroorzaken

Overigens zou ik nogmaals goed na gaan denken over het datamodel dat je nu wilt maken. Weet je zeker dat je op deze manier je functionele vraag wilt oplossen?

[cariolive23]

HarmenM schreef op vrijdag 03 september 2010 @ 11:32:
De scheiding die nu in de applicatie voelt voor de eindgebruiker artificieel, omdat hij zich afvraagt waarom de content items niet gemengd kunnen worden.

Dat is een stuk presentatie, dat heeft helemaal niets maar dan ook niets met het datamodel te maken. Gebruikers zitten niet rechtstreeks in de database te roeren, hebben daar niets te zoeken.

Dus waarom een goed datamodel ineens een probleem is voor jouw eindgebruiker, geen idee. Zorg voor een goed bruikbare presentatie/interface en het probleem van de gebruiker is opgelost.

[mulder]

cariolive23 schreef op vrijdag 03 september 2010 @ 13:07:
[...]
Dat is een stuk presentatie, dat heeft helemaal niets maar dan ook niets met het datamodel te maken. Gebruikers zitten niet rechtstreeks in de database te roeren, hebben daar niets te zoeken.
[..]

Hoe voeg ik dan verschillende contenttypen aan een pagina/content area/whatever toe? Het is dus geen presentatie.

[RobIII]

mulder schreef op vrijdag 03 september 2010 @ 13:36:
[...]
Hoe voeg ik dan verschillende contenttypen aan een pagina/content area/whatever toe? Het is dus geen presentatie.

Even uitgaande van een "pagina" die verschillende contenttypen (zeg: producten, reviews en handboeken) bevat en je wil je referentiële integriteit willen garanderen:

code:

pagina
|
|
+-- id
+-- titel
+-- auteur
+-- ...
+-- ...


pagina_product   pagina_review    pagina_handboek
|                |                |
|                |                |
+-- pagina_id    +-- pagina_id    +-- pagina_id
+-- product_id   +-- review_id    +-- handboek_id
+-- position     +-- position     +-- position


product          review           handboek
|                |                |
|                |                |
+-- id           +-- id           +-- id
+-- titel        +-- titel        +-- titel
+-- prijs        +-- categorie    +-- inleiding
+-- ...          +-- ...          +-- ...
+-- ...          +-- ...          +-- ...

Met de juiste FK's naar de juiste tabellen (dus de pagina_id's naar pagina en de overige X_id's naar de corrosponderende X tabellen). Op die manier kun je referentiële integriteit garanderen. Wil je een wat minder ingewikkeld datamodel dan krijg je zoiets:

code:

pagina
|
|
+-- id
+-- titel
+-- auteur
+-- ...
+-- ...

pagina_items
|
|
+-- pagina_id
+-- type
+-- item_id
+-- positie
+-- ...
+-- ...

Waarbij item_id verwijst naar "een" ("willekeurige") tabel, afhankelijk van het type. En dan heb je dus in pagina_items geen FK's en dus kun je de referentiële integriteit niet garanderen. Either way maakt het aan de presentatiekant van je Business Objects geen ruk uit.

TS wil de consistentie (en doelt daarmee op referentiële integriteit) garanderen en dat kan alleen met FK's (of ranzige triggers e.d. en daarmee heb je alsnog geen 100% garantie).

[ Voor 9% gewijzigd door RobIII op 03-09-2010 14:46 ]

[netvor]

RobIII schreef op vrijdag 03 september 2010 @ 13:54:

code:

pagina
|
|
+-- id
+-- titel
+-- auteur
+-- ...
+-- ...


pagina_product   pagina_review    pagina_handboek
|                |                |
|                |                |
+-- pagina_id    +-- pagina_id    +-- pagina_id
+-- product_id   +-- review_id    +-- handboek_id
+-- position     +-- position     +-- position


product          review           handboek
|                |                |
|                |                |
+-- id           +-- id           +-- id
+-- titel        +-- titel        +-- titel
+-- prijs        +-- categorie    +-- inleiding
+-- ...          +-- ...          +-- ...
+-- ...          +-- ...          +-- ...

Hier is consistentie ook niet 100% gewaarborgd, aangezien de verschillende "position" kolommen niet aan elkaar gelinkt zijn. Zo is het dus mogelijk om bijvoorbeeld een artikel, een review én een handboek alledrie op position 42 op pagina 37 neer te zetten. Wat is dan de correcte volgorde?

Als de content-types bekend zijn en het er niet heel veel zijn dan zou je deze tussenvorm kunnen overwegen:

code:

pagina
|
|
+-- id
+-- titel
+-- auteur
+-- ...
+-- ...


pagina_items
|
|
+-- pagina_id
+-- product_id
+-- review_id
+-- handboek_id
+-- position


product          review           handboek
|                |                |
|                |                |
+-- id           +-- id           +-- id
+-- titel        +-- titel        +-- titel
+-- prijs        +-- categorie    +-- inleiding
+-- ...          +-- ...          +-- ...
+-- ...          +-- ...          +-- ...

Waarbij er in elke regel van pagina_items slechts één van {product_id, review_id, handboek_id} gevuld mag zijn, de andere twee zijn null. Dat kan je netjes afdwingen in een trigger op inserts/updates.

Maarja, als de content-types dynamisch van karakter zijn en er voortdurend nieuwe types bijkomen of wegvallen, dan wil je niet voortdurend ALTER TABLE gaan spelen om stoelendans te spelen met je FK kolommen...

[RobIII]

netvor schreef op vrijdag 03 september 2010 @ 14:34:
[...]


Hier is consistentie ook niet 100% gewaarborgd, aangezien de verschillende "position" kolommen niet aan elkaar gelinkt zijn. Zo is het dus mogelijk om bijvoorbeeld een artikel, een review én een handboek alledrie op position 42 op pagina 37 neer te zetten. Wat is dan de correcte volgorde?

SQL:

Select ....
...
Order by position, title, createdate, bloedgroep...

Dit heeft niets (of weinig) met referentiële integriteit te maken overigens (waar TS op doelt met "consistentie").

netvor schreef op vrijdag 03 september 2010 @ 14:34:
Waarbij er in elke regel van pagina_items slechts één van {product_id, review_id, handboek_id} gevuld mag zijn, de andere twee zijn null. Dat kan je netjes afdwingen in een trigger op inserts/updates.

Dat kan inderdaad ook, maar triggers zijn niet 100% betrouwbaar (als in: als ze om welke reden dan ook niet getriggerd worden boeit het na de insert niet meer en kan ik dus ook lekker 2 v/d 3 of 3 v/d 3 velden vullen). Ik weet niet hoe het bij MySQL precies zit, maar ik verwacht (bijv.) wel dat bij een import de triggers niet afgevuurd worden (om maar eens wat te roepen).

netvor schreef op vrijdag 03 september 2010 @ 14:34:
Maarja, als de content-types dynamisch van karakter zijn en er voortdurend nieuwe types bijkomen of wegvallen, dan wil je niet voortdurend ALTER TABLE gaan spelen om stoelendans te spelen met je FK kolommen...

Dat hou je (min-of-meer) toch. In jouw voorbeeld moet er een veld bij, in mijn voorbeeld niet maar moet je wel de BL aanpassen om een nieuw type af te handelen (wat overigens in jouw voorbeeld net zo goed moet). Linksom of rechtsom: aanpassen moet je toch. Als gebruikers zélf types (en hun eigenschappen (velden)) kunnen definiëren dan zou ik eerder eens gaan kijken naar zgn. NoSQL oplossingen.

[ Voor 65% gewijzigd door RobIII op 03-09-2010 14:49 ]

[netvor]

RobIII schreef op vrijdag 03 september 2010 @ 14:39:
Dit heeft niets (of weinig) met referentiële integriteit te maken overigens (waar TS op doelt met "consistentie").

True, desalniettemin maakt het je leven een stuk makkelijker als je bijvoorbeeld nummer 6 uit een lijst van 10 verwijdert en nummers 7-10 gaat omnummeren; slechts één enkele UPDATE.

Dat kan inderdaad ook, maar triggers zijn niet 100% betrouwbaar (als in: als ze om welke reden dan ook niet getriggerd worden boeit het na de insert niet meer en kan ik dus ook lekker 2 v/d 3 of 3 v/d 3 velden vullen). Ik weet niet hoe het bij MySQL precies zit, maar ik verwacht (bijv.) wel dat bij een import de triggers niet afgevuurd worden (om maar eens wat te roepen).

Als MySQL echt een hard requirement is dan kan je inderdaad maar ver van triggers wegblijven. Maar in vrijwel elke volwassen DBMS is een trigger toch wel 100% betrouwbaar mag ik hopen. Maargoed, ik heb alleen hands-on ervaring met Oracle triggers, dat is wel even een sprongetje vanaf MySQL.

Dat hou je (min-of-meer) toch. In jouw voorbeeld moet er een veld bij, in mijn voorbeeld niet maar moet je wel de BL aanpassen om een nieuw type af te handelen (wat overigens in jouw voorbeeld net zo goed moet). Linksom of rechtsom: aanpassen moet je toch. Als gebruikers zélf types (en hun eigenschappen (velden)) kunnen definiëren dan zou ik eerder eens gaan kijken naar zgn. NoSQL oplossingen.

LOL, NoSQL, daar zat ik zelf al aan te denken maar ik wilde liever geen advocaat van de duivel spelen en zo'n dergelijke troll loslaten.

[B-Man]

Op zich is dit niet zo moelijk, toch? Neem het triviale voorbeeld waarbij je zegt:

Entity (id, type*)
Company > Entity (id, name)
Person > Entity (id, name, company)
Note (id, note) heeft een n-op-1 relatie met Entity

*) of deze kolom nodig is, is afhankelijk van de manier waarop je gegevens uit je database haalt; Indien je met een ORM werkt kan het zijn dat deze kolom niet nodig is (Hibernate/Java bijvoorbeeld).

De n-op-1 relatie tussen Person en Company is simpelweg een FK naar Entity.id; In de business laag bewaak je dat er enkel een Company opgegeven kan worden.

Person.id en Company.id zijn dan FKs naar Entity.id;

Samengevat: wil je databaseconsistentie en meerdere tabellen, dan moeten de tabellen die naar subtypes (Company, Person) verwijzen gaan verwijzen naar het root type (Entity)

[RobIII]

B-Man schreef op vrijdag 03 september 2010 @ 14:53:
Op zich is dit niet zo moelijk, toch? Neem het triviale voorbeeld waarbij je zegt:

Waarna alles van 't type "Entity" is. Daarmee verleg je alleen het probleem. Maar het is, idd, ook een oplossing.

[B-Man]

RobIII schreef op vrijdag 03 september 2010 @ 15:00:
[...]

Waarna alles van 't type "Entity" is. Daarmee verleg je alleen het probleem. Maar het is, idd, ook een oplossing.

Linksom of rechtsom zul je iets in moeten leveren ja; Zolang je in je business laag afdwingt dat je geen Entity maar een Person krijgt is er niets aan de hand. Ga je uitsplitsen naar lossen tabellen zonder root tabel, dan gaat daarmee je referentiele integriteit het raam uit.

[RobIII]

B-Man schreef op vrijdag 03 september 2010 @ 15:30:
[...]

Linksom of rechtsom zul je iets in moeten leveren ja; Zolang je in je business laag afdwingt dat je geen Entity maar een Person krijgt is er niets aan de hand. Ga je uitsplitsen naar lossen tabellen zonder root tabel, dan gaat daarmee je referentiele integriteit het raam uit.

Dan weet ik wel wat ik liever kies Anders join ik me scheel als ik weer wat zaken aan elkaar wil relateren En voor de "pagina" waarop verschillende "typen" voorkomen stop ik dan liever wat extra intelligentie in de BL voor de "pagina".

[ Voor 11% gewijzigd door RobIII op 03-09-2010 16:49 ]

[B-Man]

RobIII schreef op vrijdag 03 september 2010 @ 16:39:
[...]

Dan weet ik wel wat ik liever kies Anders join ik me scheel als ik weer wat zaken aan elkaar wil relateren En voor de "pagina" waarop verschillende "typen" voorkomen stop ik dan liever wat extra intelligentie in de BL voor de "pagina".

Wat de betere oplossing is verschilt per applicatie en platform vind ik, en hoeveel werk je zelf wil doen
Stel dat je gepagineerd een selectie wil tonen uit meerdere tabellen, dan levert dat voor een X aantal subtypes minimaal X queries op op de eerste pagina; Daaropvolgende pagina's is lastiger, je zult immers je paginering in je BL moeten uitvoeren; Uitzondering daarop is als je gaat pagineren op een veld wat in alle subtypes voorkomt (dat kan dan middels de eerder genoemde union), maar als je dan per subtype ook de details wil hebben zit je alsnog aan X queries vast.

Aangezien ik zelf eigenlijk alleen nog maar met ORMs werk die dit soort details verbergen en serverhardware geen drol meer kost, maken een paar joins voor mij geen merkbaar performanceverschil. Dan heb ik persoonlijk liever dat de database middels FKs de integriteit bewaakt (inclusief cascaded deletes die zonder custom triggers e.d. werken), en dat ik middels een X-voudige join in een keer alle subtypes uit de database trek en die dan ook nog eens eenvoudig kan pagineren.

Maar het is een keuze die ieder voor zich moet maken uiteraard

[RobIII]

B-Man schreef op zaterdag 04 september 2010 @ 11:57:
Dan heb ik persoonlijk liever dat de database middels FKs de integriteit bewaakt (inclusief cascaded deletes die zonder custom triggers e.d. werken), en dat ik middels een X-voudige join in een keer alle subtypes uit de database trek en die dan ook nog eens eenvoudig kan pagineren.

Dat is toch precies wat ik ook betoog?
(Behalve dat ik zelf nooit aan cascaded deletes doe; die doe ik liever expliciet in code).

[ Voor 9% gewijzigd door RobIII op 04-09-2010 14:34 ]

[B-Man]

RobIII schreef op zaterdag 04 september 2010 @ 14:34:
[...]

Dat is toch precies wat ik ook betoog?
(Behalve dat ik zelf nooit aan cascaded deletes doe; die doe ik liever expliciet in code).

Met dien verschille dat jij per tabel waar je aan wil koppelen (pagina in je voorbeeld) per subtype een extra tabel moet aanmaken (pagina_X) voor RefInt. Met de oplossing die ik voorstel heb je 1 extra tabel per hiërarchie van objecten waar je mee wil koppelen, en per tabel waar je aan wil koppelen 1 tabel die de m-n relatie bevat.

Maar goed, dit wordt geneuzel over details Ik denk dat de TS voldoende input heeft om zelf een weloverwogen keuze te maken. En dan is jouw oplossing -vanuit de database bekeken- nog wat netter dan die van mij. Vanuit de BL werken ze beiden prima denk ik.

[HarmenM]

Bedankt voor alle feedback. Het was voor mij zeker een nuttig topic om gedachten te horen.

[RobIII]

HarmenM schreef op maandag 06 september 2010 @ 15:42:
Bedankt voor alle feedback. Het was voor mij zeker een nuttig topic om gedachten te horen.

En wat is het nu geworden?