Is this a 1NF failure?

Question

In considering a 1NF failure, no repeating groups of elements, what if you wanted to have a set limit on the number of a repeating group?

For example, you want a student to only have 3 phone numbers listed. No more. Would having a table as follows be considered a 1NF failure?

Student 1    Phone1    Phone2    Phone3
Sally        111-1111 222-2222   333-3333
John         555-5555 999-9999   NULL

You would be creating a limit. Is this acceptable, efficient database design?

Would it be better to put phone numbers in a separate table, as 1NF failures call for? How would you create the limit of 3 numbers per user if it were in separate table?

Answer 1

No, it is not normalized. You will have wasted space in your table when there are null values, and if you want to do things like search for a particular phone number, you'll have to search all three columns. Instead use a separate table (StudentPhoneNumbers, for example) that stores them. If you want to limit it to three, use a trigger to prevent more than three per student.

Answer 2

1NF bans repeating lists in a row. Your design violates this, and so would the following design:

Student     Phones
'John D'    '555-5555, 666-6666, 777-7777'
'Sally S'   '111-1111, 222-2222'

The following design would violate 2NF, because the only primary key is Name, Phone, but the Address attribute does not depend on the Phone:

Name        Phone       Address
'John D'    '555-5555'  '1 Square Village'
'John D'    '666-6666'  '1 Square Village'
'John D'    '777-7777'  '1 Square Village'
'Sally S'   '111-1111'  '999 Flash City'
'Sally S'   '222-2222'  '999 Flash City'

The next design would violate 3NF, because AreaName does not depend on Name, but only on Area:

Name        Area    Phone   AreaName
'John D'    '555'   '5555'  '111name'
'John D'    '666'   '6666'  '666name'
'John D'    '777'   '7777'  '777name'
'Sally S'   '111'   '1111'  '111name'
'Sally S'   '222'   '2222'  '222name'

Even if your design violates 1NF, it's an excellent choice. The complexity of adding a PhoneNumber table is hardly ever justified.

Think about how hard an update to a customer becomes if you conform to 1NF. The numbers would be in a separate table. So if someone submits a form with an updated list of phone numbers, how would you change the database? First you'd have to retrieve the existing list of numbers. Then you'd have to compare them to the submitted list. Then you'd have to delete or insert rows based on the difference. One heck of a complex solution.

If you stick to your solution, you can just update the three columns. The saved time can be spend on real features! Or even writing long answers on Stack Overflow.

Answer 3

Your relation variable (relvar) indeed violates 1NF but perhaps not for the reason you are expecting: it is the presence of the null that violates 1NF. If you think your relvar contains a repeating group, think again.

First normal form, or simply "normalized", is the minimum requirement for the relational model. To quote Chris Date:

by definition, a null isn't a value. It follows that: A "type" that contains a null isn't a type (because types contain values); A "tuple" that contains a null isn't a tuple (because tuples contain values); A "relation" that contains a null isn't a relation (because relations contain tuples, and tuples don't contain nulls). In fact, nulls violate the most fundamental relational principle of all—viz., The Information Principle. The net of all this is that if nulls are present, then we're certainly not talking about the relational model (I don't know what we are talking about, but it's not the relational model); the entire edifice crumbles, and all bets are off.

The point about repeating groups and 1NF is a tricky one to explain and I won't try. Instead, I urge you to read Facts and Fallacies about First Normal Form, specifically the section "The ambiguity of Repeating Groups".

Assuming the null was eliminated, the relvar would satisfy 1NF but note we would need further information (e.g. keys) to determine whether it would also satisfy higher normal forms.