[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: John Locke <..hidden..>
- Date: Mon, 03 Oct 2011 11:38:59 -0700
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
On 10/03/2011 11:22 AM, John Locke wrote:
> 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
> 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.
> 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:
>> 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
>> 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
>> 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.
>> Ledger-smb-devel mailing list
> 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.
> Ledger-smb-devel mailing list