Proposal for database refactoring in 1.4

[Chris Travers <..hidden..>]

Hi all;

I would like to go forward with my database refactoring proposal for
1.4.  This is an outgrowth of two things, Josh Drake's feature request
that we merge the AR and AP tables and my work on restoring
referential integrity to the database.  The current problem is that
acc_trans.trans_id references transactions.id, but transactions as a
table largely is a proxy for the set of ap.id, ar.id, and gl.id.
While we have a working solution here, it is more brittle and complex
than I would like it to be.  So I am working on refactoring the tables
as follows.  For those who wish to follow the documentation, check out
the doc/database/ledgersmb.html in your ledgersmb directory.

The following tables would be replaced:

acc_trans
transactions
payment
payment_map
gl
ar
ap
invoice
oe
orderitems

If we can't get to it, we could leave invoice and orderitems out for
1.4 and address in 1.5

The new tables would be
journal_line (replaces acc_trans).  Represents line items.

journal_type (replaces the "table" field in transactions).  Gives a
list of journals for journal entries (sales, purchases, general, etc)

journal_entry (replaces most of transactions, payment, and gl, as well
as parts of ar and ap).  Holds basic info about transactions, like
source number

payment_map (would work almost the reverse of the current payment_map
table, mapping the payment journal entry to the appropriate purchase
entries)

invoice (replaces ar and ap for customer and invoice-specific info).
Basically stores due date, customer/vendor links, etc.

inventory_orders (replaces oe, and also parts of ar and ap as regards
actual inventory invoices).  This stores container info for
quotations, orders, and invoices

inventory_lines.  Replaces invoice and orderitems except for cogs purposes

inventory_cogs.  Contains info for calculating cogs, so that things
can be back-tracked to their original transactions.

This has the impact of:
1)  Reducing 10 tables into 8
2)  Allowing enforcement of referential integrity, and reducing the
number of redundant fields used.
3)  Better tracking of payments, in particular
4)  Given that PostgreSQL does allow the creation of constraints on
subsets, this does not reduce the expressiveness of potential
constraints.
5)  Should make for easier reporting.

Also logically it changes things to the following under the above proposal:
1)  Every AR and every AP transaction is also a journal entry
2)  Every payment and every receipt is now a full transaction and journal entry
3)  COGS calculations can be traced back to see which transactions
allocated which cogs, allowing for better debugging

Any feedback before I send a sample database schema?

Best Wishes,
Chris Travers