Database ontwerp voor telefoonsysteem

[Lukse]

We hebben op het werk een telefoonsysteem dat per kwartier verschillende gegevens over een helpdesk agent opslaat in een oracle database, bv. de totale login tijd, after call work tijd, average handling time...
De structuur van die tabellen ziet er ongeveer zo uit:

Agents
Id
Name
...

Data
Timestamp
AgentId
Login_01
Login_02
Login_03
ACW_01
ACW_02
ACW_03
...

Waarbij de 01, 02, 03, ... slaan op de verschillende producten.

Als er nieuwe gegevens in de db komen, verdwijnen er oude. Er wordt voor een dikke maand data bijgehouden.

We hebben nu een project opgezet, waarbij we op een intranet site, realtime alle gegevens van een agent kunnen bekijken.
Een extra feature is nu, dat we ook historische data gaan laten zien: per uur, dag, week, maand, kwartaal, jaar.
Maar we lopen hier dus tegen die maand limiet op.

Het idee is nu om zelf een soort database aan te leggen, waar alle data gewoon in blijft staan.
Elke kwartier runnen we dus een script dat de laatste data ophaalt en wegschrijft in onze db.
Na verloop van tijd gaat dit dus enorm veel data worden, en we zoeken de beste manier om deze gegevens op te slaan.

Een collega stelt voor om dit in xml te doen, en dan op te delen in verschillende dirs per jaar, maand, dag, ... wat wil zeggen dat we xml files moeten gaan parsen, wat volgens mij enorm traag is met zoveel data.

Mij lijkt het beter om opnieuw een db te gebruiken, met een beter structuur (niet zo ranzig als hierboven ). Maar het probleem is dan dat een aantal tabellen enorm groot gaan worden, wat dus ook niet ten goede komt aan de performance.

Wat denken jullie dat het beste is? Opslaan als xml, of toch een db gebruiken?
Zijn er dan misschien manieren om die db te splitsen, zodat de data gesplitst kan worden?
Of zijn er andere technieken?


Edit: Timestamp veld toevoegd in data tabel. Veld ziet er zo uit: 200612201115

[DiedX]

Disclaimer: Ik ben absoluut geen database-expert.

XML is leuk, maar ik zie de toegevoegde waarde niet er van in bij dit project. Verder is de database-structuur (?) welke je geeft verre van volledig. Ik denk dat je terug moet gaan naar je vraagstelling: wat is exact je bedoeling? Wat doet een agent, behalve bellen natuurlijk? Wat wil je geregistreerd hebben?

Wel interessant, dus ik heb een bookmark erbij.

[whoami]

XML is helemaal geen optie om grote datasets in op te slaan. XML moet je ook niet zien als een database. Wat zijn de argumenten van die collega om XML te gebruiken ?
Die XML file gaat gigantisch groot worden, en het zal niet makkelijk zijn om er statistische queries op los te laten.

Het beste is imho om te werken met history tabellen binnen je database. Je houdt dus je 'werk-tabel' waarin de gegevens van de laatste maand in staan, en op gezette tijden (laatste dag / nacht van de maand), transfer je dan die gegevens naar je history tabel. Aangezien die history tabel enkel nog voor retrieval gebruikt wordt, kan je daar een aantal indexen op leggen die specifiek bedoeld zijn / geoptimaliseerd zijn voor het snel ophalen van de statistische gegevens.

[bat266]

Mij lijkt dat je bijvoorbeeld de historiche data in de vorm van rapporten kunt opslaan per week /dag, zodat je niet de volledige onderliggende data nodig hebt. In ieder geval lijk mij xml niet makkelijk te gebruiken aangezien het formaat veel overhead meebrengt en voor dit een database sneller is in mijn ervaring.

[Lukse]

whoami schreef op woensdag 20 december 2006 @ 11:21:
Het beste is imho om te werken met history tabellen binnen je database. Je houdt dus je 'werk-tabel' waarin de gegevens van de laatste maand in staan, en op gezette tijden (laatste dag / nacht van de maand), transfer je dan die gegevens naar je history tabel. Aangezien die history tabel enkel nog voor retrieval gebruikt wordt, kan je daar een aantal indexen op leggen die specifiek bedoeld zijn / geoptimaliseerd zijn voor het snel ophalen van de statistische gegevens.

