[mysql] DB model forum wel of niet volgens normaalvormen

[ripexx]

Ik ben bezig om een klein forum te implementeren in mijn cms om zodoende meer backoffice activiteiten online te zetten.

Nu heeft mysql een aantal grote beperkingen zoals het niet ondersteunen van subselects enz. Nu heb ik de source van verschillende fora door gekeken en zie dat deze eigenlijk niet geheel volgens de normaalvormen zijn opgebouwd. Als ik het goed gedaan heb zou het db model er ongeveer zo uit moeten zien.

Fora		Topic		Post
Forum_id		Topic_id		Post_id
Naam		Forum_id		Topic_id
Omschrijving		Topic titel		Post

De tabellen zullen altijd meer info bevatten maar het gaat om het idee

Aan de hand van dit basis model kan je in wezen alle informatief herleiden. Dus om een index te maken zoals op got zul je alle tabellen moeten aanspreken om de forum naam, omschrijving, aantal posts, aantal reply's en lastpost datum/tijd te verkrijgen. Nu zie je bij verschillende fora dat informatie als aantal posts, reply's en lastpost time zijn opgenomen als een kolom in je db.

Dit heeft een aantal reden die ik zo kan bedenken;
- Query's worden een stuk eenvoudiger
- DB belasting is lager door minder joins en/of subselects
- MySQL heeft geen subslects.

Als gevolg hiervan is je db model dus niet opgebouwd volgens de noormaal vormen omdat je rekenvelden opneemt in je db. Verder worden ander acties zoals een topic move of post delete een stuk complexer en kan dus leiden tot foutieve data in je db.

Nu ik ds zelf bezig ben zie ik dus een aantal problemen, ik heb dus redleijk volgens de normaalvormen een db opzet gemaakt en loop dus tegen problemen aan als het niet ondersteuen van subselects. Nu kan je hier redelijk omheen werken door joins te gebruiken wat dus opzich niet zo'n probleem is. Maar zie bijvoorbeeld de volgende query; doe is het verkrijgen van een overzicht van alle topics binnin een bepaalde tijd, eerst gesorteerd op status dan op lastpost time. Als uitkomst moeten er dus info als status, topictitel, lastposttime en id, aantal replyl's enz worden opgehaald.

SQL:

SELECT 
   forum_topic.titel, forum_topic.status, forum_topic.lid_id,
   forum_topic.forum_id, forum_topic.forum_topic_id, 
   forum_post.pdt, forum_post.deleted, forum_post.lid_id, 
   forum_post.forum_topic_id, forum_post.forum_post_id , 
   leden.lid_id, leden.voornaam, leden.tussenvoegsel, 
   leden.achternaam, leden.functie, 
   count(forum_post.forum_post_id) as reply
FROM 
   forum_topic LEFT JOIN leden 
     ON forum_topic.lid_id = leden.lid_id
   LEFT JOIN forum_post 
     ON forum_topic.forum_topic_id = forum_post.forum_topic_id
WHERE 
   forum_topic.forum_id = '$fid' 
   AND forum_post.deleted = 'N' 
   AND forum_post.pdt > '$dts'
GROUP BY 
   forum_post.forum_topic_id DESC
ORDER BY 
   forum_topic.status ASC, 
   forum_post.pdt ASC;

Gevolg is dat door de group by niet de lastpost maar de firstpost wordt geselecteerd. Terwijl de sortering wel goed gaat op lastpost. Maar dat terzijde, het gaat om het voorbeeld.

Mijn vraag is dan ook, laat je de normaalvormen vallen en ga je voor eenvoudigere query's die je de juiste informatie geven maar weer niet zorgen voor een waterdicht db model. Of kies je juist voor een zo perfect mogelijk model, waarbij je dus in het geval van MySQL tegen zeer irritante zaken aanloopt.
_{Ik weet dat geavanceerdere RDBMS'en wel ondersteuning bieden voor transacties, subselect en wat ever maar helaas is de werkelijkheid zo dat voor veel van dit soort zaken gebruik wordt gemaakt van MySQL, dus reply's over andere RDBMS'en hebben dan ook weinig nut, aangezien het hier vooral over MySQL gaat.}

[whoami]

Je Group by is niet goed. Je moet groeperen op alle velden die in je SELECT list staan en die geen group by zijn.

Rekenvelden opnemen in je DB is niet volgens de normaalvormen, maar in bepaalde gevallen is het toch aan te raden om dat te doen. Stel bv. dat je in de index het aantal posts per subforum/forum wilt laten zien. Als je dat iedere keer met een select count() moet gaan tellen, kan dat nadelig zijn voor de performance. Daarom is het beter om daar een calculated field voor te gebruiken (opslaan dus). Data-integriteit van dat veld kan je waarborgen dmv een INSERT en DELETE trigger. Bij het trashen van een post verminder je het bijhorende calc. field, bij het posten verhoog je het.

Moraal v/h verhaal: soms moet je je DB na het normaliseren ook de-normalizeren. Dit om de performance van je queries goed te houden.
Voor data-markts enzo wordt er zelfs veel gebruik gemaakt van data-normalisatie omdat die gegevens niet wijzigen. Dat zijn historische gegevens waar er enkel select's op gebeuren.

[ Voor 19% gewijzigd door whoami op 03-09-2003 19:43 ]

[Creepy]

Als je je DB model gaat aanpassen zodat je "minder moeilijke queries" hoeft te schrijven.. tja... eehh.. dat moet je zelf weten

Het feit dat totalen (post aantal bijv) in je DB als kolom worden opgenomen is puur en alleen vanwege performance redenen. Dat je "simpelere" queries krijgt is toevallig mooi meegenomen.

Edit en je group by is niet correct, ook al vindt MySQL dit een feature.
Edit2: en whoami was me voor

_{Enneuh.. een DESC bij een group by?}

[ Voor 20% gewijzigd door Creepy op 03-09-2003 19:44 ]

[bigtree]

Er zijn een aantal dingen die me opvallen aan je post.

Ten eerste heb je het over een 'klein' forum. Performance is dan vast niet zo'n hot item.

Daarnaast wil je een 'waterdicht' db model met een MySQL database? Dat wordt lastig als je geen referentiële integriteit kan afdwingen op je database.

Overigens lijkt het mij dat wat je wilt prima is op te lossen met MySQL, en zelfs zonder subselects. Je eigen creativiteit is de belangrijkste beperking van MySQL.

[ripexx]

Ja een DESC of ASC mag volges mysql in de Group By, mijn probleem komt dus daar deels uitvoort, ik zal nog een sgaan kijken naar de group by. Gisteren bijna drie lopen kloten op deze query, waarna ik de source van PHPBB en Jottum heb bijgepakt. Daarin zie ik dus een hoop zaken die eigenlijk dus niet volgens de normaalvormen zijn. Zeker bij grote tabellen met miljoenen records kunnen deze count() voor zeer veel belasting zorgen.

Ik weet dat er een verschil is tussen theorie en praktijk, waardoor je dus in sommige situate afwijkt van bepaalde stelregels. Maar de regel is dus dat je alleen afwijkt als dit je preformance te goede komt anders ben je dus niet echt snugger/slim bezig.

Maar een hint om mijn query zo te laten werken dat ik wel mijn lastpost terug krijg ipv de firstpost.

[Yoeri]

ripexx schreef op 03 September 2003 @ 19:35:
Mijn vraag is dan ook, laat je de normaalvormen vallen en ga je voor eenvoudigere query's die je de juiste informatie geven maar weer niet zorgen voor een waterdicht db model. Of kies je juist voor een zo perfect mogelijk model, waarbij je dus in het geval van MySQL tegen zeer irritante zaken aanloopt.
_{Ik weet dat geavanceerdere RDBMS'en wel ondersteuning bieden voor transacties, subselect en wat ever maar helaas is de werkelijkheid zo dat voor veel van dit soort zaken gebruik wordt gemaakt van MySQL, dus reply's over andere RDBMS'en hebben dan ook weinig nut, aangezien het hier vooral over MySQL gaat.}

De laatste stap in het normaliseren van een database-ontwerp is het dénormaliseren ten gunste van performantie. Bij deze is je vraag beantwoord, want dénormaliseren is een onderdeel van het normaliseren (kan het nog gekker klinken? )

[whoami]

Robbedoeske schreef op 03 September 2003 @ 19:58:
[...]


De laatste stap in het normaliseren van een database-ontwerp is het dénormaliseren ten gunste van performantie. Bij deze is je vraag beantwoord, want dénormaliseren is een onderdeel van het normaliseren (kan het nog gekker klinken? )

4de en 5de NF zijn dat.

[ripexx]

whoami schreef op 03 September 2003 @ 20:04:
[...]


4de en 5de NF zijn dat.

Ben nooit verder gekomen dan de 3e, doe geen itc maar bedrijfskunde

Maar dan is het duidelijk Was alleen benieuwd naar de effecten van je db ontwerp op preformance en normaliseren.

Maar als ik heel lullig ben zeg ik dus dat ik ga voor preformance en maak het me daardoor zelf een stuk eenvoudiger

[whoami]

ripexx schreef op 04 September 2003 @ 01:53:
[...]


Maar als ik heel lullig ben zeg ik dus dat ik ga voor preformance en maak het me daardoor zelf een stuk eenvoudiger

Je mag daar nu ook niet in overdrijven. Normaliseer eerst je data-model tot de 3de normaalvorm.
Daarna kan je gaan kijken of het nodig is om bepaalde calc. fields te gaan opslaan.

Een niet genormaliseerd data-model is ook nadelig voor de performance en voor de integriteit van je gegevens.

[ripexx]

Oke na normalisatie ziet het db model er zo ongeveer uit:
_{Legenda:Tabel naam | primary key | overige velden}

fora forum_id naam omschrijving post_count reply_count afkt rank history

Topic forum_topic_id forum_id titel status lid_id

Post forum_post_id forum_topic_id content_ubb content_par post_date post_uid update_date update_uid deleted

Na het toevoegen van enkele rekenvelden:

fora forum_id naam omschrijving post_count reply_count afkt rank history reply_count post_count last_post_id last_post_date

Topic forum_topic_id forum_id titel status lid_id reply_count last_post_id last_post_date

Post forum_post_id forum_topic_id content_ubb content_par post_date post_uid update_date update_uid deleted

Als je dus gaat kijken naar preformance heeft het als voordeel dat query's zeer snel kunnen worden uitgevoerd, zeker als het aantal posts en topic hoog zijn. Voor een kleine db omvang heeft het dus relatief weinig nut. Nadeel is dat de opslag iets groter is geworden maar zal per reply niet significant toenemen en is dus te verwaarlozen als geheel. De programma code zal er voor moeten zorgen dat de rekenvelden bij een verandering (Reply, TS, Move, Delete enz.) worden bijgewerkt.

[Apache]

Dit heb ik ook voorgelegd aan m'n leerkrachten. M'n eindwerk was nl een forum (ja niet origineel enz). Zij zagen er daarna ook geen probleem in om dit goed te keuren en te beschouwen als performance verbeterende velden. Zo word het ook gedaan op de meeste fora, ik neem aan ook react.

De postcount van users is er trouwens ook nog eentje.

[docs] (1.3MB)

[ripexx]

Voor de postcount van users is het niet zo'n probleem, zeker als je een index legt op de uid in je post veld. Je zou moeten brekenen met een grote record set wat de gevolgen zijn voor de preformance.