Re: For those interested in the CRM/Entity management

["David Bandel" <..hidden..>]

On 6/19/07, Chris Travers <..hidden..> wrote:
> 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.

Think about this:
we'd be dealing with possibly a many to many, so again, joined through
a second table to the third that has addresses and two extra fields,
one indicating start date, one end date, and if the end date is NULL,
it is current/active.

For company name change, a field with a back reference pointing to the
row for the previous name (assuming we don't also need a forward
reference).

Ciao,

David A. Bandel
--
Focus on the dream, not the competition.
           - Nemesis Air Racing Team motto