CODEWITHSUNDEEP
sqldatabaserelational-databasedatabase-normalizationfirst-normal-form
Kaya Toast
Kaya Toast

Reputation: 5503

First normal form violation

Let's say a user can have multiple phone numbers.

I can understand that the first table below is a violation of 1NF, as the userID=2 is repeated.

+--------+-------+
| userID | phone |
+--------+-------+
| 1      | 1010  |
| 2      | 1020  |
| 2      | 1021  |
| 3      | 1030  |
+--------+-------+

But is this second table also a violation of 1NF ? Yes, it appears to be a bad, inflexible design - but is it violating 1NF ?

+--------+--------+--------+
| userID | phone1 | phone2 |
+--------+--------+--------+
| 1      | 1010   |        |
| 2      | 1020   | 1021   |
| 3      | 1030   |        |
+--------+--------+--------+

Upvotes: -1

Views: 1779

Answers (3)

Mario Cardoso
Mario Cardoso

Reputation: 34

Actually the first table isn't violating the 1st NF because there are no multi-valued attributes. It's respecting the normal form. The second table follows the same logic as the 1st.

You can check it in the image explanation of the tutorial in the link that shows a violation and a refactor of the violation to the normal form. (https://www.geeksforgeeks.org/first-normal-form-1nf/) enter image description here

Upvotes: 0

christopher_pk
christopher_pk

Reputation: 651

It is a violation of 1NF. 1NF requires that

  1. There are no multiple-valued fields in the table.
  2. There are no repeating groups in the table.

phone1, phone2 etc are repeating groups, violation of 1NF.

repetition of userID=2 does not violate 1NF itself.

Upvotes: 1

No'am Newman
No'am Newman

Reputation: 6477

What happens to your second design when you have a third telephone number? It may not be a violation of 1NF but it's poor design. I, for example, have a home telephone number, a work telephone number, a mobile telephone number and a fax number. The ERP program which I use at work has a 'user contacts' table which has fields for each of those numbers. This allows for easier output but can sometimes lead to problems if a person has more than one number of a given type.

The standard way of handling multiple telephone numbers is to have a separate table with fields owner, telephone number and description.

Upvotes: 0

Related Questions