Setting up MySQL for 'Banking' Style Database

[c4onastick]

Hi,

I've had it with trying to keep track of what my roommates owe me for utilities, food, rent, etc. Sounds like a good opportunity to open the web-banking part of "C4onastick's Credit Union". I'm having a little trouble thinking out how this should work. Essentially I'd like it to parallel most online banking. Here's the layout I'm thinking:

Table: Roomate

Cols:
[list]
[*]transaction_id
[*]date
[*]description
[*]amount
[*]balance
[/list]

With one table for each roommate. How would I work the balance column? I'm thinking a MySQL function to calculate the value from the previous entry. How would I write that (I've never written a MySQL function)? Otherwise I could have a bunch of queries from the PHP side, and have PHP calculate the value and plop it in. Any thoughts? (Only my roommates and I will be using it, so I'm not terribly worried about security or using InnoDB)

Thanks in advance.

[mjlogan]

i believe you should get going for more of a clients (roommate) table and transaction table.

roommate

[b]client_id[/b]
name
current_balance
credit limit.....
whatever else you wanted

and then a transaction table.

trans_id
[b]client_id[/b]
date
description
amount

[c4onastick]

Oh, good point. That'd allow more options for finding/organizing data. Any one know how to set up that current balance column? I know MySQL has functions built in, can I work it sort of like an excel sheet by having the value of balance calculated? Thanks mjlogan.

[fenway]

Sounds like current_balance should be dynamically calculated on-the-fly, and not constantly updated and stored in the DB...

[c4onastick]

Agreed. I'm thinking this out in my head, and it could become a mess of database calls.

- Select the previous balance
- Calculate the resulting transaction balance
- Insert the new transaction

I invisioned the transaction table to be more like a checkbook ledger. With the resulting balance of the transaction tracked/calculated for every transaction. Sure this would be doable, but it seems like there's an easier way... Or maybe not! That's why I'm posting! Thoughts?

[fenway]

Well, the real question is why you need to keep storing the temporary balance.  If you just store the transactions, debits & credits, you can simply add them up, and there's your current balance.

[c4onastick]

[quote author=fenway link=topic=116431.msg475607#msg475607 date=1164807977]
Well, the real question is why you need to keep storing the temporary balance.  If you just store the transactions, debits & credits, you can simply add them up, and there's your current balance.
[/quote] I think I'd like to be able it have it browseable by date, in which case I think I'd need the resulting balance stored so that you could see the resulting balance (what they owe me) as of when. I'm not sure how I'd do that if they were live calculated. Ideas?

[c4onastick]

I see your point though, it would be much easier to just calculate them "on-the-fly". The only problem I have with that is that this record will be for years (I usually pay all the utilities, rent, etc. and have my roommates pay me for that all in total when rent's due). So I think it would be nice (read: smart) to have a hard copy of the balances after every transaction.

So I guess my question should really be: Is there a way to embed a 'Excel style' "=previous_balance + current_transaction" (please forgive the MySQL noobieness of this question) into the value for the balance column? Or do I need to do that the way I described earlier?

- Select the previous balance
- Calculate the resulting transaction balance
- Insert the new transaction

Thanks for all your help!

[fenway]

I suppose you could do that... but IMHO, it makes more sense to have another summary table with this information on a per-day basis.

[the_oliver]

if you used pgsql instead of mysql you could have it do calculations for you server side!  just an interesting though!