0

Does the table design below violate the 2nf normalization principle or any normalization rule ( ie. 1nf,3nf)? I have read that 2nf is violated only if there are non key attributes that are functionally dependent to some composite keys rather than to the whole composite keys. However, I have read in other sources that the design such as the one below is a violation of 2nf simply because certain attribute value appears in multiple rows for certain key. However, phone is entirely dependent to user, and I don't understand how this violates 2nf.


record_id       name          phone
1               john         6789678
2               john         3879767 
  • 1
    Are both john the same? – Marco May 29 '17 at 6:14
  • Yes. He has two phone numbers. –  Enq19 May 29 '17 at 11:11
0

If (like you said in your remark) john has 2 phone numbers then it is a 1:n relation. In that case you need to store the phone in a separate table. You end up with:

Person

person_id (PK)
name

Phone

record_id (PK)
person_id (FK)
phone

You can leave out record_id and use person_id and phone as a primary key.

Since a person can have multiple phone you must 'double' the person information for every phone. In your example this is only name so is does not seem to be such a problem but imagine that a person has 100 phone and you must change his name. You will have to change all 100 rows.

Also think about 2 person that share the same phone.

  • 1
    But how does the original table violate 2nf? Or does it violate any specific normalization rule? I cannot find any specific normalization form which it may have violated. Thanks –  Enq19 May 29 '17 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.