Accounting Database Review and Database Modeling Notes

May 15th, 2007

I'm preparing to release pbooks and of course tweaking the database model a little. As I work on the db model, I've reviewing some other open source project database models, including the following projects:

  • Arias
  • phpOrg
  • WebERP
  • SQL-ledger
  • CK-ERP

So far I have kept the pbooks data model as simple as possible. It is three tables:

  • Accounts - bank accounts, credit cards, fixed assets, etc.
  • Entries
  • Entry amounts

The entry amounts table has a foreign key constraint against the entries table, but I might change that. The idea I'm toying around with now is that in a transaction, there is at least one side of the double-entry accounting system that has only one account, while the other side could have two or more accounts. I will thus keep the three tables, but I'll add etnry_type_id, amount, and account.

Actually what I was trying to do here was make a general ledger, but I'm going to do that in a different table.

The problem with accounting systems in today's world is that its backwards - previously you would record the transaction in the journal first, then add it to the ledger, whereas now you would most likely add it to the ledger first, then to the journal (due to the number of electronic transactions). While redundant, I think there needs to be three tables. journal entries, entry amounts, and ledger transactions.

Yearly Indexes: 2003 2004 2006 2007 2008 2009 2010 2011 2012 2013 2015 2019 2020 2022