Designing Data Models: Customers

A customers table can be simple and can also get quite complicated very quickly. Let's take a look at some data we would like to keep with Customers:

Well, we would like to keep first name and last name. But what if we expect to store data from different countries that the family name is written first and first name comes second.

What about if the customers have a middle name? what if they have 3 middle names?

Would you like to include a title like Mr. Mrs. Ms. ? Although as of 2017, titles or pronouns are a sensitive issue.

Technically, if you have a title, you may not store gender.

Do you want to store these different points of data and in addition, denormalise them into a full name so that when you send emails out, you won't make any embarrassing mistakes.

Moving on, we may want to store email, phone number and an address.

However, with all of those, there maybe issues:

• Customers can change their emails over time and you may want to store the new ones as well as the old ones. Which means you may need a separate table for emails which includes if they are old or active. In addition, you can keep a copy of the current email denormalised along side the customers data.
• Phone numbers maybe home, mobile or business. Maybe you would rather you could have a phone and phone type in their own table.
• Regarding addresses, would that be home, business, delivery or billing? Addresses can change over time and would we like to store old addresses?