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.
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.
Thanks.
No need to miss a message.
Get email on-the-go with Yahoo! Mail for Mobile.
Get started.