Personal Accounts - recording of transfer and expense/income (1 Viewer)

essaytee

Need a good one-liner.
Local time
Tomorrow, 04:40
Joined
Oct 20, 2008
Messages
512
Following on from my initial schema design thread, in order to retain single entry income and expense entries and also have the ability to transfer between accounts requires altering the Line Item table with the addition of a couple of fields. One field being for the Line Type (expense/income or transfer), a Sub Category field and an Account field. Depending on the entry in the Line Type will dictate whether entries are made to the Sub Category or Account fields. One or the other will have a value but not both.

Are there any flaws in my proposed design? Any comments welcome.

The schema is similar to my initial schema drawn up in the first instance.

Code:
[U]Main account table[/U].
[INDENT]Account_ID (pk)
Account_Name
Account_Type (Asset, Liability, Savings, Investment, Shares etc.) (Could be a lookup table)
Account_Start_Balance
Account_Date_Created[/INDENT]

[U]Main transaction type table, will only have three fields (is it really necessary? It could be directly incorporated into Category table.)[/U]
[INDENT]Trans_Type_ID (pk)
Trans_Type_Name (Income or Expense) (only two records)[/INDENT]

[U]Main Category containing top level expense or income items.[/U]
[INDENT]Category_ID (pk)
Trans_Type_ID (fk)
Category_Name[/INDENT]

[U]Sub Category table linked back to Category table.[/U]
[INDENT]Sub_Cat_ID (pk)
Category_ID (fk)
Sub_Cat_Name[/INDENT]

[U]The base transaction record, will always need at least one Line item.[/U]
[INDENT]Trans_ID (pk)
Account_ID (fk)
Tran_Date
Tran_Description (Payee, Title)
Tran_Attachment (consider separate table for multiple attachments)[/INDENT]

[B][U]The actual line items of a split transaction.   Will always use this, even if only one transaction item.[/U]
[INDENT]Line_ID (pk)
Tran_ID (fk)
[COLOR="Blue"]Line_Type (income/expense or transfer)
Sub_Category_ID (fk)        (not required - entry only if is an expense or income)    
Account_ID (fk)                (not required - entry only if is a transfer)[/COLOR]
Line_Amount
Line_Note[/INDENT][/B]
For the visual types:



Notes:

On first viewing the above may seem more complicated than utilising the Account only method (complete double entry - expenses and income being accounts). The data entry screen for entry of an expense, income or transfer will be managed by one combo box which will be powered by a union query which combines the expenses, income and transfers into one list. Selecting a particular entry the system will know which fields to complete and whether an automatic creation of corresponding transfer transaction is required.

I like this system as the transactions are not automatically doubled. If there are 1000 transactions and 10% are transfers then the total transactions recorded in the database would be 1100. Under the double entry method there would be 2000 transactions.

I'm more committed to this approach than the true double entry method.

I've tested the above with sample data and referential integrity is maintained. That is, the Line Items table will only accept valid entries from the Sub Categories and Account tables and either field can be left empty.

A business rule will have to be catered for in that at least one of the fields must be entered with data, that is, either a Sub Category ID or an Account ID.

Another business rule will have to be addressed in that it's not possible to transfer to the same account. I envisage the underlying union query of the combo box for category and account selection to not include the account recording the transaction.

Still to consider how to hook in manipulation of buying, selling and holding shares.

Steve.
 

MarkK

bit cruncher
Local time
Today, 10:40
Joined
Mar 17, 2004
Messages
8,178
IMO, this is more complex than it needs to be. Aren't your accounts and categories functionally the same thing? And isn't your "line-item," which posts from an account to a category, just a disguised double-entry?

I'd just have an Account table, Transaction table and Post table. One transaction has many posts which always balance to zero. Each post is linked to an account--as well as being a child of a particular Transaction. Three tables. Simple, and if you want to distinguish "Category" accounts from "Bank" or "Credit" accounts, add a type field to the account table, and write queries.

And don't store the balance of an account. The balance of an account is the sum of all the posts to it in respect to time, so the balance is never stored, but always calculated in respect to a date. This way you can store post-dated transactions and it doesn't break your balance logic.

And 2000 records is tiny.

Makes sense?
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 04:40
Joined
Oct 20, 2008
Messages
512
IMO, this is more complex than it needs to be. Aren't your accounts and categories functionally the same thing? And isn't your "line-item," which posts from an account to a category, just a disguised double-entry?