Dit lijkt mij ook wel een goede oplossing.
Een nadeel is dan wel de week data, want een bepaalde week kan in 2 maanden zitten.

[whoami]

Wat is het probleem met die week precies ?
Je kan toch bepalen tot welk weeknummer een bepaalde datum behoort ?

Ik zie eerlijk gezegd het probleem hiervan niet in ? Je history gegevens gaan toch allemaal in dezelfde tabel ? (Ik bedoel, de gegevens van de maand januari gaan naar de tabel bla_hist, en de gegevens van de maand februari gaan naar diezelfde bla_hist tabel).

_{wel een beetje een rare DB structuur hoor, als ik je TS zo bekijk.}

[Lukse]

whoami schreef op woensdag 20 december 2006 @ 11:47:
Wat is het probleem met die week precies ?
Je kan toch bepalen tot welk weeknummer een bepaalde datum behoort ?

Ik zie eerlijk gezegd het probleem hiervan niet in ? Je history gegevens gaan toch allemaal in dezelfde tabel ? (Ik bedoel, de gegevens van de maand januari gaan naar de tabel bla_hist, en de gegevens van de maand februari gaan naar diezelfde bla_hist tabel).

Ah, alle history in 1 tabel.
Ik dacht dat je bedoelde per maand een tabel.
Wordt die tabel niet veel te groot dan?

_{wel een beetje een rare DB structuur hoor, als ik je TS zo bekijk.}

Je hebt de helft nog niet gezien.
Als bv een agent een week verlof heeft, worden er gewoon een hele week nullen opgeslagen, elk kwartier, voor elke lijn, voor elk product.
Best wel grappig

[DiedX]

Lukse schreef op woensdag 20 december 2006 @ 11:52:
[...]

Ah, alle history in 1 tabel.
Ik dacht dat je bedoelde per maand een tabel.
Wordt die tabel niet veel te groot dan?

Tuurlijk wel. Maar je kan de maand bepalen aan de hand van een timestamp.

Dus agent belt: timestamp
Agent hangt op: Timestamp

en dan zeggen: SELECT * FROM * WHERE timestamp > 1234 AND timestamp < 6789

En het legen van tabellen bestaat ook nog he? (Voor als je dat zou willen )

[whoami]

Lukse schreef op woensdag 20 december 2006 @ 11:52:
[...]

Ah, alle history in 1 tabel.
Ik dacht dat je bedoelde per maand een tabel.
Wordt die tabel niet veel te groot dan?

Hoeveel records heb je nu per maand ? Een DB kan wel eea aan hoor...

[Lukse]

whoami schreef op woensdag 20 december 2006 @ 12:11:
[...]
Hoeveel records heb je nu per maand ? Een DB kan wel eea aan hoor...

Nu zijn het ongeveer een miljoen records per maand, maar die hoop ik toch minstens te halveren, door onnodige data eruit te laten.
Als een agent niet ingelogd is, hoeft er immers ook geen data over hem opgeslagen te worden, wat nu wel gebeurt.

[BertS]

Bij <1miljoen records per maand zou ik me niet veel zorgen maken. Mits je indexen goed staan is daar niets aan de hand. 12miljoen recs kan een beetje db toch wel verwerken. Ik weet niet hoeveel jaren je wilt gaan opslaan, maar dan zou je wellicht nog eens kunnen gaan overwegen om per jaar een aparte tabel aan te maken. Maar daar hoef je de eerste twee jaar ook nog niet mee te werken denk ik.

