[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: Proposal for database refactoring in 1.4
- Subject: Re: Proposal for database refactoring in 1.4
- From: Erik Huelsmann <..hidden..>
- Date: Mon, 3 Oct 2011 23:50:52 +0200
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
> 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
> 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.