[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, David Bandel <..hidden..> wrote:
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.

Well, you have one issue.  Primary keys cannot have NULL components.
It is probably better to have a valid_to timestamp which defaults to
infinity.  At any given point in time you pick the one with the lowest
timestamp after a given date as the one that was active at that time.
Then you can add the valid_to to the primary key and make it work like

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

If you see it as a relational set rather than a C structure, we don't
need references at all.  Just the set which contains enough
information that programs can generate their own data structures as
needed from the data.

Best Wishes,
Chris Travers