Re: General Ledger db design

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/23/07 20:35, 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.
>
> Double entry accounting I think in theory dictates that you break
> referencial integrity because you have 2, not 1, sets of records
> on which a parent record's total must be based. You also have a
> natural 3rd set of detail records, for instance invoice lines,
> that don't necessarily have a relationship with the general
> ledger detail.

Setting the constraints to be NOT DEFERRABLE and INITIALLY DEFERRED
is how you need to set such constraints.

> The other way to do it is to have a pure journal/ledger
> relationship where you have a 3rd invoice header table that
> relates back to the journal. So every time you
> create/update/delete an invoice, you trigger an analogous journal
> entry. Not exactly normalized, but I guess that's why you have
> triggers.
>
> Am I wrong? Again, I apologize if off topic, but I think this is
> a real world and complex example of some of the discussions here.
> You could use other database models besides relational, but there
> is nothing as powerful and as versatile as sql in my opinion. I
> think it's worth the problems.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF36k7S9HxQb37XmcRAraxAKDUHrMFEBlHU+l12UiWBTEsnoUyogCg3etF
PGB6AjUZxOrpKR2E3G8Zir0=
=aUd/
-----END PGP SIGNATURE-----