[Database] opzet voor transactie- / balanssysteem

[CyBeRSPiN]

Achtergrond
Ik ben al enige tijd aan het brainstormen over hoe ik het beste een webapplicatie voor onze huisrekening (studentenhuis) kan bouwen. We schrijven gezamelijke kosten op (bijv. eten) en eens in de maand voer ik dat in een mooie Excelsheet in zodat duidelijk wordt wie wat aan wie moet betalen om de boel weer gelijk te trekken. Dat invoeren wil ik automatiseren, iedereen vult meteen digitaal in wat er uitgegeven is aan wie, zodat de totalen continue duidelijk zijn.
Beetje ala www.eetlijst.nl of www.buxfer.com.
Iedereen start met 0 euro, iemand die betaalt stijgt, iemand die gebruikt daalt. Ik hoop dat het een beetje duidelijk is, de meeste (ex-)studenten zullen het wel kennen.

Probleem
De balans bestaat uit transacties van een persoon (de betaler) naar een of meerdere huisgenoten (de gebruikers), waarbij elke gebruiker 1 of meer keer "gebruikt" (bijv. een gast die mee-eet).

Hoe zet ik de database het beste op?
Alle transacties moeten terug te zien zijn en aanpasbaar zijn, en de balans moet ten alle tijden in evenwicht zijn (de som moet 0 zijn).

Mijn eerste idee (had er meer, maar die waren te complex):
3 tabellen (los van user en overige systeemtabellen)
-een transactietabel (datum, omschrijving, huisgenoot_id (koper), bedrag)
-een gebruikerstabel (transactie, huisgenoot_id (gebruiker), aantal)
-een balanstabel (transactie, huisgenoot_id, bedrag)

Voor elke transactie komt er dus 1 entry in de transactietabel, #gebruikers entries in de gebruikerstabel en de balanstabel.

Ik wil eigenlijk zo min mogelijk verantwoordelijkheden in het DBMS kwijt, het kan handig zijn met triggers en constraints, maar ik wil het liefst gebruikmaken van een J2EE framework en geloof dat dit dan in de weg gaat zitten.

Is deze opzet wel handig? Er zijn continu 3 joins nodig voor elk overzicht of wat dan ook, en ik vrees een beetje voor aanpassingen van een transactie, hoe zorg ik dat de transactie in zijn geheel aangepast wordt. Moet ik dat toch afdwingen met de DBMS (stored procedures/triggers) of kan dit beter in de code?

[Voutloos]

CyBeRSPiN schreef op vrijdag 25 januari 2008 @ 17:32:
Er zijn continu 3 joins nodig voor elk overzicht of wat dan ook

Als je model verder mooi is is 3 joins zo eng nog niet. Helemaal nooit joinen en een relationele db geburiken, dat is pas suf.

en ik vrees een beetje voor aanpassingen van een transactie, hoe zorg ik dat de transactie in zijn geheel aangepast wordt.

Met transactions

[CyBeRSPiN]

Voutloos schreef op vrijdag 25 januari 2008 @ 18:33:
Met transactions

Ja duh Maar hoe pak je dat aan, in theorie zouden meerdere (web)users dezelfde transactie kunnen wijzigen, moet je dan een transactie locken oid? Ken alle principes wel, maar weet even niet hoe ze het beste toe te passen hier.

En qua database is het dus wel handig om de afgeleide transacties op te slaan in een balans tabel? Het is een soort van redundante data, omdat uit de uitgave en de gebruikers valt af te leiden wat de stand van de balans is.

Nog een probleem: dat de som van de balans 0 moet zijn is een hele mooie check, echter, door afrondingen kan dit afwijken. Welke datatypen kan ik het beste gebruiken? En is het handig om elke transactie al "mooi" af te ronden? Zodat je bijv niet 3x 0.33333333 hebt maar 2x 0.33 en 1x 0.34?

[Voutloos]

1) Heb je nou wel of niet door dat transaction een db-term is?
2) Is redundant, maar kan wel een leuke optimalisatie zijn, maar daar moet je meer argumenten voor hebben.
3) Dan gebruik je een datatype dat wel geschikt is. Currency, Decimal, of desnoods een integer waar je aantal centen in opslaat. Is heel veel over geschrevn.

[Verwijderd]

CyBeRSPiN schreef op vrijdag 01 februari 2008 @ 16:01:
[...]
Nog een probleem: dat de som van de balans 0 moet zijn is een hele mooie check, echter, door afrondingen kan dit afwijken. Welke datatypen kan ik het beste gebruiken? En is het handig om elke transactie al "mooi" af te ronden? Zodat je bijv niet 3x 0.33333333 hebt maar 2x 0.33 en 1x 0.34?

