Re: For those interested in the CRM/Entity management

[Josh Berkus <..hidden..>]

Ed,

> - Customer A makes initial contact and wants something in a hurry.  EU
> shipping address, but no vat details so we charge sales tax (VAT) and
> ship the item
> - Customer A comes back next week and we have more time to sort out the
> paperwork and they give me their VAT details and this time we can zero
> rate the item for tax purposes.  Currently means creating customer "A
> (no vat)" and remembering which customer to use forever afterwards (no
> way to hide dead entry)
> - Customer A moves down the road to their new premises.  Now I need to
> create a new "A2 (no vat)" record.  It's starting to get hard to name
> these in a way that people don't object on the top of their invoices!!
> - Customer "A2 (no vat)", orders something else, but with a shipping
> address which is now local to me and incurs sales tax (VAT).  Now I need
> a 4th record for this customer where the vat option is selected
> again...  Now very hard to remember which one to use!

These are all issues of non-relational structure in the customer information; 
basically we have a flatfile of customer details.  The way to solve these is 
to use relational structure, so;

customer	
customer_name
customer_type

has one-to-many-of:

customer_address
customer
address_type
is_current?
address details

customer also has one-to-many-of:
customer_contact
contact_type
contact_name
contact_address (link customer_address)
default_contact?

customer_contacts is one-to-many to:
contact_phones
contact
phone_type
phone_number

customer also has one-to-many-of:
tax_information
tax_type
rate
is_current?
etc.

Using proper relational structure allows both keeping old addresses and 
alternate tax information, and keeping multiples where they are called for 
(multiple offices, for example).  Although I'm not convinced that tax info 
should be attached to customers necessarily; of course, I don't really 
understand VAT as an American.

This is a basic structure for CRM apps and has been done 1000 times.

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco