So last time we were discussing the difference between normalized and de-normalized database design. We also discussed our case study, the CRM company. We decided to begin our database design by designing normalized tables to track customers and contacts with those customers.
Let’s continue with that case today. We want to build tables to store customers, their addresses, and their phone numbers. Our company will receive customer information in the form of a flat file. We need our database to hold all the information from this flat file, as well as let us conduct our business of contacting these customers.
The Source File
| Column Name | DataType | Length | Description |
| UniqueCustomerID | VARCHAR | 32 | GUID to uniquely identify each customer |
| FirstName | VARCHAR | 50 | |
| MiddleName | VARCHAR | 50 | |
| LastName | VARCHAR | 100 | |
| StreetAddress | VARCHAR | 255 | |
| StreetAddress2 | VARCHAR | 255 | |
| City | VARCHAR | 50 | |
| State | CHAR | 2 | |
| Zip | VARCHAR | 10 | |
| PhoneNumber | VARCHAR | 10 | |
| CellNumber | VARCHAR | 10 | |
| WorkNumber | VARCHAR | 15 | |
| SpouseFirstName | VARCHAR | 255 | |
| SpouseMiddleName | VARCHAR | 255 | |
| SpouseLastName | VARCHAR | 255 | |
| SpousePhoneNumber | VARCHAR | 10 | |
| SpouseCellNumber | VARCHAR | 10 | |
| SpouseWorkNumber | VARCHAR | 15 |
Looking at the data we will receive, I want you to think about the data we’re receiving. If you have any experience with object oriented programming, answer the following question. How many different objects exist in this data? If you’re not familiar with that terminology, then how many nouns do you see in this layout?
Three.
There are customers (and spouses). There are addresses. There are phone numbers.
Now, what is the parent object? In other words, what one object to the other two objects both relate to?
That’s right, customers would be your parent object. So we’ll begin by modeling that object first. We’ll design a table that will hold data about our customers. We want to create a table that holds just the data that relates to customers, but doesn’t relate to anything else…at least not directly.
The Customer Table
An example of what I mean by data that only relates to customers and not directly to anything else: first name, middle name, last name, and unique customer ID. The name doesn’t refer to an address. It doesn’t refer to a phone number either! When you break down a customer into its “atomic” attributes, we just have 4 in this example.
When I diagram a table, I usually draw a box, and then start listing the attributes that identify the object I’m modeling.
In this case, I’ll draw a box, and name it Customer. Then I’ll list the attributes that relate to customers.
Since customers and spouses have these same attributes, we’re going to use the same table to store both. That way we’re not repeating data.
Let’s Do The Same For Addresses and Phone Numbers
We’re going to draw addresses and phone numbers the same way. We’re only going to include the attributes that relate to addresses in the Address table, and we’re only going to include the attributes that relate to phone numbers in the PhoneNumber table.
Now that we’ve drawn these two tables, we’re not done with these tables. We have to relate spouses to spouses, addresses to customers, and phone numbers to customers. But we’re going to leave that until next time.
Until then, if you have any questions, send them in. I’m here to help!