Hi Chris, On Mon, Oct 3, 2011 at 7:25 PM, Chris Travers <[email protected]> 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. ------------------------------------------------------------------------------ All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 _______________________________________________ Ledger-smb-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
