Hi Guys,

I need a little design help here, I hoping someone can advise me here.

I’m working on an employee database.

In this database I need to hold Employee Records, the Campus they’re in and the Floor they’re on.

I want to have 1 form where I can select both their Building and Floor their on along with their other employee details.

I have an Employee table and a Buildings table.

My first question is…would I need to store the Floors in a separate table. I think I will have to?

Secondly as a quick description I have the following 3 tables…does the design look right? I’m a little unsure…

tblEmployee
EmpID
FName
LName
BuildingID
FloorID

tblBuilding
BuildingID
BuildingName

tblFloors
FloorID
FloorName
BuidlingID

Any advice or guidance is appreciated…

Thanks
Sukh

theoretically you probably ought to store the floor Id in a separate table, but I think its overkill unless you need to separate out the floor at some time.

Id be tempted to enforce the floor as part of the room ID eg room 423 = floor 4 room 23
or store it in the employee record as a separate element

if you need to ensure that no crap data gets in then by all means have a separate entity

Hi,

Thanks for your response,

I'm not storing information on rooms, but Buildings and Floors.

so an employee works in Building A, Floor 6.

But I guess the same principle could apply.

Like.... Building 1 Floor 1, Building 1 Floor 2...or am I missing the point?

its your design to fulfill your design requirement.
if there is no compelling reason to have an entity for floors, then dont' have one. if there is a need then have one. you need to be concious of why you are making such desing decisions, and weighing up the tradeoff between tables / development time against user requirements.

if all you want to do is record the floor soemone works on and do not intend defining that a specific building has n floors then don't bother with a separate table.