table design quest

[PMac]

Hi,

I have an Employee database design I have been working on -

tblEmp -Emp_Id links the following tables:
tblFulltime - Emp_Id, Position_Id

tblParttime- Emp_Id, Position_Id
tblTemp- Emp_Id, Position_Id

tblPositions - Position_Id

tblBuildings - Building_Id

each of them has different information that is why I separated them. Here is
the problem...I have a building table (tblBuilding) now, do I put the
Building_Id into the tblEmp or into tblFulltime, tblParttime etc.?

Thanks for any help!
Pete

 

[Bruce]

I would think BuildingID would be the foreign key in tblEmp, since each table
can have many people working there but each employee (presumably) works in a
single building. You have already linked EmpID to FullTime and PartTime.
Or are the tables FullTime and PartTime for listing different positions, and
the positions themselves (rather than the employees) are specific to
particular buildings? In that case BuildingID would be the FK in FullTime
and in PartTime.
What is in the Buildings table? If it is just for a building name, you
might want to consider storing that rather than the PK. If the Buildings
table contains other information such as address, store the ID.
If you need more information it will be necessary to know a little more
about what the tables contains and what the DB does.

 

[Bruce]

I would think BuildingID would be the foreign key in tblEmp, since each table
can have many people working there but each employee (presumably) works in a
single building. You have already linked EmpID to FullTime and PartTime.
Or are the tables FullTime and PartTime for listing different positions, and
the positions themselves (rather than the employees) are specific to
particular buildings? In that case BuildingID would be the FK in FullTime
and in PartTime.
What is in the Buildings table? If it is just for a building name, you
might want to consider storing that rather than the PK. If the Buildings
table contains other information such as address, store the ID.
If you need more information it will be necessary to know a little more
about what the tables contains and what the DB does.

 

[PMac]

Hi Bruce,

Thanks for the quick reply.

The Building table has more information than just name...address, country,
region a couple other fields.

The positions are linked to the Buildings...the position number is in that
particular building.

The Full, Parttime, and Temp tables have information in them such that is
only for a full time emp or a part time emp or a temp emp.

I put Building_Id as a FK in tblPositions.

At first I stored the Building_Id in the tblFulltime, tblParttime tblTemp
tables but found it difficult to write a query where I could list buildings
and who was located in a particular building ie a listing of full, part, temp
employees by building.

 

[Jack MacDonald]

You might want to consider the following change to your fundamental
table structure:

tblEmployees -- primary table, will have one record for all employees,
regardless of their status. Will contain a foreign key to the
Buildings table. Primary Key is Emp_ID

tblFullTime
- primary key is Emp_ID
- contains fields UNIQUE to full-time employees
- one-to-one relationship to tblEmployees
- has records only for fulltime employees


tblPartTime
- primary key is Emp_ID
- contains fields UNIQUE to parttime employees
- etc

tblTemp
- primary key is Emp_ID
- (you get the picture)


By consolodating the common information into a single table, you make
it easier to query for all employees.


I have used this concept for managing trucks and trailers, by creating
a "Unit" table. This table holds information that is common between
trucks and trailers (serial number, manufacturer, date of manufacture,
etc), while separate Truck and Trailer tables hold the information
that is unique to the different types of equipment.

HTH

Hi Bruce,

Thanks for the quick reply.

The Building table has more information than just name...address, country,
region a couple other fields.

The positions are linked to the Buildings...the position number is in that
particular building.

The Full, Parttime, and Temp tables have information in them such that is
only for a full time emp or a part time emp or a temp emp.

I put Building_Id as a FK in tblPositions.

At first I stored the Building_Id in the tblFulltime, tblParttime tblTemp
tables but found it difficult to write a query where I could list buildings
and who was located in a particular building ie a listing of full, part, temp
employees by building.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[PMac]

Thanks Jack!

You might want to consider the following change to your fundamental
table structure:

tblEmployees -- primary table, will have one record for all employees,
regardless of their status. Will contain a foreign key to the
Buildings table. Primary Key is Emp_ID

tblFullTime
- primary key is Emp_ID
- contains fields UNIQUE to full-time employees
- one-to-one relationship to tblEmployees
- has records only for fulltime employees


tblPartTime
- primary key is Emp_ID
- contains fields UNIQUE to parttime employees
- etc

tblTemp
- primary key is Emp_ID
- (you get the picture)


By consolodating the common information into a single table, you make
it easier to query for all employees.


I have used this concept for managing trucks and trailers, by creating
a "Unit" table. This table holds information that is common between
trucks and trailers (serial number, manufacturer, date of manufacture,
etc), while separate Truck and Trailer tables hold the information
that is unique to the different types of equipment.

HTH






**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security