Search code examples
phpmysqlsqldatabasenormalization

Is this a 1NF failure?

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?

Solution

  • 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.