Building/location table

[Guest]

I have six buildings and am thinking of creating a "buildings" table that
will list only the six buildings.

Does it make sense to have a separate "location" table that would provide
"building" again PLUS room number and staff name?

I want users to pick buildings from a drop down but I don't know if it makes
sense to pick from hundreds of room numbers and hundreds of staff names.

 

[Guest]

Kinda hard to answer your question without knowing how the data will be used.

Six buildings is not much unless there are 85 floors with 250 rooms per and
45 staff in each.

I want users to pick buildings from a drop down

Will they be running a building-room population density query?

Think long term. Maybe these tables --
Building
Floor w/ BLDG as foreign key
Room w/ BLDG & flooras foreign keys
Staff - employee information
StaffAssign w/ w/ BLDG, floor, & Room as foreign keys

 

[Albert D.Kallal]

Yes, it would seem that you have a table called

tblBuilding

This could list the address, contact names for emergency, phones numbers
etc.

Then, you also have a related table called tblRooms

This would simply list all of the rooms, room numbers, size, capacity
and whatever else you need for room.

Does it make sense to have a separate "location" table that would provide
"building" again PLUS room number and staff name?

Not sure why you say "again PLUS" room number????

If you have staff assinged to a room, then we now have

tblBuildings --> tblRooms--> tblStaffInRoom

Each of the above tables would be a child table of the pervious. This would
means that one room can have many staff, and one building can have many
rooms......

 

[Guest]

Sounds like I'm engaging in overkill. These buildings are schools and each
has only one floor with a total of maybe 30 rooms. One staff per room.

With this scenario, I'd like to do this with one table but each of 3300 tech
inventory items has to be assigned to a school, a room, and a staff member.

I would like to do a drop down box for data entry purposes so that a user
can pick building, room, staff but can that be done with a single table?

 

[Guest]

So you are not tracking occupancy but inventory. In this you want a table
for inventory items not buildings. Below is a simple set for inventory.

EqID – Autonumber – primary key
Name – text
Description – text
Assigned – number – integer – foreign key from StaffID
Location - number– integer – foreign key from BldgID
AssignDate - datetime

BldgID – Autonumber – primary key
Building – text
Room – text
FPI – text – optional floor plan indicator

StaffID – Autonumber – primary key
Name – text
Position – text

You could split off Assigned, Location, and AssignDate into a separate table
to track re-assignment of the inventory. The table would be like –
EqID – number – integer – foreign key from EqID
Assigned – number – integer – foreign key from StaffID
Location - number– integer – foreign key from BldgID
AssignDate - datetime
Turn-inDate – datetime
Remarks – memo