0

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
  • 2
    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
  • 4
    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
  • How is this not just asking for yet another presentation of normalization from absolute scratch? Please follow a textbook & ask 1 specific question where you are 1st stuck. See help center & the voting arrow mouseover texts. PS Normalization has nothing to say about FKs or NULLs. PS What to do with null values when modeling and normalizing? – philipxy May 3 at 2:09
0

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.

| improve this answer | |
0

Is this properly normalized?

  1. It is possible to enter mismatched (buildingID, areaID) in Room; in other words, have a room in an area of a building which does not belong to the building that the room is located in.
  2. Say that Room.areaID is NULL. Does it mean that the room is not in one of defined areas, or does it mean that the area is currently unknown?

Given that normalization is all about removing and preventing logical errors (plus some technical jargon) from a DB, the answer is no.


As Eric S. suggested, the easiest would be to allow for an 'unspecified' area to exist for every building. Then, with small modification of the original example:

-- Building BLD exists.
--
building {BLD}
      PK {BLD}

-- Area ARE is located in building BLD.
--
area {ARE, BLD}
  PK {ARE}
  FK {BLD} REFERENCES building {BLD}

-- Room ROM is located in area ARE.
--
room {ROM, ARE}
  PK {ROM}
  FK {ARE} REFERENCES area {ARE}

Or this one:

-- Building BLD exists.
--
building {BLD}
      PK {BLD}


-- Building area number ARE# of building BLD exists.
--
area {BLD, ARE#}
  PK {BLD, ARE#}
  FK {BLD} REFERENCES building {BLD}


-- Room number ROM# of building BLD
-- is located in area number ARE# of that building.
--
room {BLD, ROM#, ARE#}
  PK {BLD, ROM#}
  FK {BLD, ARE#} REFERENCES area {BLD, ARE#}

Where ARE# is in {0,1, ...} for every building, and ROM# is a room number in that building.


However, if we can not have a generic (unspecified) area for each building:

-- Building BLD exists.
--
building {BLD}
      PK {BLD}


-- Room number ROM# of building BLD exists.
--
room {BLD, ROM#}
  PK {BLD, ROM#}
  FK {BLD} REFERENCES building {BLD}


-- Building area number ARE# of building BLD exists.
--
area {BLD, ARE#}
  PK {BLD, ARE#}
  FK {BLD} REFERENCES building {BLD}


-- Room number ROM# of building BLD
-- is located in area number ARE# of that building.
--
room_area {BLD, ROM#, ARE#}
       PK {BLD, ROM#}

      FK1 {BLD, ROM#} REFERENCES room {BLD, ROM#}
      FK2 {BLD, ARE#} REFERENCES area {BLD, ARE#}

Or, using (mostly) single-column keys:

-- Building BLD exists.
--
building {BLD}
      PK {BLD}


-- Room ROM is located in building BLD.
--
room {ROM, BLD}
  PK {ROM}
  SK {ROM, BLD}

  FK {BLD} REFERENCES building {BLD}


-- Area ARE is located in building BLD.
--
area {ARE, BLD}
  PK {ARE}
  SK {ARE, BLD}

  FK {BLD} REFERENCES building {BLD}


-- Room ROM located in building BLD,
-- is located in area ARE, in that building.
--
room_area {ROM, BLD, ARE}
       PK {ROM, BLD}

      FK1 {ROM, BLD} REFERENCES room {ROM, BLD}
      FK2 {ARE, BLD} REFERENCES area {ARE, BLD}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename ARE# to ARE_NO.
| improve this answer | |

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.