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