Double entry accounting database design [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

开发者_StackOverflow

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 9 years ago.

Improve this question

Simple question

See http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html

Ok there are 3 tables ACCOUNT, JOURNAL, and POSTING

If you want to have a transaction status, where should the status column be?

1. Status should be in the Journal table
2. Status should be in the Posting table

please explain your choice, thanks.

---

Simple, but trick question? There is no requirement for a success/failure status because a JOURNAL is a logical unit of work, and all of its POSTINGs are part of that unit of work. Therefore the JOURNAL and its POSTINGS either exist, if the logical unit of work is successful, or they don't exist if the unit of work is unsuccessful.

This simple test (it's there because it worked or it's not there because it didn't) is a consequence of the fact that there is a business requirement to ensure that JOURNAL includes a candidate key comprised of an unbroken sequence of numbers, which is necessary because it gives auditors a false sense of security.

In a real-world system, there would be a second set of tables, along the lines of PENDING_BATCH, PENDING_JOURNAL and PENDING_POSTING which would contain transactions that haven't been completed yet. It would make sense to keep various kinds of status information here. The transaction status for pending transactions belongs on the PENDING_JOURNAL table because the whole journal and all of its postings must either succeed or fail as a unit, so the status of the unit should be normalized to the parent record (i.e. PENDING_JOURNAL).