[SQL/ALG] databaseopzet tbv kostenregistratie

[pkouwer]

Stel ik heb de volgende gegevens van een telefoongesprek: netlijn waarover gekletst is, duur van het gesprek, telefoonnummer waar naar toe is gebeld en wat het eigen netnummer is.

Nu wil ik middels een applicatie hier een prijskaartje aan hangen. Ik ben er al enkele dagen mee bezig om dit voor elkaar te krijgen, maar het wil maar niet vlotten.

Wat is het probleem: ik kan niet op papier krijgen hoe ik het best de database kan opzetten. Ik heb de volgende indeling bedacht:

[tabel gesprekken]
id
netlijn
duur (sec)
telefoonnummer

[netlijnen]
id
omschrijving/provider
profiel

[profiel]
id
omschrijving
startdatum
starttijd
einddatum
eindtijd
starttarief
gesprekstarief
opmerkingen

het probleem is in deze dat ik niet (goed) weet waar ik de richtingen moet plaatsen en hoe dit te koppelen met de rest. Een gesprek van Vlissingen naar Groningen is duurder dan vanUtrecht naar Hilversum, bij wijze van spreken.

Heeft iemand hier iets van ervaring mee ? Ik wil het dus zo eenvoudig mogelijk maken, dus dat als KPN (weer eens) besluit de tarieven in de lift te gooien, de klant niet alle 100.000 netnummer langs hoeft om dit handmatig aan te passen. Per gesprek moet er dus ook gekeken worden naar het tijdstip van het gesprek (daltarief etc.)

Het klink misschien wat wazig, maar ik kan het niet anders verwoorden

[P_de_B]

Hoe weet je welke gesprekken duurder zijn? Kun je dit op basis van afstand te doen of heb je een matrix met deze gegevens?

[Remco]

waarom vraag je bij kpn geen datagegevens op inclusief kosten.
Hier ben ik toevallig ook bij een andere klant mee bezig.
Deze gegevens worden dan aangeleverd door Versatel, en er staat ook een geprekskosten veld bij.

[Janoz]

De kosten zijn afhankelijk van het tijdstip en of het binnen de eigen regio (of omliggende) ligt of erbuiten. Waneer je in een tabel op hebt geslagen welke netnummers aanliggend zijn kun je zo achter halen of een telefoon gesprek voor het binnen eigen regio tarief was of niet. Op dat moment heb je alleen een tarief per periode (dal piek) en binnen/buiten de eigen regio. Dat zijn er een stuk minder dan de door jou genoemde 100000.

[P_de_B]

Janoz schreef op 03 december 2003 @ 09:53:
De kosten zijn afhankelijk van het tijdstip en of het binnen de eigen regio (of omliggende) ligt of erbuiten. Waneer je in een tabel op hebt geslagen welke netnummers aanliggend zijn kun je zo achter halen of een telefoon gesprek voor het binnen eigen regio tarief was of niet. Op dat moment heb je alleen een tarief per periode (dal piek) en binnen/buiten de eigen regio. Dat zijn er een stuk minder dan de door jou genoemde 100000.

Dat dacht ik ook maar TS zegt dat van Vlissingen naar Groningen (= buiten regio) duurder is dan Utrecht naar Hilversum (= volgens mij ook buiten regio). KPN maakt zelf dit onderscheid niet, maar TS blijkbaar wel, vandaar mijn vraag.

[Verwijderd]

He Hallo,

(Voor studentenhuis, bellen na pincode)
Ika heb ooit een appart programma aangekocht via PINCOM dit is een programma wat die de volgende dingen registreerd:

Datum, tijdstip, gekozen telefoonnummer, gebruiker,

Dit kon je heel erg makkelijk opsplitsen via de online website van KPN door hier het EXCEL bestand vandaan te halen.

Het programaatje bestaat uit 2 delen:

1e staat op een diskette en registreerd alles (staat dag en nacht aan) en is gekoppeld aan een modem. met in en uitgang

2e is de administrator. Deze matcht alles. En klaar

---------------------

Laat ik er hierbij van uitgaan dat je het toch zelf wil proberen:
Tip:
Neem een preselectcarrier: www.bel1601.nl deze heeft maar 1 tarief. Namelijk halve eurocent, piek, dal, lokaal en interlokaal. Deze heeft ook telefoonrekening online staan. Makkelijk te converteren naar Excel.

Succes!!


Keizer

[pkouwer]

precarier zalwel werken, maar hoe ik krijg mijn klanten niet zover.

Ik bedoel met Utrecht-Hilversum te zeggen dat dit binnen de eigen regio is. De gesprekskosten zijn dan lager.