Ik weet niet of de data van dit nieuwe project realtime beschikbaar moet zijn? Anders kun je overwegen om het ('s nachts) dmv een batch te laten bijwerken naar jouw stats-db (vanuit de nu bestaande db).

Als je veel (voor jullie gestandaardiseerde) groupby-queries gaat uitvoeren (bijvoorbeeld samenvattingen per dag, per week, per maand), wat ik me goed kan voorstellen in deze situatie, kun je die samenvattingen ook in een batch laten bijwerken. Dus je hebt een tabel met de individuele recs, een tabel met dagtotalen, een tabel met weektotalen, etc.

[Lukse]

BertS schreef op woensdag 20 december 2006 @ 14:07:
Bij <1miljoen records per maand zou ik me niet veel zorgen maken. Mits je indexen goed staan is daar niets aan de hand. 12miljoen recs kan een beetje db toch wel verwerken. Ik weet niet hoeveel jaren je wilt gaan opslaan, maar dan zou je wellicht nog eens kunnen gaan overwegen om per jaar een aparte tabel aan te maken. Maar daar hoef je de eerste twee jaar ook nog niet mee te werken denk ik.

De db zal wsl MySql worden.

Ik weet niet of de data van dit nieuwe project realtime beschikbaar moet zijn? Anders kun je overwegen om het ('s nachts) dmv een batch te laten bijwerken naar jouw stats-db (vanuit de nu bestaande db).

Als je veel (voor jullie gestandaardiseerde) groupby-queries gaat uitvoeren (bijvoorbeeld samenvattingen per dag, per week, per maand), wat ik me goed kan voorstellen in deze situatie, kun je die samenvattingen ook in een batch laten bijwerken. Dus je hebt een tabel met de individuele recs, een tabel met dagtotalen, een tabel met weektotalen, etc.

Dat was ik inderdaad ook aan het denken.
Die voor de uur-tabel kan dan elk uur runnen, de dag tabel 's nachts, ...
We willen de realtime data uit onze db halen, want de bestaande db wijzigt regelmatig.
Als er teveel data in de tabellen komt, maken ze gewoon een nieuwe tabel aan.
Hierdoor voorkomen we dat onze rapportages niet meer werken.

[Verwijderd]

/me zwijgt en denkt terug aan de discussies over door 'ik ben handig met computers' zelfgebrouwen applicatie's voor professioneel gebruik

[Lukse]

Verwijderd schreef op woensdag 20 december 2006 @ 14:59:
/me zwijgt en denkt terug aan de discussies over door 'ik ben handig met computers' zelfgebrouwen applicatie's voor professioneel gebruik

Je hoeft niet te zwijgen. Geef je mening maar!

[Janoz]

Tja, uitgaande op de redelijk beperkte informatie die je al in dit topic gegeven hebt is al op te maken dat het hele DB model rammelt aan alle kanten. Nummeringen in kolommen? Dat is alvast een hele grote vlag die omhoog gaat. Daarnaast het 'alle maanden maar in een apparte tabel zetten want anders wordt hij zo groot' verhaal dat in principe niet zo heel veel uit maakt (index op datum heeft bijna hetzelfde effect). De manier van opslaan van tijdsduur.

Maar vooral dat een dergelijk redelijk belangrijke applicatie wordt toevertrouwd aan een ontwikkelteam dat er eigenlijk niet de ervaring voor heeft.

[Lukse]

Janoz schreef op woensdag 20 december 2006 @ 17:55:
Tja, uitgaande op de redelijk beperkte informatie die je al in dit topic gegeven hebt is al op te maken dat het hele DB model rammelt aan alle kanten. Nummeringen in kolommen? Dat is alvast een hele grote vlag die omhoog gaat.

Als je even terugleest, geef ik idd aan dat het DB model op niks trekt. Dat is juist het probleem.
Er is een apart team van 15 mensen dat deze structuur uitgedacht heeft. Wij hebben hier niets aan te zeggen, want deze structuur wordt gebruikt in 5 vestigingen over de hele wereld.
Daarom dat we in onze vestiging juist onze eigen structuur willen opzetten en hier zoek ik de beste manier voor.

Op de rest van je verhaal geef ik liever geen commentaar.

[Verwijderd]

OK,

Je wilt natuurlijk zo dicht mogelijk bij huidige structuur blijven,
dus als we daarvan uit blijven gaan zou ik volgende doen (ff snel verzonnen):
Tabellen erbij:
Product
id
descr
...

Unit
id
descr
...

Sample
agent_id
product_id
unit_id
timestamp
duration
...

Kan je al 's die 01,02,03 uit die data weghalen.

Plus je kan makkelijk "producten","soorten metingen" toevoegen zonder iedere keer je db aan te passen.

ff aanzetje, om specifieker te werk te gaan , zal je toch meer info moeten geven over de aard van de gegevens.
Zou eerst het model 's ff uitgaan werken, dan gaan nadenken over technische implementatie, problemen als performance, opslag grootte etc.

edit:

misschien dat je via triggers je eigen "db" kan vullen ipv vanuit een scriptje ?

edit:

Lees net "de db zal wel mysql worden", waarom als je toch al oracle heb liggen?

edit:

[quote]
Als er teveel data in de tabellen komt, maken ze gewoon een nieuwe tabel aan.
Hierdoor voorkomen we dat onze rapportages niet meer werken.
[/quote]
Wat voor speciale groep is dat wel niet,die dit hebben bedacht ?

[ Voor 44% gewijzigd door Verwijderd op 20-12-2006 18:31 ]

[Lukse]

Verwijderd schreef op woensdag 20 december 2006 @ 18:23:
ff aanzetje, om specifieker te werk te gaan , zal je toch meer info moeten geven over de aard van de gegevens.
Zou eerst het model 's ff uitgaan werken, dan gaan nadenken over technische implementatie, problemen als performance, opslag grootte etc.

Ik heb het misschien niet goed uitgelegd, maar het nieuwe model is al af hoor.
Ik ben gewoon uitgegaan van alle gegevens die ik nodig heb en heb die dan genormaliseerd.
Als je wil kan ik het hier wel even volledig neerzetten, maar ik denk niet dat dat nodig is.

Het gaat mij om, in jouw geval, de Sample tabel.
Die gaat enorm groot worden.
Mijn vraag is dus hoe dit het beste aangepakt kan worden.

misschien dat je via triggers je eigen "db" kan vullen ipv vanuit een scriptje ?

Is ook een goed idee, bedankt.

Lees net "de db zal wel mysql worden", waarom als je toch al oracle heb liggen?

Oracle wordt nu gebruikt. Maar daar hebben we enkel leesrechten op.
Deze kunnen we dus niet gebruiken om zelf gegevens in op te slaan.
We gaan dus iets nieuws moeten opzetten.
Het is nog niet beslist welke db het gaat worden, maar het mag eigenlijk "niets" (niet te veel) kosten.
Daarom dat we in de richting van MySql gingen.

Wat voor speciale groep is dat wel niet,die dit hebben bedacht ?

Kom je hun dat even vertellen? Want van ons nemen ze het niet aan...
Tja, het zijn nu eenmaal Duitsers...

[ID-College]

Ik zou eens even de manier van boycodd gebruiken voor je databaseontwerp.
Bepaal wat de RG is de 0NV, 1NV, 2NV enz en bedenk een duidelijke prim. sleutels en vreemde sleutels.
Als je lukraak maar wat gaat maken krijg je geheid reduntatie in je database

[ Voor 5% gewijzigd door ID-College op 20-12-2006 19:22 ]

[Lukse]

ID-College schreef op woensdag 20 december 2006 @ 19:22:
Ik zou eens even de manier van boycodd gebruiken voor je databaseontwerp.
Bepaal wat de RG is de 0NV, 1NV, 2NV enz en bedenk een duidelijke prim. sleutels en vreemde sleutels.
Als je lukraak maar wat gaat maken krijg je geheid reduntatie in je database

Geloof mij, het ontwerp is er, en is in orde!

[Verwijderd]

Lukse schreef op woensdag 20 december 2006 @ 19:17:
[...]
Ik ben gewoon uitgegaan van alle gegevens die ik nodig heb en heb die dan genormaliseerd.
Als je wil kan ik het hier wel even volledig neerzetten, maar ik denk niet dat dat nodig is.

ja, nu ben ik nieuwsgierig geworden natuurlijk

Het gaat mij om, in jouw geval, de Sample tabel.
Die gaat enorm groot worden.
Mijn vraag is dus hoe dit het beste aangepakt kan worden.

ah ok.
Ja, indien je de indexen goed legt, kan je makkelijk miljoenen records hebben.
Je zou dan idd kunnen denken om b.v. per jaar een db aan te maken, indien je de db niet te veel wilt laten groeien. Daarnaast eventueel voor snel (historisch) inzicht apart gemiddeldes opslaan (dag/week/maand/jaar).

[Lukse]

Verwijderd schreef op woensdag 20 december 2006 @ 19:26:
[...]

ja, nu ben ik nieuwsgierig geworden natuurlijk

Model komt er morgen aan

[...]

ah ok.
Ja, indien je de indexen goed legt, kan je makkelijk miljoenen records hebben.

Ook in MySQL?

Je zou dan idd kunnen denken om b.v. per jaar een db aan te maken, indien je de db niet te veel wilt laten groeien.

Is idd een mogelijkheid.

Daarnaast eventueel voor snel (historisch) inzicht apart gemiddeldes opslaan (dag/week/maand/jaar).

Maar dat maakt de db weer misschien dubbel zo groot, wat weer ten koste gaat aan de performance.

[Verwijderd]

Lukse schreef op woensdag 20 december 2006 @ 19:39:
[...]
Ook in MySQL?

heb zelf niet zo heel veel ervaring met mysql (behalve dat sommige cms'en erop draaien).
maar lijkt me toch geen probleem op zich.

Maar dat maakt de db weer misschien dubbel zo groot, wat weer ten koste gaat aan de performance.

zal ook meevallen, goed op de indexen letten (houdt in, indexen goed zetten en niet zomaar overal indexen op leggen, iedere index moet ook geschreven worden dus kost ook beetje performance).

[DiedX]

Lukse schreef op woensdag 20 december 2006 @ 19:39:
[...]

Model komt er morgen aan

Ik ben benieuwd

Ook in MySQL?

Yup. Ik zou wel een lekker systeem neerleggen, maar dat moet hij MAKKELIJK kunnen trekken.

[Verwijderd]

ID-College schreef op woensdag 20 december 2006 @ 19:22:
Ik zou eens even de manier van boycodd gebruiken voor je databaseontwerp.
Bepaal wat de RG is de 0NV, 1NV, 2NV enz en bedenk een duidelijke prim. sleutels en vreemde sleutels.
Als je lukraak maar wat gaat maken krijg je geheid reduntatie in je database

Leg 's uit? Zoeken op 'boycodd' gaf een interessante pagina results op, maar niks had te maken met database design. En wat de RG voor m'n xNV is of zou moeten zijn weet ik niet, want die afkortingen ken ik niet.

Overigens is bewust gekozen redundantie in je database helemaal niet evil. Het kan ervoor zorgen dat bij een representatie van zeg 40 regels bij 14 kolommen de GUI een stuk sneller en prettiger werkt voor de eindgebruiker. En wanneer die redundante velden worden bijgehouden door triggers op de tabellen die die velden moeten voeden, zie ik er niks kwaads in.

[Lukse]

Datamodel zoals het er nu uitziet:

...

[ Voor 44% gewijzigd door Lukse op 23-12-2006 08:10 ]

[DiedX]

Vertel, vertel

[Janoz]

Verwijderd schreef op woensdag 20 december 2006 @ 21:51:
[...]

Leg 's uit? Zoeken op 'boycodd' gaf een interessante pagina results op.

Zoeken op de juiste naam daarintegen Boyce-Codd Normal Form (BCNF).

En met betrekking op redundante data. Ik ben daar geen voorstander van. Als je het al gebruikt, zorg dan in ieder geval dat je duidelijke argumenten hebt die ook goed te onderbouwen zijn. Te vaak zie ik het onder het mom van 'Dan zal het vast een stuk sneller/beter/makkelijker werken' gebeuren terwijl dat eigenlijk nergens op wordt gebaseerd en ook niet is onderbouwd.

[Verwijderd]

Waarom MySQL gebruiken, als je gewoon Oracle Express (of SQL Express kan gebruiken). Zeker omdat de data uit Orcacle komt lijkt mij dat een stuk makkelijker werken cq overdragen binnen het bedrijf, als je voor Orcacle Express kiest.

Ik heb zelf weleens zo'n systeem gemaakt voor een call center en toen waren rapportages tot op een uur, gedetaileerd genoeg. Van elke week draaide we dan een wekelijkse rapportage, die de 'historie'-tabel vulde. Dit leverde aanzienlijk minder records op, namelijk 24uur*7dagen*<aantal agents>. Dit is dus maar 168 records per agent, per week.

De call agents en het management hadden daarbij nog de mogelijkheid om bij data van niet ouder dan een maand, door te klikken op de uren, waarbij dan elke call afzonderlijk gespecifeerd was. Na een maand was men hier niet meer in geïntresserd (dus waarom opslaan).

[Gomez12]

Idd ik zou het deels doen volgens het idee van RemyvD.
Ik zou in 1e instantie gewoon alle sample data laten lopen naar 1 grote verzameltabel ( / meerdere genormaliseerde verzameltabellen ) deze krijgen dan een paar miljoen records te verstouwen, boeiend ( mits je goede indexen hebt )
En in 2e instantie hulptabellen aanmaken ( "statistische" tabellen ) met daarin de groeperingen volgens de standaard uitlees methodes ( vb. mensen willen het aantal calls per uur zien, tabel met kolommetje datetimestamp en een kolommetje calls ). Dit levert heel erg kleine en supersnelle tabellen op die makkelijk in een dashboard gezet kunnen worden.

Je hulptabellen worden via een batchscript met een query gevuld ( niet triggeren, want een tabel met aantal calls per uur hoeft niet elke seconde getriggerd te worden, alleen maar 1x per uur bijgewerkt te worden, laatste uur kan je live uit je db halen ).

Zo kan je heel erg makkelijk een systeem maken waarbij mensen snel de totalen in beeld hebben ( uit de hulptabellen ) en ze kunnen altijd doorklikken naar losse calls ( door je complete sampletabel ) en het enige redundante wat er inzit zijn geaggregeerde gegevens. Maar dit heb ik praktisch nooit als echt redundante data beschouwd omdat het vooraf aggregeren gewoon een gigantische tijdswinst is.

[JaQ]

Lukse schreef op woensdag 20 december 2006 @ 11:06:
Als er nieuwe gegevens in de db komen, verdwijnen er oude. Er wordt voor een dikke maand data bijgehouden.

We hebben nu een project opgezet, waarbij we op een intranet site, realtime alle gegevens van een agent kunnen bekijken.
Een extra feature is nu, dat we ook historische data gaan laten zien: per uur, dag, week, maand, kwartaal, jaar.
Maar we lopen hier dus tegen die maand limiet op.

Het idee is nu om zelf een soort database aan te leggen, waar alle data gewoon in blijft staan.
Elke kwartier runnen we dus een script dat de laatste data ophaalt en wegschrijft in onze db.
Na verloop van tijd gaat dit dus enorm veel data worden, en we zoeken de beste manier om deze gegevens op te slaan.

Een collega stelt voor om dit in xml te doen, en dan op te delen in verschillende dirs per jaar, maand, dag, ... wat wil zeggen dat we xml files moeten gaan parsen, wat volgens mij enorm traag is met zoveel data.

Mij lijkt het beter om opnieuw een db te gebruiken, met een beter structuur (niet zo ranzig als hierboven ). Maar het probleem is dan dat een aantal tabellen enorm groot gaan worden, wat dus ook niet ten goede komt aan de performance.

Wat denken jullie dat het beste is? Opslaan als xml, of toch een db gebruiken?
Zijn er dan misschien manieren om die db te splitsen, zodat de data gesplitst kan worden?
Of zijn er andere technieken?

Ik denk dat het mooiste is om bij 1 bron te blijven. Je hebt al een rapport (toch?) en je hebt al een database. Sterker nog, je hebt al een het mooiste stuk techniek van Oracle Corp. in huis (het RDBMS). Dit RDBMS heeft een feature die "trigger" heet. Je zou natuurlijk maar zo eens je data kunnen kopieren naar een schaduw tabel voordat deze verwijderd wordt (before delete dus).

Door vervolgens een view(s) te maken die een union doet van de history tabel(en) en de originele tabel(en), en je bestaande rapportage niet meer naar de "originele" tabel(en) maar naar die view(s) laat kijken, ben je klaar.

Je hoeft op deze manier geen hoofdpijn te krijgen van een nieuw databaseontwerp (ondanks dat het huidige ontwerp misschien rammeld, maar zonder beter feature beschrijving denk ik niet dat wij dat zo kunnen inschatten). Het enige waar je wel goed naar zal moeten gaan kijken is ruimte en beschikbare rekenkracht. Je gaat je databaseserver zwaarder belasten en het is de vraag of je server hierop geschaald is. (je hebt hopelijk wel een fatsoenlijke Oracle DBA bij de hand?)

[ Voor 0% gewijzigd door JaQ op 20-01-2007 21:38 . Reden: er waren dus stiekem 2 pagina's :( ]

[Verwijderd]

Niks mis met goed gekozen redundante gegevens, vooral wanneer deze uitsluitend gevoed worden vanuit triggers op je data tabellen.