[db ontwerp] Verschillende tariefcalculaties

[storeman]

Ik ben bezig met een systeem waarin tarieven voor campers berekend moeten worden. De wereld van camperhuur is nogal vreemd. De sport is om steeds iets te vinden wat niet in het systeem past. Tijdens het ontwerp van de eerste versie is er dus een afweging gemaakt tussen flexibiliteit & data-complexiteit en bruikbaarheid. Ik ben me nu aan het oriënteren op een nieuwe database structuur. Heb hierover een paar vragen/discussiepunten (omdat het natuurlijk op meerdere manieren kan).

Camper tarieven

Een supplier heeft 1 of meer campers in zijn programma. Daarnaast beschikt deze supplier over meerdere locaties waar de camper opgehaald of afgeleverd kan worden. Er zijn meerdere suppliers die elk hun eigen methode hebben om de tarieven te berekenen. De ene methode bestaat uit codes, deze codes vertegenwoordigen tarieven. _{Elke week worden er nieuwe codes uitgegeven in een matrix, hierbij staat verticaal de camper en horizontaal de ophaallocatie, of ophaallocatie tegen inleverlocatie met per camper een ander blad. + uitzonderingen}
De andere methode zijn seizoenstarieven. _{Tarieven met randvoorwaarden: camper, ophaallokatie, ophaaldatum.}



Tot en met de camper tabelis alles nog hetzelfde, maar
Waar leg ik vast welk tarief-type er moet worden gebruikt?

Zelf denk ik dat ik dit gewoon vastleg in de campertabel met een kolom ratetype. Dit geeft het voordeel dat via de interface de keuze al gemaakt is bij de camper, of zelfs bij de supplier en dat de gebruiker dit niet verkeerd kan kiezen.

Aan de andere kant kun je zeggen dat dit gewoon moet blijken uit de beschikbare data, maar dat vergroot de kans op invoerfouten aanzienlijk.

Aanbiedingen

Als je denkt, waar stelt die jongen zich aan met ingewikkeld. Dan gaan we gewoon even verder . Bij de camper kunnen allerlei items worden geboekt. De tarieven varieren per seizoen hebben dus allen een beperking qua geldigheid die met datum worden aangegeven.

Voor de rest zijn er een aantal dingen die kunnen van invloed kunnen zijn op de prijs:

- ophaallokatie
- campertype
- per dag of vast bedrag (t/m x dagen vast, daarna y dollar per dag)
- inleverlokatie (bij route a -> b worden terugrijkosten berekend)
- afstand ( mijlpakketten )

Dit is de situatie nu ongeveer, maar ze bedenken zo weer wat anders. Juist als je denkt alle mogelijkheden te hebben afgedekt.

Hier is mijn afweging, maak in één tabel die alle voorwaarden denkt, waarbij de onnodige kolommen NULL blijven en dus relaties niet bestaan óf ga ik voor alle mogelijke variaties een aparte tabel maken. Hierin kan ik niet echt een helder e visie krijgen. Een stuk of 4 tabellen om je tarieven te defineren is ook niet echt optimaal.

Optie 1: 1 tabel


Optie 2: losse tabellen


Optie 1 heeft mijn voorkeur (KISS), maar ik het grootste probleem wat ik hier zie, is de optionele constraints. Kan iemand mij ervan overtuigen dat dit niet zo erg is?

Aanbiedingen

Een apart hoofdstuk waarvan ik niet eens weet of ik jullie daarmee lastig kan/mag vallen op dit tijdstip. Aanbiedingen worden op de meest onmogelijke combinaties aangeboden. Een aantal mogelijkheden die onderling soms ook nog gecombineerd worden:

- Korting als vast bedrag, als percentage, in huurdagen of gereduceerd tot een vast bedrag
- Verschilt in ophaal en inleverlocatie of camper
- Kies een x aantal items uit een set van y items
- Aanbieding A mag wel in combinatie met B maar niet met C
- 3 halen 2 betalen
- x aantal gratis

Ik kon zo 1 voorbeeldje vinden, helaas niet de meest complexe.

Vertrek in april, kies er één:
- Gratis one-way
- 50% korting op ongelimiteerde mijlen bij C30 campers, hierbij geldt de korting van 50% alleen op de eerste 21 dagen, de extra dagen worden volledig berekend.
- Gratis preparatie

Bonus: Gratis persoonskits bij vertrek tussen 1 - 19 april


Zoals je ziet een web van voorwaarden. Een persoon snapt dit nog eens, maar programmeer het maar eens. Met dit probleem verwacht ik geen oplossing, maar misschien kan iemand me een schop in de goede richting geven . Ik zelf zou de aanbiedingen in de meeste gevallen als een item zien, op dit moment worden items in de aanbieding gewoon nieuwe items met andere bedragen. Dit is prima te doen, maar hoe kan ik in de db-structuur vastleggen welke combinaties er mogen en belangrijker niet mogen.