Het moet ook nog eens uit te splitsen zijn in netlijnen, de meeste zullen via KPN lopen, andere via Scarlet oid, een preselect gedoe. Dan zijn de kosten weer anders. Het moet door de klant in te stellen zijn, want een kantoor dat inzage wil hebben in de kosten heeft geen toeslag t.o.v. een hotel wat de kosten moet doorrekenen aan de gasten

[Remco]

Ik zou een tabel maken die alle data uit andere tabellen verzameld (lijnnummer, tijdsduur, kosten etc.) en daarin wegschrijft.
Je krijgt dan 1 record met alle gegevens.
Hier kan je dan je rapporten uit maken.
Als je namelijk de kosten wijzigd dan zal de historie nog gebruik maken van de oude kosten.
Als je dit niet doet, en de kosten wijzigen, dan zullen ook je kosten wijzigen in je historie.

[Verwijderd]

Ik zou zelf iets maken van:

Een invoerveld met: Kengetalselectie
Alle regionale codes. (voor lokale tarieven) $Regiocodes
In mijn geval:

013
0497
0488

Dan kun je zoiets maken van:

Inbeltik X (Regiocode / interlokaal ) X tijdsduur = totale kosten gesprek

Kengetalselectie maken:

Regionale codes (alles zonder kengetal gekozen en met 013/0497/0488)
Mobiel (alles met 06)
internationaal (alle met 00)


--------------------------------------
Uiteindelijk zou een klant door met de volgende variabelen in te voeren het overzicht kunnen zien:

Alle regiocode:
Tijdstip:
Tarieven: dal/piek/
Verbindingstik: Piek = ..... bij tijdstip en dal= bij tijdstip

Enige andere variabele waar je nog mee zit is gesprekstarief:
lokaal en interlokaal piek en dal, meestal 4 tarieven.

Internationaal: Inporttabel vanuit website of zoiets???

[JaQ]

ben ik nou gek, of is dit gewoon niet zo spannend.

Je hebt je CDR (Calling Detail Record), waar als het goed is de volgende gegevens in staan:
1. bron (bijvoorbeeld user-id, toestel-id oid)
2. gekozen nummer
3. start datum + tijd
4. eind datum + tijd

Vervolgens moet je een nummer gaan categoriseren naar een tarief. Dat tarief is afhankelijk van het netnummer + de tijd (let op, als een gesprek van 19.50 tot 20.10 duurt, dan is 10 minuten in het laag tarief en 10 minuten in het hoogtarief). Je moet je cdr's dus gaan "platslaan"

De categorisatie van de netnummers kijkt dus eerst naar de lengte van het nummer (lengte > 8 --> altijd lokaal, anders misschien hoog tarief). Vervolgens discard je de (gratis) 0800 nummers (die zijn toch gratis?) en zet je de "dure" 0900 nummers appart (die hebben een wissellend tarief.. of was 0800 en 0900 toch andersom.. doe daar iets stoers mee in ieder geval. Je kan daar de tarieven nooit automagisch van berekenen).

Wat rest is een kort lijstje met "lokale" netnummers vergelijken met de netnummers van de overgebleven nummers. Lijkt me niet zo spannend.

