ötletelés - adatmodell illetve lekérdezés

Adatbázis: SQL, XML DB

Készítek egy kis programot, amiben a következő feladat van:

Van egy folyószámla, amihez tartoznak tételek.
Azt szeretném, ha a folyószámla tételeinek listázásakor látszana, hogy az adott tétel a számla egyenlegét hogy módosította.

Példa:
kezdő egyenleg 0
1. tétel +500 - egyenleg 500
2. tétel -200 - egyenleg 300
3. tétel +200 - egyenleg 500
4. tétel +200 - egyenleg 700

A kérdés az, hogy hogyan lenne jó ezt kialakítani.Jelenleg egyenleget nem tartok nyilván, és nem is számolok.
A tételeknek van mennyisége és van dátuma (meg leírás), és kész.

A mostani lekérdezésben van egy sum(mennyiseg) a végén, ami a számla jelenlegi egyenlegét megmutatja, de az nem látszik, hogy mondjuk 1 hete mennyi volt az egyenleg.

Nincs nagyon sok tétel, mondjuk napi 10 a nagyságrend. Előfordul időnként, hogy régi tételek kerülnek be a rendszerbe. Viszonylag újak (pár hét) elég sűrűn, és nem is kevés. Régebbiek ritkán: néha soká (hónapokig) semmi, néha meg egyszercsak sok.

Több ötletem is van, de egyikkel sem vagyok teljesen elégedett.

1:
minden számlatétel mellett eltárolom, hogy éppen mennyi az egyenleg, és ha régi tételt rögzítek, akkor az összes későbbi tételnél ezt módosítom. - Kicsit félek a régi tétel beszúrásától - sok módosítás, illetve úgy érzem, hogy az egyenlegnek nem a számlatételnél van a helye logikailag. Viszont könnyű lekérdezésbe tenni, és megjeleníteni.

2:
készítek egy egyenlegek táblát, ahová minden folyószámlához és minden naphoz eltárolom, hogy mennyi volt a napi egyenleg. - Ez logikailag jobban tetszik, persze régi tétel beszúrásakor ugyanúgy át kell számolni és módosítani az egész táblát. Emellett a lekérdezés mem könnyű, tehát ennek inkább csak hátrányai vannak, mint előnye. :-)

2bis: tételenként az egyenleg külön táblában. Ez se tetszik jobban.

3:
valami ügyes lekérdezést kellene készíteni, ami az adott tételben, és az őt megelőző tételekben szereplő mennyiségeket összegzi. - Ez tetszik, mert nem kell beszúráskor, vagy ha pl. egy tétel egyik folyószámláról másikra kerül, végigmódosítani mindent, nincs egyenleg információ eltárolva itt-ott (ami kvázi felesleges, hiszen számolható mennyiség). És Viszont egyfelől még nem izzadtam ki a szükséges lekérdezést, másfelől félek, hogy bonyolult és lassú lesz.

Szóval a kérdések: valaki csinált már hasonló projektet? Mit használt, miért, és hogyan vált be?

A fenti hármon kívül van még ötlet?

Vagy bármi komment?

Ja, mindezt adatbázisban kellene, tehát az nem megy, hogy lekérdezek egy adathalmazt, és majd valami programban kiszámolom. adatmodell, SQL, esetleg tárolt eljárás

  • 1318 megtekintés

( mulder | 2010. 11. 03., sze – 11:43 )

Ha ez a konyhapénz számító algoritmus, akkor én úgy csinálnám, hogy maga a tábla 4 mezőből állna:
timestamp, betét, kivét, egyenleg. A táblához adnék egy triggert, ami INSERT/UPDATE/DELETE-re lefuttat egy DB tárolt eljárást, ami átmazsolázza az összes rekordot és frissíti az egyenleg értékét.

Így a számítást az adatbázis végzi, nem kell a külső alkalmazásból molyolnod. Mellesleg transzparens lesz a megoldás: egy beszúrásnál csak egy SQL parancsot kell kiadni, nem kell foglalkoznod a többivel.

Igen, ez kb. az 1-es pont, de a lényeg, hogy ne a külső alkalmazás piszkálgassa az egyenleg számolást, hanem az a db feladata legyen.

Ha nem konyhapénz lenne, akkor nagyon sok változó lenne, ami alapján dönteni kellene. Bejöhetne a VIEW, MATERIALIZED VIEW (Oracle) függve attól, hogy inkább írjuk vagy olvassuk az adatbázis. Lehetne játszani azzal, hogy az INSERT-nél csak akkor szüttyögünk az egyenleg számítással, ha nem a legutolsó tételt írjuk be (azaz csak azon a részhalmazon számítjuk újra az egyenlegeket, amelyek garantáltan megváltoztak) stb stb.

