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 >> >> ------------------------------------------------------------------------------ >> 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 >> >> >> > > ------------------------------------------------------------------------------ > 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:4e89fd70202867447362887! > ------------------------------------------------------------------------------ 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