3

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.

Improve this question

1 Answer 1

Reset to default
10

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.

Improve this answer
4
  • 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. Commented Aug 31, 2019 at 19:47
  • 1
    Yes. I just meant that the addition of a single column key wouldn’t “fix” a non 2NF table. Commented Aug 31, 2019 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. Commented Aug 31, 2019 at 21:25
  • All-CKs-simple violates 2NF when {} functionally determines a non-prime attribute, which is when the attribute has to have the same value in every row. Also 2NF is no partial dependencies where a CK determines a non-prime attribute. Commented Jan 12, 2023 at 1:02

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.