Table Design Question

[chelsea]

Hi,

I'm creating a database that will trac a lot of information about various
items in our inventory. The basic starting point I am at is trying to
standardize the locations of the items in several buildings. I have a list
of all the BUILDINGS, a list of all the FLOORS in each BUILDING, and a list
of all the ROOMS in each FLOOR in each BUILDING. I would like the user to be
able to select a BUILDING from a combobox on the main form, then a second
combo box automatically populate with all the applicable FLOORs for that
BUILDING. (I think this is called "cascading"?) When the user selects a
FLOOR, a third combobox will populate with all the applicable ROOMs for that
FLOOR for that BUILDING. The FLOOR numbers obviously reapeat in many of the
BUILDINGs, and many of the ROOMs repeat in both the FLOORs of each BUILDING
and also in several different BUILDINGs themselves.

Right now I'm just trying to figure out the best way to set up the tables
and relationships... any help would be appreciated!

 

[Douglas J. Steele]

I'd have 3 tables: one for Buildings, one for Floors (with a "2nd floor" row
in there for each building that has a second floor) and one for Rooms (with
a "Room 222" row in there for each building that has a Room 222)

While you could get by with a Floors table that has only as many rows as the
maximum number of floors in any of your building, you're probably going to
have attributes that are unique to the floor by building. Similar with
rooms.