[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: For those interested in the CRM/Entity management
- Subject: Re: For those interested in the CRM/Entity management
- From: "Chris Travers" <..hidden..>
- Date: Tue, 19 Jun 2007 21:35:24 -0700
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
> > - 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
> > 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.