[mysql] database ontwerp kostencalculatiesysteem

[blade181]

Even een korte toelichting alvorens ik het probleem uitleg

Voor het bedrijf waar ik werk heb ik een opzet gemaakt en geimplementeerd van een kostencalculatiesysteem. Het is een compleet systeem waarin kosten die machines en werkplekken maken uiteindelijk leiden tot een feitelijke winst en verliesrekening en kostenverdeelstaat. Energie, personeelskosten, gereedschapskosten, onderhoudskosten, hulpstofkosten en noem maar verder op worden per machine gedefinieerd en vervolgens aan de hand van het aantal draaiuren omgezet in kosten per werkplek. Dit resulteerd in kosten die in feite per machine gedefinieerd zijn (ik noem ze eerst belaste kosten).

Het probleem ligt in het volgende deel. Aan alle machines is een kostenplaats toegewezen, voor diegene die bekend is met het kostenplaatsmethode, kosten van machines vallen onder een hoofdkostenplaats omdat machines in feite aan de grondslag liggen van het maken van producten, je hebt ook nog hulpkostenplaatsen, dit zijn kostenplaatsen zoals huisvesting of afdelingen zoals een afdeling directie die in feite niets met productie te maken hebben maar er bijvoorbeeld wel sturing aangeven. Alle kosten die gemaakt worden bij hulpkostenplaatsen worden daarom doorbelast naar hoofdkostenplaatsen of de kostendrager (het product).

Nu komt het feitelijke probleem, om door te belasten heb ik een tabel ingericht waarin regels beschreven staan die aangeven waar een doorbelasting vandaan komt (bron kostenplaats en bron divisie) en waar het naar toe gaat (doel kostenplaats en doel divisie) en voor hoeveel procent van de kosten door wordt belast. Daarmee kan ik dus doorbelasten van de een naar de andere kostenplaats de tabel:

tabel doorbelastingregels
doorbelastingregelsID
bron kostenplaatsID
bron divisieID
doel kostenplaatsID
doel divisieID
percentage

De kosten die gemaakt zijn door machines (lees: eerst belaste kosten) worden in de volgende tabel gestopt:

tabel eerst belaste kosten
eerstebelastekostenID
kostenplaatsID
divisieID
grootboekrekeningID
bedrag
kostensoort (vast of variabele kosten)

Aan de hand van de regels en de eerst belaste kosten, kan ik dus kosten verschuiven van de ene naar de andere kostenplaats. Deze worden gestopt in de tabel doorbelaste kosten

tabel doorbelaste kosten:
doorbelaste kostenID
Bron kostenplaatsID
Bron divisieID
Doel kostenplaatsID
Doel divisieID
grootboekrekeningID
bedrag
kostensoort

Nu ontstaat bij mij het volgende probleem, hierbij worden wel fictieve gegevens gebruikt

Ik heb een afdeling huisvesting op kostenplaats 2000 waar 500 € aan huur voor gebouwen worden geboekt en ik heb een afdeling directie op kostenplaats 1000 waar 1000€ aan personeelskosten worden geboekt en ik heb een afdeling fabricage (machine) op kostenplaats 3000 waar kosten uiteindelijk op terecht komen (eindstation als het ware) en die 1000 € aan energiekosten heeft.

Een voorbeeld:
Ik heb een doorbelastingregel die beschrijft een doorbelasting van kostenplaats 2000 naar 1000 voor 50% of €500.
Vervolgens heb ik een regel die beschrijft een doorbelasting van kostenplaats 1000 naar 3000 voor 100% ofwel 500€ + 500€ = 1000 €

Uiteindelijk komen er aan totaal kosten op kostenplaats 3000: 1000€ (energiekosten eerst belast) + 500 € (kostenplaats 2000) + 500€ (kostenplaats 1000) = 2000 €

HET PROBLEEM

De kosten die doorbelast worden van kostenplaats 2000 naar 1000 moeten meegenomen worden in de doorbelasting van 1000 naar 3000.

Mijn vraag: Hoe kan ik daarop mijn ontwerp in mysql aanpassen zodat ik op een redelijk eenvoudige manier dit voor elkaar kan krijgen.

