[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: For those interested in the CRM/Entity management

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


- 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


- 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

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

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