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.
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.
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]
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.