Sowieso lijkt 't me niet handig om afronden pas helemaal aan 't eind te doen. In principe heb je echter in het geval van valuta waarden meestal een getal dat op 2 decimalen afgerond is, zeker in dit geval. Tenzij je natuurlijk alles weer moet verdelen over 4 mensen (bijv. 0,25 euro, over 4 man, dat wordt 0,0625 euro per persoon).
Als je afgeronde waarden gaat opslaan gaan je eindberekeningen niet kloppen.

Over transacties... Heel simpel gezegd kent een (fatsoenlijk) dbms transacties. Dat is een techniek die ingebakken zit, waarmee je in één keer een aantal queries kunt uitvoeren, zonder dat de data die je gebruikt tussentijds verandert. Ook kan je, als er in één van die queries iets fout gaat, alles ongedaan maken door die transactie te annuleren.

Zoek eens in de manual van je database (MySQL gok ik?) hoe dat moet. er is genoeg over te vinden

[CyBeRSPiN]

Met database transacties ben ik wel bekend, maar ik doelde op applicatieniveau, soort van mutex locking (synchronisatie) dat niet twee tegelijk een transactie achter elkaar editten, dat voorkom je namelijk niet met database transacties (persoon B kan het werk van persoon A overschrijven zonder het gezien te hebben), maar goed, ik denk dat dat ook wat te overdreven is, tis geen rocket science

En je hebt juist heel vaak bedragen die niet mooi afronden, deel het gemiddelde supermarktbonnetje maar door 3 En zover ik weet is er geen datatype waar je breuken in kwijt kunt, 1/3 bijvoorbeeld, veel compacter en nauwkeuriger dan 0,333333333333333333. Voor de bedragen maakt het niet zoveel uit, maar de check dat de balans 0.00 moet zijn is wel een hele mooie, die je door de afrondingen dan misloopt. Naja, ik kan alsnog checken of de balans tussen -0.50 en 0.50 ligt, dan vang ik waarschijnlijk alle fouten wel af zonder door elke afronding getriggerd te worden.
@Voutloos, ik zal nog wel even wat verder zoeken, er is zeker veel over geschreven, maar zoals zo vaak kom je veelal mensen tegen met dezelfde vraag, niet met de juiste oplossing

Bedankt voor het meedenken!

[Boss]

Ah, de studentenhuisapplicatie In het topic [studentenhuis] keuken computer software van mij is er ook nog wel wat over te vinden

Heb misschien ook nog wel ergens de achterliggende database voor je. Daarmee kan je in ieder geval met een onbeperkt aantal gebruikers dmv queries altijd de exacte saldi bekijken.

[CyBeRSPiN]

Boss schreef op zaterdag 02 februari 2008 @ 11:27:
Ah, de studentenhuisapplicatie In het topic [studentenhuis] keuken computer software van mij is er ook nog wel wat over te vinden

Heb misschien ook nog wel ergens de achterliggende database voor je. Daarmee kan je in ieder geval met een onbeperkt aantal gebruikers dmv queries altijd de exacte saldi bekijken.

Ah, had al wel gezocht naar balans, eetlijst, kooklijst, maar was deze nog niet tegengekomen, ben uiteraard wel geinteresseerd in de database

[cowgirl]

Twee ideetjes om je verder op weg te helpen:
- Maak in je balanstabel een extra regel voor de afrondingsverschillen. Doordat deze de ene keer positief en de andere keer negatief zullen zijn zal deze altijd rond de 0 zweven. Mocht er toch een groter bedrag op komen kan je iets verzinnen om dit weer gelijkelijk over de personen te verdelen.
- Voordat je een gewijzigd record wegschrijft naar de database controleer je eerst of deze in de tussentijd niet gewijzigd is. Zo ja, de tweede transactie afkeuren en opnieuw laten invoeren.

[Niemand_Anders]

Wij hebben een tabel waarin wij een lijst bijhouden welke entities (tabel, recordid) de edit state hebben gekregen. Ook de persoon welke de record in de edit state heeft gezet wordt bijgehouden.

Het systeem reset transacties automatisch elke 30 minuten. Dit om te voorkomen dat objecten oneindig in de edit state blijven staan.

Zodra iemand een record wilt wijzigen, kun je simpelweg een lookup doen naar de transactie tabel. Is er een entry, dan geef je de betreffende gebruiker een melding dat het record niet gewijzigd kan worden. eventueel kun je zelf aangeven welke persoon het record in edit modus heeft gezet.

Via een trigger wordt dit ook nog bij een update of delete statement gecontroleerd. In dat geval wordt een foutmelding terug gegeven.

Records in een balans tabel worden in principe nooit afgerond.

Toen ik studeerde hadden wij gewoon een 'pot'. Iedere week ging er 50 gulden de man in de pot en daarvan werden boodschappen gedaan. Op het moment dat bezoek bleef eten werd er verwacht dat je 2,50 per extra persoon in de pot gooide.

Met het geld dat op donderdag avond over bleef gingen we de stad in. Maar ja, mijn studenten tijdperk is ook alweer een tijd geleden. Geen internet (hadden we alleen op de HBO), wel een modem om in te bellen op een BBS.