Re: Proposal for database refactoring in 1.4

[Erik Huelsmann <..hidden..>]

Hi Chris,

On Mon, Oct 3, 2011 at 7:25 PM, Chris Travers <..hidden..> wrote:
> 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:

[ ... ]

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

Having discussed the design a bit on IRC, I feel this design is much
more intuitive than the one we had so far. As you pointed out,
tracking in this model is also explicit -- whereas tracking in the
current model is implicit: things which have the same date and have
equal values for some other characteristics are to be considerd
"belonging together".


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

You assured me that we could define a technical design on top of these
ideas which doesn't depend on UPDATE and DELETE as much as the current
model does where insertion of new data is concerned: it's rather
difficult to enforce the difference between data insertion and data
maintenance if insertion needs UPDATE and DELETE as well.


Bye,


Erik.