There have been a few debates on table design for an accounts system. I’m trying to make a little home finance app to learn some new technologies.
My requirements though, are that a transaction, as presented to a user, can be made up of a few sub transactions. That’s to allow for portions of the transaction to be assigned to different budgets.
I have two ideas – both giving me issues. In the examples below, I have 4 transactions.
- I get paid 5K
- I pay 50 to nandos for dinner
- I pay 20 to FKC for lunch
- I pay 10 to Coffee Club, but 5 of that comes from my weekly budget, and 5 comes out of my wifes weekly budget
The first idea, I am currently modelling.
A Transaction is the header table, with common data. (Date, Description). (BudgetId in there is an error, and is being removed)
A Transaction has many transaction lines. Which has the source and destination accounts and amounts, as well as the Budget that it’s assigned to if any.
An account is simply an account. Can be a bank account, a shop… anything that I send money to or get money from.
The major issue I have at the moment with this, is the budget. Looking at transaction 4 – why am I putting the budget in two rows only. Should I be splitting the the MyBank to CoffeeClub as well? and that should be 4 rows?
Another issue: If I show the transaction to the user – I want to show: Paid $ 10 from MyBank to CoffeeClub
But how do I do that? I have 3 rows… how do I know it isn’t “coffee Club paid -5 to MyBank”.
The 2nd option also has it’s issues. The SQL to get a summary line of a transaction, seems tricky. I have no way of really knowing what th ‘primary’account is. For example, I want to find all the transactions for MyBank. In the Cr/Dr version, I’d need to select from TransactionLine where Account = MyBank. Which will give me the amounts an ‘dr/cr’, but … I can’t tell who I paid to, or got money from. That’s on a separate row. So I’d need to find the other row (which is part of the transaction) and work out who the money went to or came from). This seems bad.
How do I produce a line like:
Both can be checked easily to see that everything is in balance.
Any advice, ideas and critique would be great. Both ideas seem to work, but it’s the SQL to query the data efficiently for both ideas, that is stumping me.