Gossamer Forum
Home : General : Internet Technologies :

database design for double entry accounting system

Quote Reply
database design for double entry accounting system

a question for anyone with some knowledge of accounting.

I'm building a double entry system for my web site and just wanted to know how you would approach the design of the ledger - I have 2 questions in particular that I'd like to get answers for but any other general suggestions would be great (my accounting knowledge is not that great!).

I currently have the following columns in the ledger table...

id (unique)
account (userid)
type (journal type)
asset ($, pounds etc for different currencies later down the track)
date (of transaction)
rate (for asset conversion)
timestamp (date transaction entered into db)

What I'd like to know is....

1. Currently I am representing the total of each transaction under the 'gross' column, with either a '100.00' or '-100.00' value for credits and debits. Is a single 'gross' column ok, or would it be better for me to have two columns 'debit' and 'credit' for each ledger entry and fill the relevant one? - Why?

2. Regarding the bank account or holding account that each ledger entry relates to, should I add another 'holdingaccount' column to the ledger database, or create a separate table for the different holdingaccounts and another table that links each holdingaccount => ledgerentry? - Why?

any advice would be greatly appreciated!