[Ledger-smb-devel] Proposal for database refactoring in 1.4
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 Ledger-smb-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
Re: [Ledger-smb-devel] Proposal for database refactoring in 1.4
Hi, Initial thoughts: Sounds like a huge improvement, and it mostly makes sense to me. The one area I'm not clear on is inventory management, and whether oe and order_items should get rolled into the same tables as ar/ap/invoice. I may be totally off-base here, but I'm thinking that invoices are different from orders. How do items on an order get converted to items on an order? Is there going to mainly be some sort of type column on these tables to split it out? Might it be better to keep the order management separate from the invoicing, if invoices represent legal documents? I'm not saying this is the way it should go -- I'm just asking the question why should these be in the same table? instead of copying these items to equivalent invoice tables at the time of invoice -- basically keep all the invoice stuff separate from the order stuff. What you're describing makes sense to me if an order basically IS an invoice, just at an earlier phase of the lifecycle. But it strikes me that if you're partially shipping an order and splitting it into multiple invoices, that might get messier/harder to figure out what happened than if they're treated as two different things. Definitely like the idea of combining ar, ap, and gl and doing away with transactions. I haven't been through the payment schema/workflow enough to speak to that. Cheers, John On 10/03/2011 10:25 AM, Chris Travers 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: 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 Ledger-smb-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel !DSPAM:4e89f01a202861876012615! -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance,
Re: [Ledger-smb-devel] Proposal for database refactoring in 1.4
I keep thinking about this, and again, I may be way off base, but I'm thinking we're missing a historical component here, particularly in orders. Again, I look to how Drupal addresses this, and for content, this is done by splitting a node into two tables: node and node_revision. If you add fields to a content type, these are stored in separate tables with essentially a fk reference to node_revision. I'm thinking order and order_revision. Order being a container for revisions. As items get shipped on an order, you get new revisions, with a log message, new set of line items, etc. Invoices may not need this, if they're not supposed to get modified after creation. On 10/03/2011 11:22 AM, John Locke wrote: Hi, Initial thoughts: Sounds like a huge improvement, and it mostly makes sense to me. The one area I'm not clear on is inventory management, and whether oe and order_items should get rolled into the same tables as ar/ap/invoice. I may be totally off-base here, but I'm thinking that invoices are different from orders. How do items on an order get converted to items on an order? Is there going to mainly be some sort of type column on these tables to split it out? Might it be better to keep the order management separate from the invoicing, if invoices represent legal documents? I'm not saying this is the way it should go -- I'm just asking the question why should these be in the same table? instead of copying these items to equivalent invoice tables at the time of invoice -- basically keep all the invoice stuff separate from the order stuff. What you're describing makes sense to me if an order basically IS an invoice, just at an earlier phase of the lifecycle. But it strikes me that if you're partially shipping an order and splitting it into multiple invoices, that might get messier/harder to figure out what happened than if they're treated as two different things. Definitely like the idea of combining ar, ap, and gl and doing away with transactions. I haven't been through the payment schema/workflow enough to speak to that. Cheers, John On 10/03/2011 10:25 AM, Chris Travers 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: 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
Re: [Ledger-smb-devel] Proposal for database refactoring in 1.4
Hi Chris, On Mon, Oct 3, 2011 at 7:25 PM, Chris Travers chris.trav...@gmail.com 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 Ledger-smb-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
Re: [Ledger-smb-devel] Proposal for database refactoring in 1.4
On Mon, Oct 3, 2011 at 2:50 PM, Erik Huelsmann ehu...@gmail.com wrote: Hi Chris, 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. Just to clarify what I think you are talking about and what I am talking about. The old SL code does an insert in the following way: 1) Insert bogus data into a table 2) Locate bogus data 3) Update bogus data with real data This is a fundamentally broken approach since it makes data constraints impossible, Of course, changing this behavior in the SL codebase has proven to be extremely difficult. I succeeded in doing it in part for orders and then only with a lot of work. In general the effort can be better put into a rewrite. The newer code tends to do something like: 1) Try to update record 2) If not found, insert record However the basic area I am talking about here is essentially splitting order lines when shipment or billing occurs so you can see quickly on the order screen what has been invoiced. The current approach here is fundamentally broken and hopefully this approach will be better. Hope this helps, 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 Ledger-smb-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
