Re: General Ledger db design

Martin Winsler wrote:
> I hope this isn't too far off topic. I've noticed some discussion
> about referential integrity, the use of nulls, and database design
> recently here. This is a real world situation where referential
> integrity needs to be broken in theory, I believe. Does anybody have
> any experience or knowledge of building financial accounting
> databases? Am I wrong about this?
>
> The problem is that with "double entry accounting" you have records in
> tables that both reference other records in the same table as well as
> different records in other tables depending on some fairly complex
> logic.
>
> For instance an invoice is a financial instrument, so the "parent
> record" would naturally want to be part of a company wide "journal" or
> "ledger." However, its child records would be actual invoice lines as
> well as two different sets of entries in the general ledger detail,
> all 3 sets of records must agree with each other on the invoice parent
> record total.

I can only tell you what I've done in the past, you can take it from there.

First, there are two very basic tables, the chart of accounts and the
list of transactions. Every line in the transaction table links to one
account.

So far so good. Now we have AR invoices, and AP vouchers. Let's
oversimplify for argument and say that when you post an AR invoice you
post two entries in the transactions table, a debit to AR and a credit
to sales (forget about tax and stuff for now). Likewise for an AP
voucher, debit some expense account and credit AP.

So now the problem is we've got at least two more potential parent
tables for the transaction, being invoices and vouchers, and it gets
worse when we add checks received, checks paid, overpayments, allowances
and so on and so on.

The solution I've always used is to introduce a table of batches. This
is the table that unifies all of the others. When you post an invoice,
you generate a new batch, give it type "AR". The invoice is stamped
with the batch #, as are the GL transaction rows. When you post an AP
voucher, do the same thing. Same for checks received, checks paid,
etc, all of them have different batch types.

In short, the problem of too many parents is inverted to produce many
children instead, and the problem goes away.

--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200 Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this
::question: do you worry about how to throw away a garbage can?