"stored aggregations" zijn bij dit soort progjes je vriend, oftewel: sla je subtotalen / totalen gewoon op in een tabel. Wat nou elke keer on-the-fly opnieuw maken (wordt erg lullig als je het over 80.000 CDR's per uur gaat verwerken ) Het rekensommetje dat overblijft (tabelletje met tarieven anyone?) lijkt me ook een geval appeltje eitje.

success in ieder geval, en laat even weten of het gelukt is.

_{vind ik dit nou makkelijk omdat dit ik dit eerder gebouwd heb voor een ISP om kickback op telefoonkosten uit te rekenen, of is het gewoon echt zo gemakkelijk?}

[pkouwer]

dat zal dan wel appeltje eitje zijn, maar heb je ook rekening gehouden met het feit dat netlijn 1 andere tarieven heeft dan netlijn 23 ? En dat als je van Vlissingen naar Groningen belt in het daltarief ? Heb je dat ook meegenomen. Als het zo eazzzzzy is, hoe ziet je databasestructuur er dan uit ?

[pkouwer]

DrFrankenstoner schreef op 03 december 2003 @ 19:00:

De categorisatie van de netnummers kijkt dus eerst naar de lengte van het nummer (lengte > 8 --> altijd lokaal[/sub]

Bull-shit, hangt af van de telefoonautomaat en het netnummer, bv 078 en 0118 ??

[JaQ]

pkouwer schreef op 03 december 2003 @ 19:48:
dat zal dan wel appeltje eitje zijn, maar heb je ook rekening gehouden met het feit dat netlijn 1 andere tarieven heeft dan netlijn 23 ? En dat als je van Vlissingen naar Groningen belt in het daltarief ? Heb je dat ook meegenomen. Als het zo eazzzzzy is, hoe ziet je databasestructuur er dan uit ?

netlijn 1 andere tarieven dan netlijn 23? leg eens uit? je hebt volgens mij (buiten dure betaallijnen en buitenlandse lijnen) 6 mogelijkheden:
- lokaal + daltarief
- lokaal + piektarief
- interlokaal + daltarief
- interlokaal + piektarief.
- mobiel + daltarief
- modbiel + piektarief

Dit kan eventueel complexer gemaakt worden door het gebruik van een instantie als tele2, maar het lijkt me dat je dat ook kan afleiden uit het gekozen telefoonnummer?

Je datamodel is dus iets als dit:

klanten, je kan zelf vast wel wat bedenken, met een leuk ID-tje als PK:

klant-lijnen (als je dus meer lijnen per klant wilt hebben) klant_id, lijn_id

cdr: start_datestamp, end_datestamp, UID, called_number
(waarbij UID dus een unieke identificatie van de "beller" is. een customer id, een lijn-id, wat jij wil).

tarieven: id, name, tarrief, omschrijving

lokale_netcodes: klant_id (of lijn_id bij meer lijnen per klant) netcode

Vervolgens hou je volgens mij enkel een tabel over:
UID, begindatum, einddatum, totaal_tijd_piek, totaal_tijd_dal, totaal_tijd_mobiel, totaal_tijd_onbekend, totaal_bedrag_piek, totaal_bedrag_dal, totaal_bedrag_mobiel

Tot slot heb je een relatief slimme functie nodig die dus bekijkt wanneer iets piek of dal tarief is (dus met datums gaan rommellen, ma-vrij tussen 8 en 20 is piek, rest is dal). en een wat minder slimme functie die dus kijkt of een nummer lokaal, interlokaal, mobiel of onbekend (buitenland oid dus) is. Daarvoor gebruik je dus die lokale_netcodes tabel.

edit:

toevoeging: denk er om dat je niet zomaar datums + tijden kan optellen en aftrekken van elkaar. (ja, een gesprek kan natuurlijk van 23.50 tot 00.50 zijn, daarom dus een datestamp opslaan). Zelf vind ik het altijd het handigste om e.e.a. om te rekenen naar seconden (want daar kan je wel gewoon mee rekenen), of te kiezen voor een database met het type interval (zoals postgresql).

Je gaat dus wel hardstikke nat als de piek en dal tijden veranderen. Als je apparte tarieven per klant wilt gaan handhaven, maak je dus een koppeltabel tussen klanten en tarieven. De bovengenoemde tarieffunctie selecteerd dus dan eerst het van toepassing zijnde tarief nummer uit deze tabel.

Dit i.c.m. de eerder genoemde manier om een nummer te kunnen identificeren is volgens mij je software, of begrijp ik gewoon niet wat je wil/zoekt?

pkouwer schreef op 03 december 2003 @ 19:50:
[...]
Bull-shit, hangt af van de telefoonautomaat en het netnummer, bv 078 en 0118 ??

ow.. ik dacht dat alle (behalve 0800 en 0900 nummers) binnenlandse nummers 10 cijfers waren (inclusief netnummer dus). Ook meen ik dat je al jaren niet voor je eigen netnummer het netnummer hoef te zetten en dat het kortste netnummer in nederland 3 cijfers is. 10 - 3 = 7 (als ik mij niet vergis), dus alle nummers die 7 cijfers of korter zijn, zijn per definitie lokaal. Misschien dat ik dus al jaren niet zit op te letten, misschien zijn mijn analystische kwaliteiten ook niet wat ik zou hopen, misschien is databases ontwerpen en bouwen eigenlijk wel niet wat ik zou moeten doen voor mijn brood en misschien is het niet handig om dingen bull-shit te noemen voordat je er goed over hebt nagedacht.

En wat is een telefoonautomaat eigenlijk?

[ Voor 10% gewijzigd door JaQ op 03-12-2003 20:44 ]

[pkouwer]

DrFrankenstoner schreef op 03 december 2003 @ 20:27:
[...]


En wat is een telefoonautomaat eigenlijk?

een handig apparaatje waar je je telefoonlijnen en toestellen aan knoopt ??

daarnaast heb je nog buitenlandse gesprekken, preselectcariers waarbij het niet eens noodzakelijk is om 1602 oid voor te draaien...

[ Voor 23% gewijzigd door pkouwer op 03-12-2003 21:17 ]

[JaQ]

pkouwer schreef op 03 december 2003 @ 21:14:
[...]


een handig apparaatje waar je je telefoonlijnen en toestellen aan knoopt ??

daarnaast heb je nog buitenlandse gesprekken, preselectcariers waarbij het niet eens noodzakelijk is om 1602 oid voor te draaien...

een telefoonautomaat is dus een soort centrale? (als ik zoek op google krijg ik wel 3 pagina's resultaten, maar geen definitie oid van zo'n ding).

Anywayz, buitenlandse gesprekken beginnen altijd met 00 (en dan nooit een 8 of 9), ook nogal herkenbaar. Als iemand klant een preselectcarier gebruikt, dan weet jij dat en kan je dat dus openmen in de tarieven van die specifieke klant. Zo complex is dat nou ook weer niet.

Kan je trouwens nog meer uitzonderingen bedenken die ik voor je uit moet schrijven, of denk je dat je er zo wel bent

[pkouwer]

telefoonautomaat/centrale als het beestje maar een naam heeft. Al iemand het heeft over een PC weet jij toch ook dat ht om een computer gaat

Anyway, als in een nummer draai vanuit mijn kantoor, bv 078-xxxxxx kun je aan het call record niet zien of he via tele2 of Scarlet verloopt. Dit wordt in de centrale geregeld. Waarom ? de eindgebruiker hoeft alleen maar 078-xxxxx te draaien, zonder die iri 1602 !

daarnaast had ik de splitsing van lokaal,mobiel,buitenland al lang uitgedokterd voor ik hier kwam. Als je eens goed leest, dan LEES je dat het hier niet om te doen is. Vraag was en blijft hoe om te gaan met de verschillende netlijnen,richtingen (lokaal,etc) en piek/dal.

Ik krijig een beetje de indruk dat je het een beetje flamen is in mijn richting. LAAT DAT

[JaQ]

pkouwer schreef op 04 december 2003 @ 07:43:
telefoonautomaat/centrale als het beestje maar een naam heeft. Al iemand het heeft over een PC weet jij toch ook dat ht om een computer gaat

Anyway, als in een nummer draai vanuit mijn kantoor, bv 078-xxxxxx kun je aan het call record niet zien of he via tele2 of Scarlet verloopt. Dit wordt in de centrale geregeld. Waarom ? de eindgebruiker hoeft alleen maar 078-xxxxx te draaien, zonder die iri 1602 !

daarnaast had ik de splitsing van lokaal,mobiel,buitenland al lang uitgedokterd voor ik hier kwam. Als je eens goed leest, dan LEES je dat het hier niet om te doen is. Vraag was en blijft hoe om te gaan met de verschillende netlijnen,richtingen (lokaal,etc) en piek/dal.

Ik krijig een beetje de indruk dat je het een beetje flamen is in mijn richting. LAAT DAT

nofi, really. Ik kreeg alleen de indruk dat je op zoek was naar een compleet uitgeschreven verhaal. (maar dat kan aan mij liggen).

Het gaat je uiteindelijk dus om datamodel voor een splitsing van tarieven voor eindklanten als ik het goed begrijp? Dan zou je volgens mij met een 5-tal tabellen toe kunnen:
1. klanten (daar heb je zelf vast al een heel mooi tabelletje voor gemaakt).
2. tarief (id, omschrijving, bedrag): Deze gebruik je voor het vastleggen of het een mobiel, piek, dal, dal-via-tele2 etc. tarief is
3. klant-tarieven: klant_id, tarief_id
4. netnummers (netnummer,plaats)
5. lokale_netnummers (netnummer,netnummer)

Als je de software niet voor heel nederland wilt intzetten, kan je tabel 5 weglaten en in tabel 4 enkel de netnummers die "lokaal" zijn toevoegen. Indien je dit pakketje voor heel nederland beschikbaar wilt stellen heb je dus een netnummertabel nodig van heel nederland. Volgens mij is die wel te vinden op het net, ik weet in ieder geval zeker dat de netnummertabel te koop is bij KPN.

Dan blijft enkel het probleem piek en dal over. Over het algemeen zijn dat constructies als: ma-vrij tussen 8 en 5 is dal, de rest is piek. Dat is nogal klote (lees: praktisch onmogelijk) om in een tabel te zetten. Dat zou ik persoonlijk ook niet doen (want elke oplossing die je daar voor bedenkt, kan door een wijziging in die tijden/dagen structuur volledig obsolete blijken te zijn). Inbakken in de software dus.

Goed, deze slow-chat gaat op deze manier niet echt handig. Als je denkt dat ik je eventueel nog verder kan helpen: mail adres / msn adres / icq nummer staat wel in mijn profiel.