I'd just have an Account table, Transaction table and Post table. One transaction has many posts which always balance to zero. Each post is linked to an account--as well as being a child of a particular Transaction. Three tables. Simple, and if you want to distinguish "Category" accounts from "Bank" or "Credit" accounts, add a type field to the account table, and write queries.

Yes, the above is my other option and have that semi-mapped out. What you have pointed out is valid and does make sense.

My 'Line_Item' as a disguised double-entry, when you put it that way, yes it is, without the double-entry posting.

And don't store the balance of an account. The balance of an account is the sum of all the posts to it in respect to time, so the balance is never stored, but always calculated in respect to a date. This way you can store post-dated transactions and it doesn't break your balance logic.

And 2000 records is tiny.

Makes sense?

Interesting. My intention is to only store the initial starting balance of the account along with an associated date. All balances thereafter will be calculated. On whatever date I start using this program I'll be creating different account types and most will have a balance to start with. Are you suggesting that the starting balance should, in effect, be a transaction item (posting)?

I only used 1000 transactions for simple maths and yes you're right 2000 is not many.

Thanks Mark, appreciate you input.

Steve.
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 04:40
Joined
Oct 20, 2008
Messages
512
That would be a transaction then....

Thanks, that clears that up. I'll certainly take it on board and use it.

In entering the first transaction I assume there is a corresponding 'Brought Forward' or 'Carried Forward' type of account? Is that how it is normally done in the business world?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:40
Joined
Feb 19, 2013
Messages
16,553
I assume there is a corresponding 'Brought Forward' or 'Carried Forward' type of account?
Account - no, transaction -yes. It depends in the type of account and what you want to do. You could just have a single value 'brought forward' or you may have a number of values which make up the brought forward figure. And again, you might 'predate' these if it is appropriate to do so.

So for example (and I'm not clear what you are using this for) if you have a 'sales' account and there are 3 invoices outstanding, I would enter each invoice total as a separate transaction (rather than all the detail) - that way if you are producing statements, the recipient can see how the current balance is made up - much more helpful than just a single b/f balance.

Is that how it is normally done in the business world?
definitely

There are typically two transaction types:

Reciepts
Payments

And these may be split into subcategories for management purposes - for example payments may be split into brought forward balance (to create a 'negative' balance), cash, transfers out, cheque, BACS etc, and are managed by having a transaction subtype field as to what data needs to be completed.

It is only my preference, but users typically enter positive values so I only allow positive values and then for payments multiply by -1 for any calculations required.
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 04:40
Joined
Oct 20, 2008
Messages
512
...snip...

So for example (and I'm not clear what you are using this for) if you have a 'sales' account

...snip...

It's not for business purposes, it's purely for personal accounting attempting to replace my reliance on Reckon Accounts (Quicken). I'm not intending on starting new backend data files for the start of each financial year.
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 04:40
Joined
Oct 20, 2008
Messages
512
Account - no, transaction -yes. It depends in the type of account and what you want to do. You could just have a single value 'brought forward' or you may have a number of values which make up the brought forward figure. And again, you might 'predate' these if it is appropriate to do so.

...snip...

Sorry, I meant to say transaction.
 

MarkK

bit cruncher
Local time
Today, 10:40
Joined
Mar 17, 2004
Messages
8,178
When you start your accounting system you need an account called Owner's Equity, which I think is an asset, but maybe Google 'Owner's Equity' and see what you get.

Then, to create an opening balance in a Savings account, let's say, you create a transaction with a post of -$25 to Owner's Equity, and a post of +$25 to Savings. Opening balance for Visa: +$55 to Owner's Equity and -$55 to Visa.

What happens when you take this approach is that you can easily calculate the balance of all your inputs into your accounting system, since everything you start to record comes from OE. So this gives you the basis for your balance sheet, and your net worth, and all the kinds of summary information that make accounting crucial to understanding your business.
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 04:40
Joined
Oct 20, 2008
Messages
512
When you start your accounting system you need an account called Owner's Equity, which I think is an asset, but maybe Google 'Owner's Equity' and see what you get.

Then, to create an opening balance in a Savings account, let's say, you create a transaction with a post of -$25 to Owner's Equity, and a post of +$25 to Savings. Opening balance for Visa: +$55 to Owner's Equity and -$55 to Visa.

What happens when you take this approach is that you can easily calculate the balance of all your inputs into your accounting system, since everything you start to record comes from OE. So this gives you the basis for your balance sheet, and your net worth, and all the kinds of summary information that make accounting crucial to understanding your business.

Thanks Mark, the concept makes sense.
 

Users who are viewing this thread

Top Bottom