No, kicsit nagy a konyha?:)

esetleg ki kellene próbálni, hogy egy summázó subselect nem felel-e meg... a triggeres módosítgatós cuccal nekem az a gondom, hogy redundanciát visz az adatbázisba és ha egyszeris valaki belenyúl kézzel egy tételbe, felborul az egész.

szerk: 1 szavazat a 3-ra.

Viszont minden esetben bele kell biggyeszteni a joint a lekérdezésbe. Míg triggeres esetnél tetszőleges rekordot lekérdezhetek: minden kedd, hétvégenkénti bontás, stb. Joinolásnál minden egyes ilyen típusú lekérdezéshez külön passzítani kell a joinos részt. Ettől akarnám a posztolót megkímélni

( uid_194 | 2010. 11. 03., sze – 12:38 )

Harmadik megoldas (postgres, de mukodhet hasonlo mysql-el is kis modositassal; sqlite ha jol neztem nem tud ilyet): 

CREATE TABLE tetelek (id SERIAL, tetel INT);

feltoltod adatokkal, majd: 

SELECT t1.id, t1.tetel, SUM(t2.tetel) FROM tetelek t1 JOIN tetelek t2 ON t1.id >= t2.id GROUP BY t1.id, t1.tetel ORDER BY t1.id;

Nalam ez igy nez ki: 


algernon=# select * from tetelek;
 id | tetel 
----+-------
  1 |   200
  2 |   100
  3 |   150
  4 |   -50
  5 |   -25
  6 |    40
(6 rows)


algernon=# select t1.id, t1.tetel, sum(t2.tetel) from tetelek t1 join tetelek t2 on t1.id >= t2.id group by t1.id, t1.tetel order by id;
 id | tetel | sum 
----+-------+-----
  1 |   200 | 200
  2 |   100 | 300
  3 |   150 | 450
  4 |   -50 | 400
  5 |   -25 | 375
  6 |    40 | 415
(6 rows)

Szóval 3-as megoldás.

Működik a lekérdezés, csak a join feltételén kellene valamit agyalni.

Ugye id nem használható, mert nem a rögzítés sorrendje, hanem a dátum számít.

A sima t1.datum >= t2.datum meg azért nem jó, mert az egy napra rögzített több tételt összemossa.

Szóval valami ebben az irányban elindulva jó lehet...

select * from tetelek2 order by datum

"ID","DATUM","TETEL"
"7","30-OCT-10","40"
"2","01-NOV-10","200"
"3","02-NOV-10","100"
"4","02-NOV-10","150"
"5","03-NOV-10","-20"
"6","03-NOV-10","-25"
"1","10-NOV-10","200"

select
t1.id, t1.datum, t1.tetel, sum(t2.tetel)
from tetelek2 t1 join tetelek2 t2 on t1.datum >= t2.datum
group by t1.id, t1.tetel, t1.datum order by datum;

"ID","DATUM","TETEL","SUM(T2.TETEL)"
"7","30-OCT-10","40","40"
"2","01-NOV-10","200","240"
"3","02-NOV-10","100","490"
"4","02-NOV-10","150","490"
"6","03-NOV-10","-25","445"
"5","03-NOV-10","-20","445"
"1","10-NOV-10","200","645"

...

Most eddig jutottam:

select t1.n, t1.id, t1.datum, t1.tetel, sum(t2.tetel)
from
(select id, datum, tetel, rownum n from (select id, datum, tetel from tetelek2 order by datum, id)) t1,
(select id, datum, tetel, rownum n from (select id, datum, tetel from tetelek2 order by datum, id)) t2
where t1.n >= t2.n
group by t1.n, t1.id, t1.tetel, t1.datum
order by 1

"N","ID","DATUM","TETEL","SUM(T2.TETEL)"
"1","7","30-OCT-10","40","40"
"2","2","01-NOV-10","200","240"
"3","3","02-NOV-10","100","340"
"4","4","02-NOV-10","150","490"
"5","5","03-NOV-10","-20","470"
"6","6","03-NOV-10","-25","445"
"7","1","10-NOV-10","200","645"

Működik, bele is lehet tolni egy view-ba, ha csak adott tételeket akarok nézni, mondjuk.

De azért szépnek nem mondanám :-)