Wat heb ik tot nu toe gedaan. Views gemaakt van gekoppelde tabellen van eerst belaste en doorbelaste kosten.

Wat ik weet is dat de doel kostenplaats bij een doorbelastingregel gelijk moet zijn aan de bron van de volgende doorbelastingregel. Als dat zo is dan heb je dus te maken met een dubbele doorbelasting en moet je die kosten meenemen. Vervolgens kun de kosten die voorkomen in de view meenemen in de doorbelasting. Alleen was het maar zo simpel, ik krijg het niet voor elkaar op dit moment. Sommige kosten neemt die niet mee, andere weer wel. Ik weet dat dit lastige materie is, maar wellicht kunnen mensen advies geven hoe ik dit probleem kan oplossen.

[Orphix]

Wil je het runtime bepalen? Of moeten de resultaten in een tabel komen?

In het laatste geval zou ik een resultatentabel maken met daarin de (tijdelijke) doorbelaste gegevens per kostenplaats. Vervolgens ga je net zolang je doorbelastingen uitvoeren (op die resultatentabel) totdat er geen doorbelaste kosten meer worden teruggegeven vanuit je query. Immers, als alle kosten bij de 'eind' kostenplaats staan opgeteld ben je klaar. Er worden dan, als het goed is, geen doorbelastingregels meer getriggered.

[blade181]

Orphix schreef op donderdag 07 mei 2009 @ 16:54:
Wil je het runtime bepalen? Of moeten de resultaten in een tabel komen?

In het laatste geval zou ik een resultatentabel maken met daarin de (tijdelijke) doorbelaste gegevens per kostenplaats. Vervolgens ga je net zolang je doorbelastingen uitvoeren (op die resultatentabel) totdat er geen doorbelaste kosten meer worden teruggegeven vanuit je query. Immers, als alle kosten bij de 'eind' kostenplaats staan opgeteld ben je klaar. Er worden dan, als het goed is, geen doorbelastingregels meer getriggered.

De resultaten komen op dit moment in een tabel terug. Dit doe ik doormiddel van procedures die ik vervolgens trigger. Net zo lang doorbelastingen uitvoeren, betekent dat je resultaten uit 1 tabel gaat verplaatsen naar een andere tabel en de doorbelastingen stoppen zodra de ene tabel leeg is? Hoe zou je dan rekening houden met doorbelastingen die als tussenstation fungeren en daarna nogmaals worden doorbelast?

[Alain]

Ik hoop dat ik je uitleg goed begrijp.

Ik zie drie entiteiten, namelijk Kostenplaats, Doorbelasting en een Kostenregel. (Let op dat ik met regel iets anders bedoel als jou). De tabellen zouden er (ik heb wat info uit jou model weggelaten) zo uitzien:

Kostenplaats:

id (pk)
naam
totale kosten (afleidbaar)

Doorbelasting:

bron kostenplaats (pk)
doel kostenplaats (pk)
percentage

Kostenregel:

kostenplaats (pk)
volgnr (pk)
bedrag

Hierbij moet je stellen dat een kostenplaats via doorbelasting nooit bij zichzelf uit mag komen.

Als je dan een trigger op de insert in kostenregel zet om de totale kosten van de kostenplaats te updaten. Daarnaast een trigger op het updaten van totale kosten die de totale kosten van de parent update. Op die manier weet je altijd de totale kosten die geboekt zijn op de kostenplaats. De kosten die op de kostenplaats geboekt zijn kun je uit de tabel Kostenregel halen.

Als je meer informatie over doorbelastingen wilt weten kun je de trigger op het updaten van totale kosten gebruiken om de doorbelasting op te slaan als historie.

Edit:

Ik heb net even snel een testcase gemaakt en een recursieve trigger gaat niet werken. Ik denk dat je vier tabellen nodig hebt om dit probleem te tackelen, dus nog een tabel met de historie van doorbelastingen, zodat je de totale kosten kunt afleiden.

[ Voor 10% gewijzigd door Alain op 15-05-2009 00:12 ]