Re: Proposal for database refactoring in 1.4

[Chris Travers <..hidden..>]

On Mon, Oct 3, 2011 at 2:50 PM, Erik Huelsmann <..hidden..> 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