I am creating a table as follows:

Building (PK: buildingID)
Area (PK: areaID, FK: buildingID)
Room (PK: roomID, FK: buildingID, FK: areaID)

All rooms are in a building. Some rooms can be in an area of a building. Rooms that are not in an area of a building have the FK: areaID set to NULL.

For example, areas of a building include: blue zone, green zone, red zone. Rooms can be in one of those areas, but may not be in an area and just part of the building itself.

Is this properly normalized? I feel that when the FK: areaID is set to a value then it is not normalized as the buildingID can be determined from the areaID.

If it is not normalized, how do I go about normalizing it so that the FK: areaID can still use NULL values?

  • Sorry, I was reading the relationships in reverse order. – ypercubeᵀᴹ Aug 28 '18 at 16:03
  • 1
    One way to make it really impressively normalized would be to have the building itself be an AREA. Every row of AREA would have a FK called "parent" which can be null, indicating a top-level building. Rooms could also be AREAs. And you could have areas within areas, an infinite hierarchy. That said, your schema is probably fine, there's little risk of anomaly unless you end up moving an Area from Building to Building. – workerjoe Aug 28 '18 at 16:05
  • 1
    You could just add an area called something along the side of "Unspecified Area" and make that the default to represent a room in no area. This lets you remove buildingID in Rooms which will remove the issue of having to check multiple places for a building ID and at worst having a room with a building ID that doesn't agree building ID associated with the area. – Eric S Aug 28 '18 at 16:23

Many designers use a NULL in a foreign key when there is an optional relationship, and this particular row is not participating.
There's nothing terribly wrong with this. When a join is done on this FK and the corresponding PK, the rows with NULL will just drop out. Other designers avoid this like the plague.

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.