I'm in the beginning stages of writing a new database. Our existing database, while functional, lacks a sound design resulting in slow queries.
I'm working on the design of the tables surrounding customer information. Our existing table looks something like below
Customer table
- First name
- last name
- Company
- Department
- Address line 1
- address line 2
- city
- state / province
- Zip/postal code
- country
- Phone Number
Where we suffer the most is when a customer changes location. We deal with academia and so our customers often end up in different univeristies / departments. What this leads to is either erroneous data being entered (shipping to someones undergrad university rather than where they are teaching) or in our current system we often "correct" the old data with their existing location (in other words, we lose track of where the person has been which can be useful).
I'm in the process of normalizing the data and I'm hung up on addresses. All customers will have an address, all departments in companies will have addresses, not all customers will work for a company and therefore ship to their home.
Any help (assuming you can read through my gibberish) would be appreciated.
-Matt