0

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   |        |
+--------+--------+--------+
Kaya Toast
  • 5,267
  • 8
  • 35
  • 59
  • 2
    First example is not a violation, since userId is atomic – Vincent Apr 09 '15 at 02:43
  • `userID=2` getting repeated seems to be a violation - at least according to this wikipedia example http://en.wikipedia.org/wiki/First_normal_form#Examples – Kaya Toast Apr 09 '15 at 03:23
  • It is a shame that anyone can just downvote a question, without an explanation ! ... it is a specific programming question. Mr. @moderator ? :-) – Kaya Toast Apr 09 '15 at 03:26
  • 1
    @KayaToast: You've misread the Wikipedia article. But [this SO answer](http://stackoverflow.com/a/23202535/562459) gives a much better explanation. – Mike Sherrill 'Cat Recall' Apr 09 '15 at 05:02

2 Answers2

1

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.

christopher_pk
  • 641
  • 4
  • 17
0

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.

No'am Newman
  • 6,395
  • 5
  • 38
  • 50
  • Thanks for your effort in responding. But it is a very specific question - does it violate 1NF ? I have already stated/acknowledged in my question that it is a bad design. – Kaya Toast Apr 09 '15 at 03:20