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? Ed W