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 ------------------------------------------------------------------------- This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ _______________________________________________ Ledger-smb-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
