SQL 103 – Normalized Table Design

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 NameDataTypeLengthDescription
UniqueCustomerIDVARCHAR32GUID to uniquely identify each customer
FirstNameVARCHAR50
MiddleNameVARCHAR50
LastNameVARCHAR100
StreetAddressVARCHAR255
StreetAddress2VARCHAR255
CityVARCHAR50
StateCHAR2
ZipVARCHAR10
PhoneNumberVARCHAR10
CellNumberVARCHAR10
WorkNumberVARCHAR15
SpouseFirstNameVARCHAR255
SpouseMiddleNameVARCHAR255
SpouseLastNameVARCHAR255
SpousePhoneNumberVARCHAR10
SpouseCellNumberVARCHAR10
SpouseWorkNumberVARCHAR15

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!

Tags:

Trackbacks/Pingbacks

  1. SQL 103 – Introduction to Database Design and Architecture | Shannon Lowder - 20111003

    […] Normalized Table Design […]

  2. SQL 103 – Relating Your Tables | Shannon Lowder - 20111005

    […] Last time we were discussing how to build normalized tables.  We designed 3 tables: Customer, Address and […]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.