I have entities Objects, Parts, Subparts and Items and the following restrictions:

  • A Part belongs to one and only one Object.
  • A Subpart belongs to one and only one Part.
  • An Item belongs to one and only one Subpart.

I have implemented the following tables (primary keys marked with *):

  • Object (*IDObject)
  • Part (*IDPart, *IDObject): IDObject is a foreign key (the primary key of Object)
  • Subpart (*IDSubpart, *IDPart, *IDObject): *IDPart, *IDObject is a foreign key (the primary key of Part)
  • Item (*IDItem, *IDSubpart, *IDPart, *IDObject): IDSubpart, IDPart, IDObject is a foreign key (the primary key of Subpart)

Is this design is in 3NF?

Item is a weak entity depending on Subpart, Subpart is a weak entity depending on Part, and Part is a weak entity depending on Object, so by theory we get those composite keys, making this design in 3NF.

But for other people, this design is redundant, and the best way for resolving it is through the following tables (primary keys marked with *):

  • Object (*IDObject)
  • Part (*IDPart, IDObject): IDObject is a foreign key, but doesn't make part of the primary key of Part
  • Subpart (*IDSubpart, IDPart): IDPart is a foreign key, but doesn't make part of the primary key of Subpart
  • Item (*IDItem, IDSubpart): IDSubpart is a foreign key, but doesn't make part of the primary key of Item

In this specification, the restrictions of "one and only one" would be managed by code.

Why would I choose this implementation, declining to use 3NF?


1 Answer 1


Both designs are technically 3NF, depending on your data population.

What a lot of people who have only a cursory understanding of formal normalization theory don't understand is that redundancy in foreign keys isn't really redundancy by definition. Transitive dependencies and partial dependencies apply to the way non-key attributes relate to key attributes.

If your composite keys happen to contain (or even consist of) foreign keys then whether your foreign key has the look of being denormalized isn't relevant. (i.e. Design 1 isn't redundant in a way which is significant to the formal definition of normal forms.)

The practical issue comes down to what the values of your keys are. If you have natural keys and the Part key consists of IDObject=123 and IDPart=1 - i.e. your IDPart values are natural number sequences which are only unique within the context of a given IDObject value, then you need to use design 1 (weak entities).

However, what a lot of people do for good (and less good) reasons is apply surrogate primary keys to every or nearly every table. I'm not going to get into whether or why to use surrogate keys. That is a holy war for another question. However, if you happen to have table-wide unique (e.g. IDENTITY) values for IDPart, then your primary key for Part is more complex than it needs to be using design 1. In that case, there is no need for IDObject in the PK of Part because the table already has a unique key just using IDPart. In such a case, design 2 is sufficient and simpler, therefore better, as a rule of thumb.

In practice, I would say weak entities are much more common in logical models than in physical models, if for no other reason than that most physical models tend to use surrogate primary keys.

  • The answer is very valuable for its clarity and completeness, by involving several key concepts about the logical modeling and physical implementation of a database.
    – villamejia
    Apr 23, 2014 at 12:21
  • What can be said about normalization when replacing the composite natural key of a logic N to M relationship by a subrogate key?
    – villamejia
    Apr 23, 2014 at 12:37
  • 1
    @villamejia - Adding a single surrogate key to the intersection entity that implements a logical M:N is a difficult case. To answer your question directly, it is not a subject for normalization per se. There may be practical reasons for doing this, especially in the physical model and especially if the relationship itself is a parent of some third entity. However, I think it is often seen simply because some people dogmatically apply surrogate keys to every table in their database.
    – Joel Brown
    Apr 23, 2014 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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