Re: Proposal for database refactoring in 1.4

[John Locke <..hidden..>]

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
>> ..hidden..
>> 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
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
>
> !DSPAM:4e89fd70202867447362887!
>