Re: Proposal for database refactoring in 1.4

[John Locke <..hidden..>]

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
>
> !DSPAM:4e89f01a202861876012615!
>