CODEWITHSUNDEEP
database-normalization3nf
UserFriendly
UserFriendly

Reputation: 63

3NF - Did I do it right?

I have this table containing these columns and I want to make it satisfy the 3NF. What I did was move DOB, City, Province, Postcode to another table called 2ndCus. However, I am still not sure if I did it correctly or not. What do you think?

CustomerID
LastName 
FirstName
DateofBirth
Address
City
Province
PostCode
Email
Phone#

Upvotes: -2

Views: 915

Answers (1)

Mark J. Bobak
Mark J. Bobak

Reputation: 14403

Well, without knowing your total requirement, I can't be sure, but a reasonable guess look something like this:

CUSTOMERS
---------
CUSTOMER_ID
LAST_NAME
FIRST_NAME
DOB

ADDRESSES
---------
ADDRESS_ID
ADDRESS_TYPE
ADDRESS
CITY
PROVINCE
POSTCODE

EMAIL_ADDRESSES
---------------
EMAIL_ID
EMAIL_TYPE
EMAIL_ADDDRESS

PHONE_NUMBERS
-------------
PHONE_NUMBER_ID
PHONE_NUMBER_TYPE
COUNTRY_CODE
AREA_CODE
PHONE_NUMBER

And then you can have intersection tables for the many-to-many relationships, such as:

CUSTOMER_ADDRESSES
------------------
CUSTOMER_ID
ADDRESS_ID

CUSTOMER_EMAIL_ADDRESSES
------------------------
CUSTOMER_ID
EMAIL_ID

CUSTOMER_PHONE_NUMBERS
----------------------
CUSTOMER_ID
PHONE_NUMBER_ID

This is just one example, it can get much more involved than this.

One other thought: When it comes to address types, email types, phone number types, etc, those could be implemented via a check constraints or valid tables, depending on the amount of "churn" you have in add/removing types.

Upvotes: 2

Related Questions