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