Samenvattend al het bovenstaande:
- Laat ik de data zelf bepalen wat er geldig is of wordt dit door de gebruiker zelf gedefiniëerd door het vast te leggen in een hogere tabel?
- Hoe kan ik flexibel voorwaarden opgeven in combinaties van verschillende tarieven uit verschillende tabellen?

[pedorus]

Even een snelle reactie. Algemeen lijkt mij de naamgeving iets beter kunnen. Tabellen hoeven niet met t te beginnen en op 1 te eindigen, Hungarian notation is achterhaald wat mij betreft. Gebruik overal dezelfde naam voor id's en zet id achteraan (dus bijv. supplierId). In het schema is mij niet zo duidelijk welke keys nu waar naartoe verwijzen, en wat uniek moet zijn. Die pijlen staan nu helaas op tabelniveau.

Mij lijkt dat als het tarieftype gewoon kan blijken uit de data die er is, een extra kolom waarin dit nogmaals staat alleen maar redundantie oplevert in je datamodel. Dit leidt alleen maar tot meer mogelijkheden voor inconsistente data. Niet doen dus, gebruik gewoon een goede representatie van de data en goede constraints.

Bij de tariefstructuur heb ik niet genoeg informatie om er veel zinnigs over te zeggen. Wat is bijvoorbeeld Tioga en waar slaat die Add op? De optionele constraints worden niet echt beschreven. Het zal nogal schelen of er ophaallocatie*wegbrenglocatie*autotype*periode tarieven zijn, of dat je dat beter kan opsplitsen in losse tabellen. Waarschijnlijk is iets dat zo min mogelijk redundante data met zich meebrengt het handigst.

Bij de aanbiedingen denk ik dat je je moet afvragen of je dit niet gewoon beter in programmacode kan oplossen, in plaats van in je datamodel. (ja, 'hard coding' )

[RobIII]

storeman schreef op dinsdag 23 juni 2009 @ 23:13:
Waar leg ik vast welk tarief-type er moet worden gebruikt?

Je zegt het zelf al:

storeman schreef op dinsdag 23 juni 2009 @ 23:13:
Er zijn meerdere suppliers die elk hun eigen methode hebben om de tarieven te berekenen.

Bij de supplier dus. Maak een veld "calculationtype" of whatever, zet daar een code "a" of "b" in (of "x" of "y" ) en afhankelijk van dat type weet je welke berekenmethode je wil gebruiken.

storeman schreef op dinsdag 23 juni 2009 @ 23:13:
Dit is de situatie nu ongeveer, maar ze bedenken zo weer wat anders. Juist als je denkt alle mogelijkheden te hebben afgedekt.

Welkom in the real world Gebruikers (of opdrachtgevers) bedenken zich om de haverklap. Ga niet alles proberen te vangen; zorg voor een goede basis en bouw daar op verder als er wijzigingen optreden; je kunt vantevoren (zoals je zelf al aangeeft) niet verzinnen wat ze morgen weer verzinnen...

storeman schreef op dinsdag 23 juni 2009 @ 23:13:
Optie 1 heeft mijn voorkeur (KISS), maar ik het grootste probleem wat ik hier zie, is de optionele constraints. Kan iemand mij ervan overtuigen dat dit niet zo erg is?

Optionele constraints?
Je kunt toch gewoon een left join doen?

storeman schreef op dinsdag 23 juni 2009 @ 23:13:
Zoals je ziet een web van voorwaarden. Een persoon snapt dit nog eens, maar programmeer het maar eens.

Pfff; al eens verdiept in de varkens*handel? Dat is pas ellende (Trust me, ik heb er 3 jaar van mijn leven in opgeofferd om, net na mijn vertrek, het project gecancelled te zien worden op 90% completetion _{*Overigens deden we meer diersoorten dan varkens (die an sich al complex zijn) die elk weer hun eigen 'maniertjes' hadden...dus het is meer 'veehandel'})

storeman schreef op dinsdag 23 juni 2009 @ 23:13:
Met dit probleem verwacht ik geen oplossing, maar misschien kan iemand me een schop in de goede richting geven

Zoals ik al zei: probeer alles duidelijk uitgewerkt en in requirements op papier te krijgen en ga daar van uit; ga niet alles vooraf proberen te vangen maar zorg wel dat je geen puinhoop (gaandeweg) van je tabellen/model maakt. Ik vraag me een beetje af of, gezien de 'puinhoop' die de data/logica an-sich al is, je dit uberhaupt wel in een DB moet willen vangen; je zou misschien wel beter per leverancier (hoeveel zijn het er werkelijk?) een "dll"/"plugin"/"include"/whatever kunnen maken waarin je in de business logica de aanbiedingen/kortingen/whatevers programmeert. Met een "common base" (en interface) kun je natuurlijk veelgebruikte functies makkelijk delen. De data kun je, per leverancier, alsnog in tabellen gooien of, hell, voor mijn part in leverancier.xml/.dat/.txt/.bla bestanden. Ik vrees dat je anders op een vreselijke kluwen tabellen en bijbehorende code met alle uitzonderingen links en rechts komt en dat is, op den duur, ook niet echt onderhoudbaar meer.

[ Voor 4% gewijzigd door RobIII op 24-06-2009 00:49 ]

[Creepy]

En ook nog een tikje richting Software Engineering & Architecture

[storeman]

pedorus schreef op woensdag 24 juni 2009 @ 00:32:
Algemeen lijkt mij de naamgeving iets beter kunnen. Tabellen hoeven niet met t te beginnen en op 1 te eindigen...

De t heb ik voor gekozen zodat ik onderscheid kan maken tussen tabellen en views. Naamgeving is verder iets persoonlijks. Voor mijn model vind ik het logisch dat je supplier.id hebt, en niet supplier.supplierId. De 1 achter de tabelnamen zijn een fantastische functie van visio, leek met niet zo belangrijk voor de vragen.

pedorus schreef op woensdag 24 juni 2009 @ 00:32:
Mij lijkt dat als het tarieftype gewoon kan blijken uit de data die er is, een extra kolom waarin dit nogmaals staat alleen maar redundantie oplevert in je datamodel.

Ik zie hier toch niet echt een voordeel in en ik zou niet spreken van redundantie. Het is een flag, met die flag kun je juist op invoerfouten controleren, zonder die flag zou ik niet kunnen bepalen of het nu flex of seizoenstarieven betreft.

pedorus schreef op woensdag 24 juni 2009 @ 00:32:
Bij de aanbiedingen denk ik dat je je moet afvragen of je dit niet gewoon beter in programmacode kan oplossen, in plaats van in je datamodel. (ja, 'hard coding' )

Eensch, het is ook geen doen om dit in de tabel te plaatsen. Wellicht dat ik specifieke modellen hiervoor moet maken met rekenfuncties. Inderdaad gebaseerd op een abstract class of interface.

RobIII schreef op woensdag 24 juni 2009 @ 00:41:
Optionele constraints?
Je kunt toch gewoon een left join doen?

Ja, uiteraard

RobIII schreef op woensdag 24 juni 2009 @ 00:41:
[...]

Zoals ik al zei: probeer alles duidelijk uitgewerkt en in requirements op papier te krijgen en ga daar van uit; ga niet alles vooraf proberen te vangen maar zorg wel dat je geen puinhoop (gaandeweg) van je tabellen/model maakt. Ik vraag me een beetje af of, gezien de 'puinhoop' die de data/logica an-sich al is, je dit uberhaupt wel in een DB moet willen vangen; je zou misschien wel beter per leverancier (hoeveel zijn het er werkelijk?) een "dll"/"plugin"/"include"/whatever kunnen maken waarin je in de business logica de aanbiedingen/kortingen/whatevers programmeert. Met een "common base" (en interface) kun je natuurlijk veelgebruikte functies makkelijk delen. De data kun je, per leverancier, alsnog in tabellen gooien of, hell, voor mijn part in leverancier.xml/.dat/.txt/.bla bestanden. Ik vrees dat je anders op een vreselijke kluwen tabellen en bijbehorende code met alle uitzonderingen links en rechts komt en dat is, op den duur, ook niet echt onderhoudbaar meer.

Jep, je ligt in lijn met pedorus, belangrijk is inderdaad om te weten wat er mogelijk moet zijn.

[pedorus]

storeman schreef op woensdag 24 juni 2009 @ 11:04:
De t heb ik voor gekozen zodat ik onderscheid kan maken tussen tabellen en views. Naamgeving is verder iets persoonlijks. Voor mijn model vind ik het logisch dat je supplier.id hebt, en niet supplier.supplierId. De 1 achter de tabelnamen zijn een fantastische functie van visio, leek met niet zo belangrijk voor de vragen.

Ik had het vooral over idSupplier, maar echt boeiend is dit niet. Dat probleem met die eentjes ken ik trouwens niet van Visio.

Ik zie hier toch niet echt een voordeel in en ik zou niet spreken van redundantie. Het is een flag, met die flag kun je juist op invoerfouten controleren, zonder die flag zou ik niet kunnen bepalen of het nu flex of seizoenstarieven betreft.

Ik dacht bijvoorbeeld aan een view die gebruikt maakt van EXISTS.

SQL:

select ..., exists (select * from FlexRates where ...) as isFlexRate from ...

Echt veel scheelt het trouwens niet natuurlijk, en het hangt er ook vanaf of de flag 'los' kan bestaan ("Ik zeg alvast dat het deze tariefsoort is, maar voer de tarieven pas later in" is op deze manier wat lastig). Voor de rest lijkt het mij dat het programma er zoveel mogelijk voor moet zorgen dat fout invoeren simpelweg niet kan.