Re: For those interested in the CRM/Entity management

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

On 6/19/07, Ed W <..hidden..> wrote:
> Hi
>
> > Ok, I look at normalization as a mathematical method of ensuring that
> > the data can represent anything it needs to.
> >
> > Interface and physical model are not tightly coupled.
> >
>
> Agreed.
>
> 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
> - 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
>
> 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).  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
>
> Address should probably be nomalised out of the entity in order to allow
> having multiple addresses per entity (eg two delivery addresses)
>
> 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)
>
> Sound sensible?

Does to me.  It should be easy to do.  Instead of an update, we
actually create a new record.  If we use the recordid to link, then
the old invoices could point to the old row and the new invoices use
the new row, but when we look at historical activity it accesses both
records.

BTW, I face the same thing, but with a company whose name changed.  I
need the old name for old invoices, but the new name for the new ones.
I want to make sure it's the same company (i.e., if I reference the
new name, the historical invoice info is included, but under the old
name).

This is not so much for me, but for someone new to the company that
doesn't have the background.

Ciao,

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