Re: For those interested in the CRM/Entity management

["Chris Travers" <..hidden..>]

On 6/19/07, Ed W <..hidden..> wrote:

> One other issue which occured to me which is significant to think about
> right now is as follows:
>
> - Invoices really should have delivery and billing addresses locked down

Only caveat is "billing address" may be somewhat ambiguous in this case :-).

Agreed on shipping invoices.  Maybe we should store the address that
the invoice was originally billed to also (which my be separate from
the address the current statement with the  invoiced amount may be
sent to).

> - As it's an address these should possibly be normalised out of the
> invoice record itself

Agreed.

> - Customers may move or update their address details through time -
> however, for record keeping purposes we need to know how we really
> invoiced and at what address 20 years ago, even if they are no longer
> there now

Agreed.

> - Customers may have multiple (regular) delivery addresses and tax codes
> may be different for each address

We need to tackle the tax issue.  My own thinking is that this is
something that is better handled by tax rules.  300 tax check boxes
next to each location may be a problem.

> So one possibility is that whenever a customer contact record is edited
> then we make a copy of the record, mark the old one obselete, update the
> new record and use a linked list structure to chain together all the the
> changed customer details (so that we have an audit of the old changed
> addresses).

I was with you up until you said "linked list" which isn't really
meaningful in this context.  More likely you would have a list of
records which would have a valid_to attribute describing when they
were no longer used.

>  Optionally we could allow deleting of these linked records
> if no invoice/order/quote ever referenced that iteration of the
> customer, eg change a customer twice in quick succession without
> invoicing them, then we might only keep the latest change and not both
> changes

If we really want the audit trail, we don't want to allow deleting of
these records.


> Address should probably be nomalised out of the entity in order to allow
> having multiple addresses per entity (eg two delivery addresses)

We already do this in 1.3.

> How does this sound?  We probably don't want this to get
> overcomplicated, hence the suggestion just to duplicate records when
> they are changed and hide the old records so that they don't normally
> show up (but are available to be referenced by old invoices)

This could get complicated.  I think there are some ways of doing
this, but may require some variation from what you suggest.  For
example, adding a valid_to timestamp with a default of infinity, and
adding that to primary keys.  Also, a lot of our indexes would need to
become partial indexes.

Ideally, we would use some sort of table partitioning to keep active
records together as well.

Best Wishes,
Chris Travers