Skip to content

SQL 103 – Normalized Table Design

2011 October 3
tags:
by Shannon Lowder

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!

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS