If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
table design quest
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 |
#2
|
|||
|
|||
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" wrote: 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 |
#3
|
|||
|
|||
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" wrote: 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 |
#4
|
|||
|
|||
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. "Bruce" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
You might want to consider the following change to your fundamental
table structu 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 On Wed, 29 Dec 2004 10:47:07 -0800, "PMac" wrote: 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. "Bruce" wrote: 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" wrote: 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 ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#6
|
|||
|
|||
Thanks Jack!
"Jack MacDonald" wrote: You might want to consider the following change to your fundamental table structu 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 On Wed, 29 Dec 2004 10:47:07 -0800, "PMac" wrote: 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. "Bruce" wrote: 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" wrote: 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 ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help w/ table design | Tom | Database Design | 0 | August 12th, 2004 02:34 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
Convert linked table design to local table | Dave Venus | Database Design | 5 | June 17th, 2004 12:05 PM |
Table design question - advice needed | David | Database Design | 3 | June 8th, 2004 02:21 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |