[DB-Design] Meerdere kleine vs 1 grote tabel

[Sypher]

Ik ben in een vurige discussie terecht gekomen over het modelleren van een database, we hebben in beide zaken een verschillende optiek over hoe het nou "best practise" is. Ik zal de situaties even voorleggen.

Het betreft een PostgreSQL database, wat verder niet veel van doen heeft met de situatie.
Het systeem zelf wordt vrij groot en leunt over het algemeen zeer op de database en de tabellen.
Er zullen een flink aantal tabellen aanwezig zijn, en daar kwam dus het meningsverschil

Optiek 1
1 grote tabel (dit kan naar verloop van tijd wel 10.000 tot een veelvoud worden

Optiek 2
Bovenstaande tabel splitsen in 3 tabellen. Deze zullen dus minder rijen bevatten máár de data zou wel sneller dubbel aanwezig kunnen zijn.

Ikzelf denk dat optie 2 veiliger is, namelijk:
- Data is gesplitst (risicospreiding)
- Minder data in een tabel is sneller te doorzoeken
- Backups maken en terugzetten gaat sneller
- Datacorruptie slaat niet direct een gat in het systeem.

Enfin, ik vroeg mij af wat de optieken van anderen waren op dit gebied. Ik moet eerlijk toegegeven dat modelleren niet mijn sterkste kant is en dat ik meer iemand ben die gaandeweg aanpassingen en uitbreidingen maakt zonder het bij het begin direct uit te denken.

Kortom; wat is slimmer? 1 grote tabel of 3 kleinere?

[Verwijderd]

mmmm, bedoel je nu dat je de tabel in 3en hakt of dat je ze dmv relaties aan elkaar koppelt?

[Kalentum]

Begrijp ik het goed dat die drie tabellen dezelfde structuur krijgen?

In dat geval gewoon 1 tabel.
1. datacorruptie in 1 van de 3 tabellen is net zo erg als datacorruptie in 1 grote tabel.
2. Voor data doorzoeken zijn indexen uitgevonden. Als je de juiste indexen aanmaakt is er geen verschil in zoeksnelheid
3. backups terugzetten en maken maakt geen donder uit. 3 tabellen wegschrijven en weer terugzetten is zelfs langzamer dan 1 tabel.

[whoami]

Waarom zou je die tabel splitsen ?
- Datacorruptie ? Tja, als één tabel corrupt is, dan zal je toch een backup moeten terugzetten. Dit is geen gegronde reden.
- 10.000 rows in een tabel is niks. 100.000 rows in een tabel is niks. Mits goede indexen vind je data snel terug. Sneller dan eerst te moeten nagaan of je nu in tabel 1 , 2 of 3 moet zoeken. Ook geen goede reden dus.
- Snellere backups ? Heb je dat getest ? Verder, wat maakt het uit of het maken van een backup nu 10 of 15 minuten duurt ?

Wanneer is het wel een gegronde reden om een tabel in 2 te splitsen ? Als je bv met history gegevens gaat zitten. Bv, een hoop gegevens die je enkel af en toe nog raadpleegt, maar niet meer wijzigt. Dan kan je naar een history tabel gaan verplaatsen zodanig dat je werktabel wat kleiner blijft.

[ Voor 30% gewijzigd door whoami op 22-08-2007 14:11 ]

[GarBaGe]

Sypher schreef op woensdag 22 augustus 2007 @ 14:00:
...
Kortom; wat is slimmer? 1 grote tabel of 3 kleinere?

1 tabel is echt veel beter. Zelfs met miljoenen records is dit geen probleem.

Risico spreiding is niet de verantwoordelijkheid van de database, maar van het OS en onderliggend filesystem. Zorg voor goede backups en RAID setup.
Minder data in een tabel is niet sneller te doorzoeken. Je gebruikt indices (toch?) en die staan in het geheugen! Als je queries steeds langzamer worden door een groeiende database, dan komt dit omdat de query / database structuur kennelijk een full table scan forceert. Dit is altijd langzaam en moet altijd worden voorkomen!

[Niemand_Anders]

Wat split je precies van de tabel op? De velden of de records? Het opsplitsen van de records naar meerdere tabellen heeft een negatieve invloed op je performance. Want in de meeste gevallen zul je dan weer de tabellen moeten samenvoegen met unions en daarover weer meestal een order by uitvoeren.

Velden opsplitsen naar meerdere tabellen kan uiteraard wel een performance winst behalen. Maar doen moet je zeer goed bedenken welke velden in welke tabel komen waarbij je dan zo min mogelijk joins nodig hebt om de informatie weer terug te halen.

Tot 1.000.000 records zal geen enkele bekende database problemen geven. Wij hebben in postgres 8.1 een tabel (tblLog) staan met daarin inmiddels 2.324.766 records. Een query "select * from tblLog where IP='10.0.1.12' and Page='/ws/user.php'" geeft binnen 0.972 seconde 4205 records terug.

[Sypher]

Goed, even wat meer duidelijkheid dan.

- Het aantal rows valt misschien mee, maar het is een bedrijfskritische applicatie en dient dus zo min mogelijk foutgevoelig te zijn.
- Daarom is het dus noodzakelijk dat backups restoren - wanneer dat onverhoopt nodig is - zo snel mogelijk kan verlopen.
- Als je corruptie hebt in 1 tabel ligt alles er uit aangezien dat aan die tabel geknoopt zit, en als je het splitst is het enkel in die specieke tabel.

Het betreft een "accounts" tabel. Mijn collega wil alles in 1 tabel proppen, en daar vervolgens aan de hand van roles er taken (ftp, mail, ...) aan knopen. Wat ik in gedachten had was dus per service (dit zijn er dus 3) een aparte tabel te doen. Dit aangezien ik van mening ben dat de gegevens in de tabellen genoeg van elkaar verschillen.

Het is dus niet zo dat je van:
1|2|3|4|5|6|7|8|9

1|2|3, 4|5|6, 7|8|9 maakt.

Daarnaast komt daar een stukje praktische zaken bij kijken, maar dat is een verhaal apart wat verder losstaat van de issue meerdere vs 1.

[ Voor 4% gewijzigd door Sypher op 22-08-2007 14:19 ]

[GarBaGe]

Okee, bedrijfskritische applicaties los je niet op door tabellen op te splitsen. Je kan wel kiezen voor meerdere fysieke gesynchronizeerde servers (master-slave setup) en/of RAID.
En een tabel raakt niet zomaar corrupt. Als dat wel gebeurt, is de oorzaak snel een software fout of een filesystem fout, waardoor je andere tabel zeer waarschijnlijk ook de sigaar is.
En voor user accounts en rechten kan je natuurlijk ook kijken naar bestaande bewezen applicaties (bv LDAP) in plaats van een do-it-yourself oplossing. De meeste fouten en problemen worden veroozaakt door software fouten (lees: programmeer fouten), niet door hardware fouten.

Als je zegt: de tabellen verschillen genoeg van elkaar om verschillende tabellen te gebruiken: voor dan een behoorlijke database analyse uit!
Maar verschillende tabellen gebruiken met als doel: fout bestendigheid verhogen is echt de grootste schijn-veiligheid die je maar kan bedenken!!

[NMe]

Waar hoort mijn topic?

PRG>>SEA

[whoami]

Ik denk dat jij hier gewoon wilt horen dat jouw mening de juiste is, maar dat is het niet ....

Als je je tabel splitst over meerdere tabellen, heb je gewoon meer kans op corrupte / foute data. Stel bv dat je een unique constraint op een bepaalde veld-combinatie wilt hebben.
Als al je gegevens in één tabel zitten, kan je databank deze constraint gewoon makkelijk beheren.
Zijn je gegevens gesplitst over meerdere tabellen, dan kan je dat niet meer.... In dat geval kan je bv een record toevoegen in tabel 1, terwijl er in tabel 2 ook al een record bestaat met deze combinatie.

Bye Bye integriteit van je gegevens.

[__fred__]

Sypher schreef op woensdag 22 augustus 2007 @ 14:18:
Wat ik in gedachten had was dus per service (dit zijn er dus 3) een aparte tabel te doen.

En wat ga je dan doen als er een nieuwe service bijkomt? Nieuwe tabel? En als je nou alle accounts voor alle services wilt hebben, wat voor queries denk je dan te krijgen, drie keer union?

Je collega heeft gelijk. Gewoon netjes normaliseren volgens de normale regels en je indexen goed kiezen, dan wordt dat geen enkel probleem.

Ook de dataintegriteit is geen issue, omdat een goede backup altijd mogelijk maakt om een restore te doen. Desnoods op een aparte database, waarna je alleen een selectie overhaalt naar je productieomgeving.

Mocht je nou gigantisch veel records krijgen, waarvan 80% archiefmateriaal is, dan kun je nog een keer Partioning overwegen. Een goede DB ondersteunt dat en is eigenlijk wat jij hier zelf probeert te knutselen, maar dan in een nette DB feature gegoten. Zowel SQL Server, MySQL, DB2 als Oracle ondersteunen partitioning.

http://msdn2.microsoft.com/en-us/library/ms190787.aspx

[ Voor 3% gewijzigd door __fred__ op 22-08-2007 14:39 ]

[GarBaGe]

Dit topic doet mij denken aan de vele entries die ik ben tegengekomen op:

http://thedailywtf.com

Je dagelijkse portie serieuze software screw-ups, waaronder: 1 tabel per artikel. Of 1 tabel per klant Of tabelnamen met een cryptische 6 letter-code... Het zou eigenlijk een site moeten zijn om je krom om te lachen, maar helaas blijken de artikelen vaak op waarheid gebaseerd.
Zo ook onze TS...

[Verwijderd]

GarBaGe schreef op woensdag 22 augustus 2007 @ 14:13:
Minder data in een tabel is niet sneller te doorzoeken. Je gebruikt indices (toch?) en die staan in het geheugen! Als je queries steeds langzamer worden door een groeiende database, dan komt dit omdat de query / database structuur kennelijk een full table scan forceert. Dit is altijd langzaam en moet altijd worden voorkomen!

Dat schaalt maar in beperkte mate. Tegen de tijd dat je tabellen krijgt met 300 miljoen rows, wil je dit echt gaan opsplitsen als dat mogelijk is. Zeker als je vaak maar data uit een beperkt aantal tabellen nodig hebt.

Voor de situatie van TS lijkt het me simpel. Hoeveel accounts zullen er totaal zijn? 100.000? Uitgaande van 1KB per row zou dat dan om 100MB gaan. Peanuts. Je hele database past zelfs in je geheugen. Niet de moeite waard om op te splitsen dus. En elke competente databaseserver heeft prima mogelijkheden voor high availability, dus daar zit het probleem ook niet. Lekker 1 table houden dus (en RAID is vooral HA, geen backup, dus uitsluitend nuttig als toevoeging op andere bescherming).

[den 150]

Je bedoelt natuurlijk gewoon sharding. Voor de huis-tuin-en-keuken developer een zéér slecht idee, dit zou ik pas toepassen wanneer "ik gooi er gewoon wat meer hardware tegenaan" te duur wordt (iets wat ik niet verwacht ooit te moeten doen).

[ACM]

den 150 schreef op woensdag 22 augustus 2007 @ 22:57:
Je bedoelt natuurlijk gewoon sharding. Voor de huis-tuin-en-keuken developer een zéér slecht idee, dit zou ik pas toepassen wanneer "ik gooi er gewoon wat meer hardware tegenaan" te duur wordt (iets wat ik niet verwacht ooit te moeten doen).

Dat hoeft niet, het kan ook zijn dat table partitioning een prima oplossing is, zonder losse servers te gebruiken. Maar ik ben het met de mede-posters eens dat dat voor een tabel met minder dan een paar miljoen records meestal niet zinvol en vaak zelfs onverstandig is om te doen.

Sypher schreef op woensdag 22 augustus 2007 @ 14:18:
- Het aantal rows valt misschien mee, maar het is een bedrijfskritische applicatie en dient dus zo min mogelijk foutgevoelig te zijn.

Wat precies de reden is dat je het in een consistente tabel wilt hebben, niet in losse stukjes die mogelijk corrupties onderling kunnen ontwikkelen en waar je allerlei lastige queries en stukken administratieve overhead omheen moet ontwikkelen. Hoe meer code je nodig hebt om queries uit te voeren, hoe erger de kans op meer fouten is en daardoor neemt de foutgevoeligheid dus juist toe.
Data-veiligheid bereik je door goede backups te maken, evt met een replicatie-oplossing en Point In Time Recovery om je backups aan te vullen met up-to-the-last-minute afspeelbare transactielogs.

- Daarom is het dus noodzakelijk dat backups restoren - wanneer dat onverhoopt nodig is - zo snel mogelijk kan verlopen.

Bij backups restoren wil je in de eerste plaats dat het goed gebeurd en pas in de tweede plaats dat het snel gaat. De kans dat slechts een enkele tabel corrupt geraakt is, is mijns inziens vrij klein, maar dan nog loop je grote kans dat je alsnog de hele database opnieuw moet inladen. Je moet tenslotte vooral ook een consistente versie van je data terugzetten, niet enkel van elk los stukje de laatste versie - die dus mogelijk verschillen.
Het lijkt me voor bedrijfskritische data namelijk juist erger dat een bewerking - door het half terugzetten van een backup - maar half verwerkt is, dan helemaal niet.

Maar ook hier geldt weer dat je waarschijnlijk beter complete backups kunt maken en die aan kunt vullen met PITR om zo zo veel mogelijk van je data terug te kunnen zetten, mocht er wat mis gaan.

[q]- Als je corruptie hebt in 1 tabel ligt alles er uit aangezien dat aan die tabel geknoopt zit, en als je het splitst is het enkel in die specieke tabel.[/
Mja, maar je kan niet garanderen dat je corruptie enkel in die ene losse splits-tabel zit, dus zul je alsnog moeten nagaan dat de rest wel o.k. is, mogelijk kost je dat alleen maar meer tijd...

Het betreft een "accounts" tabel. Mijn collega wil alles in 1 tabel proppen, en daar vervolgens aan de hand van roles er taken (ftp, mail, ...) aan knopen. Wat ik in gedachten had was dus per service (dit zijn er dus 3) een aparte tabel te doen. Dit aangezien ik van mening ben dat de gegevens in de tabellen genoeg van elkaar verschillen.

Uit het oogpunt van normalisatie is zijn oplossing de beste. En om de foutgevoeligheid te verlagen wil je over het algemeen normaliseren, niet denormaliseren.

Het is dus niet zo dat je van:
1|2|3|4|5|6|7|8|9

1|2|3, 4|5|6, 7|8|9 maakt.

En wat doe je met klanten die zowel ftp en mail etc hebben? Ga je die dubbel opslaan? Dubbel opslaan levert nog meer foutgevoeligheid op.

[EfBe]

GarBaGe schreef op woensdag 22 augustus 2007 @ 15:02:
Dit topic doet mij denken aan de vele entries die ik ben tegengekomen op:

http://thedailywtf.com

Je dagelijkse portie serieuze software screw-ups, waaronder: 1 tabel per artikel. Of 1 tabel per klant Of tabelnamen met een cryptische 6 letter-code... Het zou eigenlijk een site moeten zijn om je krom om te lachen, maar helaas blijken de artikelen vaak op waarheid gebaseerd.
Zo ook onze TS...

Over cryptische 6 letterige table names: Er is op aarde een database, met het grootste marktaandeel nog wel, die heet DB2. DB2 had (en heeft op AS400 nog steeds dacht ik) het euvel dat je maar 8 characters per tablename mocht gebruiken. Men ging dus snel over tot codes voor tablenames.

Neemt niet weg dat wanneer het niet nodig is je het idd niet moet doen. Tot zover het nutteloze triviaatje van de dag!

Sypher schreef op woensdag 22 augustus 2007 @ 14:00:
Ik ben in een vurige discussie terecht gekomen over het modelleren van een database, we hebben in beide zaken een verschillende optiek over hoe het nou "best practise" is. Ik zal de situaties even voorleggen.

Het betreft een PostgreSQL database, wat verder niet veel van doen heeft met de situatie.
Het systeem zelf wordt vrij groot en leunt over het algemeen zeer op de database en de tabellen.
Er zullen een flink aantal tabellen aanwezig zijn, en daar kwam dus het meningsverschil

Optiek 1
1 grote tabel (dit kan naar verloop van tijd wel 10.000 tot een veelvoud worden

Je bedoelt, 10000 schamele rows in die table of 10000 tables?

Optiek 2
Bovenstaande tabel splitsen in 3 tabellen. Deze zullen dus minder rijen bevatten máár de data zou wel sneller dubbel aanwezig kunnen zijn.

Ikzelf denk dat optie 2 veiliger is, namelijk:
- Data is gesplitst (risicospreiding)

Nee.

- Minder data in een tabel is sneller te doorzoeken

Nee, want je moet nog steeds de index door van 3 tables.

- Backups maken en terugzetten gaat sneller

Nee, want de 3 tables samen vormen 1 entity. WANNEER leert men nu eens wat basisprincipes van de informatica alvorens met de tengels aan bv databases te zitten. Je moet niet op z'n Fowleriaans maar wat tables gaan zitten refactoren als een kip zonder kop, die tables zijn een representatie van entities die je hebt herkent in je domain. Heb je 1 entity? Heb je 1 table.

Wanneer je miljoenen en miljoenen rows hebt, wil men soms nog wel eens de table horizontaal splitsen, dus dat de ene 50% in de ene table zit en de andere 50% in de andere. Echter, dit is in veel gevallen een ramp om tegenaan te programmeren, en daarom laat men dat meestal over aan het RDBMS. Ik weet niet of PostgreSql dit ondersteunt, het zou zomaar kunnen.

- Datacorruptie slaat niet direct een gat in het systeem.

Erm... transaction log, backup, goede spullen kopen etc. etc... die zorgen voor prima recovery. Ookal splits je het 10 keer op, dan nog ben je data kwijt bij een corrupte table wanneer je geen goede disaster recovery hebt.

Enfin, ik vroeg mij af wat de optieken van anderen waren op dit gebied. Ik moet eerlijk toegegeven dat modelleren niet mijn sterkste kant is en dat ik meer iemand ben die gaandeweg aanpassingen en uitbreidingen maakt zonder het bij het begin direct uit te denken.

DIt heeft geen snars met modelleren te maken maar hoe data wordt opgeslagen. Oracle bv ondersteunt het horizontaal opsplitsen van tables, maar dat is transparant voor de gebruiker, en dat is ook de enige weg. Zelf gaan zitten knoeien met opsplitsen van tables is echt een ramp en veelal levert het niets op. Oh, en 10.000 rows is echt helemaal niks, ik zou pas denken over horizontaal opsplitsen bij een paar miljoen rows en dan nog.

[ Voor 61% gewijzigd door EfBe op 23-08-2007 09:34 ]

[Verwijderd]

EfBe schreef op donderdag 23 augustus 2007 @ 09:26:
[...]

DIt heeft geen snars met modelleren te maken maar hoe data wordt opgeslagen. Oracle bv ondersteunt het horizontaal opsplitsen van tables, maar dat is transparant voor de gebruiker, en dat is ook de enige weg. Zelf gaan zitten knoeien met opsplitsen van tables is echt een ramp en veelal levert het niets op. Oh, en 10.000 rows is echt helemaal niks, ik zou pas denken over horizontaal opsplitsen bij een paar miljoen rows en dan nog.

Dat heet partitioneren in Oracle. Dan kun je je tabel verdelen in partities op basis van een range, hash of list. Combinaties zijn ook mogelijk b.v. range en hash. Een datum veld wordt hier vaak voor gebruikt. Indexeren kan dan ook weer per partitie.

Postgre heeft alleen range en list partitioning.

[EfBe]

Verwijderd schreef op donderdag 23 augustus 2007 @ 10:44:
[...]

Dat heet partitioneren in Oracle. Dan kun je je tabel verdelen in partities op basis van een range, hash of list. Combinaties zijn ook mogelijk b.v. range en hash. Een datum veld wordt hier vaak voor gebruikt. Indexeren kan dan ook weer per partitie.

dank, ik was even de term kwijt en had geen zin om die op te zoeken

Postgre heeft alleen range en list partitioning.

Dat zou dan genoeg moeten zijn volgens mij, althans wanneer de table in aanmerking zou komen voor partitioning, echter de table wordt niet echt overladen met data, dus lijkt het me wat overbodig die dan te gaan partitioneren.

[Verwijderd]

EfBe schreef op donderdag 23 augustus 2007 @ 14:07:
[...]

Dat zou dan genoeg moeten zijn volgens mij, althans wanneer de table in aanmerking zou komen voor partitioning, echter de table wordt niet echt overladen met data, dus lijkt het me wat overbodig die dan te gaan partitioneren.

Op het moment dat er performance problemen gaan onstaan doordat de tabel te groot wordt kan je altijd nog besluiten te gaan partitioneren.

[MSalters]

Ik denk dat de TS een groot probleem heeft. Hij moet een DB ontwerpen, die bedrijfskritisch is, maar daarvoor heeft hij te weinig kennis. Ik vraag me af of het dan een goed advies is om hem bij stap 1 te helpen. Laten we hem zo niet ten onrechte in de waan dat hij bedrijfskritische databases kan bouwen?

[Boss]

Ja, laten we elkaar allemaal ontmoedigen om dingen uit te zoeken, onszelf iets nieuws aan te leren en te discussieren over ideeën

Ik kan nergens vinden dat het hier om een bedrijfskritische DB gaat, en dat TS het in zijn eentje moet doen en de volle verantwoording draagt.

[Juup]

Sypher, luister naar je discussiepartner (collega) en stop het in 1 tabel. Ga daarna een database tutprial lezen.
Bv. deze: http://www.sum-it.nl/cursus/dbdesign/hollands/intro010.php3

[joggie]

Als het inderdaad zoals je zegt om een bedrijfskritische database gaat, is het misschien meer van belang DAT je de data kunt terug zetten in plaats van hoelang het nou precies duurt. In het geval van 100.000 records zal het eerder in seconden dan minuten schelen...

Wat betreft de snelheid van zoeken en de foutgevoeligheid etc ben ik ook van mening dat je alles beter in 1 tabel kunt stoppen.

[edeboeck]

Boss schreef op zaterdag 25 augustus 2007 @ 11:51:(knip)
Ik kan nergens vinden dat het hier om een bedrijfskritische DB gaat, en dat TS het in zijn eentje moet doen en de volle verantwoording draagt.

Wat het bedrijfskritische karakter betreft: ik wel

Sypher schreef op woensdag 22 augustus 2007 @ 14:18:
Goed, even wat meer duidelijkheid dan.

- Het aantal rows valt misschien mee, maar het is een bedrijfskritische applicatie en dient dus zo min mogelijk foutgevoelig te zijn.
(knip)

Ik ben het met je eens dat je moet gestimuleerd worden om te leren, maar probeer dan thuis eerst wat uit zodat je tenminste een degelijke basis hebt om van te vertrekken als je aan een bedrijfskritische applicatie moet beginnen.

[EfBe]

Volgens mij is verreweg de meeste bedrijfsmatig ingezette software bedrijfskritisch. Onzin? Ga maandag even langs bij wat systemen op je werk en trek even wat stekkers uit de systemen. Ga dan maar eens kijken hoeveel ellende dat veroorzaakt.

[MisterBlue]

[quote]Volgens mij is verreweg de meeste bedrijfsmatig ingezette software bedrijfskritisch. Onzin? Ga maandag even langs bij wat systemen op je werk en trek even wat stekkers uit de systemen. Ga dan maar eens kijken hoeveel ellende dat veroorzaakt.[/qoute]

Ja, gebruikers raken van slag omdat ze nu anders moeten werken. Als het een week duurt, heeft men er om heen gewerkt en blijkt dat men het met de helft van de systemen ook kan doen.