3NF - Did I do it right?

Question

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#

normalization is all fun and games until you've got to join 50 tables together in every query — developerwjk, Feb 26 '14 at 00:37
@Randy: it's not indeed. But it has something to do with a customer — zerkms, Feb 26 '14 at 00:41
There might be some merit in splitting out the address information into its own table; you'd need some address identifier to include in the main customer table. I can see no justification for leaving the `Address` column out of the second table, and (like other commentators) I can see no justification for including DoB in the second table. If you think that there won't be enough repetition in the database, the table could be usably in 3NF. However, in principle, there could be multiple customers living at the same address. A given customer might have several email addresses and phone numbers. — Jonathan Leffler, Feb 26 '14 at 00:45
So the correct way to do this is to create another table with Address, City, Province, PostCode instead of the DOB. The reason why I included DOB was because I thought 2 customers might have the same DOB so it does not satisfy the NF rules. — UserFriendly, Feb 26 '14 at 01:48
Re "is this right": Show the steps of your work following your reference/textbook, with justification--not all terms/notations are standard & we don't know exactly what algorithm/method you are following & we want to check your work but not redo it & we need your choices when a process allows them & otherwise we can't tell you where you went right or wrong & we don't want to rewrite your reference. [ask] [help] [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) Basic questions are faqs, research before considering asking & reflect research. — philipxy, May 25 '23 at 23:08

score 2 · Answer 1 · edited May 25 '23 at 23:01

2

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.

edited May 25 '23 at 23:01

halfer

19,824
17
99
186

answered Feb 26 '14 at 00:46

Mark J. Bobak

13,720
6
39
67

Thank you for your detailed answer. It does help me grasp the idea much better, but i'm wondering if there is any possible way to create only 1 extra table and still meet the 3NF requirements. – UserFriendly Feb 26 '14 at 01:52
I forgot to mention that this is my test review question and it has only 1 requirement which is to reduce the table to 3NF. since I was taught about 3NF and how to implement this rule a couple of weeks ago, I am still in the process of understanding the concept and find it a bit hard to catch the drift as to why you needed to create so many different tables. it would be much aprreciated if you could help shed some light on me. Thanks! – UserFriendly Feb 26 '14 at 02:03
If each customer is only allowed one phone number and one email address, then you don't need the phone number and email tables. OTOH, lots of people do have more than one of each. – Jonathan Leffler Feb 26 '14 at 03:26

3NF - Did I do it right?

1 Answers1