2

From all of the database related reference books that I have come across, there is a universal rule that state a database table should be at least 3NF.

From what I understand, a table is considered 2NF if it satisfies 1NF and it has single column primary key. Correct me if I am wrong.

So I couldn't understand why there is so much argument of having composite key especially for entities that relate M:N tables; eg:

A Product_Customer table, with the columns:

  • CustomerProductID
  • CustomerID
  • ProductID

Some say that primary key (CustomerProductID) is not needed and it should use composite key (CustomerID, ProductID) instead.

But doesn't that violate the universal rule of a table should have at least 3NF since using a composite key will not even satisfy 2NF?

Correct me if I am wrong, especially the part where I understand that if a table has composite keys, it will not be in 2NF.

7

Your definition of 2NF is not quite correct.

2NF is when a relation is in 1NF and it has no partial dependencies, meaning there are no predicates (columns) that depend on only part of a multi-part key.

What that means is that a table where all the (one or more) keys are single column ones cannot violate 2NF by definition. However, it's also possible to have a table with a multi column (composite) key which also doesn't violate 2NF. It's not about how many columns are in your key, its about whether there are non-key columns which depend on only part of any candidate key.

Since it's possible for a table to have a multi column key be in 2NF, it's also possible for a table with a multi column key to be in 3NF. You just have to make sure there are no transitive dependencies as well.

  • 1
    A table with a single column key can violate 2NF if it has multiple keys. – David Browne - Microsoft Aug 31 at 15:55
  • @David right, corrected.t – ypercubeᵀᴹ Aug 31 at 19:44
  • 1
    @DavidBrowne-Microsoft I wouldn't say that multiple single column candidate keys would allow for a partial dependency (and therefore a violation of 2NF). Every non-key column would have to be functionally dependent on each candidate key (otherwise it wouldn't be a candidate key). To violate 2NF you need a composite candidate key. – Joel Brown Aug 31 at 19:47
  • 1
    Yes. I just meant that the addition of a single column key wouldn’t “fix” a non 2NF table. – David Browne - Microsoft Aug 31 at 20:18
  • 1
    @DavidBrowne-Microsoft Yes, quite so. Too many people are too quick to throw an IDENTITY / AutoNumber into a table thinking they no longer have to worry about other candidate keys and insert/update anomalies. – Joel Brown Aug 31 at 21:25

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.