Some Employees work at all facilities

[LMB]

Hi Guys,

Suffice it to say I am a beginner. I have made and edited some databases with help from the group and now am attempting to create a new "by the rules" employee database. This is a database to keep track of the employees in the respiratory department. We have 3 facilities. Each facility has a respiratory department. Some employees only work at once facility but there are some that work at 2 or all three. Right now I have 3 separate databases for each facility. I may keep it that way but I was hoping to use the demographics portion of the Employee table for all of the facilities. I am stuck on where the hire dates would go. This is what I have on paper so far but it just doesn't look right. These are the hire dates each employee can have.

MHPHireDate (date employee was hired in system) Possible to have only 1 per employee
FacilityHireDate (date employee was hired at facility) Possible to have 3 for an employee
DeptHireDate (date employee was hired in specific Department) Possible to have 3 per emp.

It may be confusing that facility and dept could be different but an employee could have been hired at a facility in the dietary dept then transfer to the respiratory dept.

These dates determine seniority for different events that will be tracked in the database.

This is what I have so far...

tblEmployee
EmployeeID
EmployeeLName
EmployeeFName
MHPHireDate

tbllstFacility
FacilityID
FacilityName

tblEmployeeFacility
EmployeeFacilityID
EmployeeID
FacilityHireDate
DepartmentHireDate

Do I need to put Department in a different table?



Thanks,
Linda

 

[Guest]

I will assume that the field named (Something)ID in the first two tables is
the primary key (PK) field. Since each employee can work at many (i.e. more
than one) facilities, and each facility can have many employees working
there, there is a many to many relationship between employees and facilities.
To resolve that you will need a junction table. I suggest the following
modifications to tblEmployeeFacility (FK stands for foreign key).

tblEmployeeFacility
EmployeeFacilityID (PK)
EmployeeID (FK)
FacilityID (FK)
FacilityHireDate

Establish a relationship between the PKs from tblEmployee and tblFacility
and the FK fields from tblEmployeeFacility. Remember that you define the PK
in table design, but a field becomes the FK because of its relationship to
the PK. Also, if the PKs are of the data type Autonumber the FKs need to be
Number; If the PKs are other than autonumber, the FKs need to be the same.
You may already know this, but I don't know that you know.
Department should probably be a separate table linked to tblFacility and
tblEmployee. If each employee in their careers will work at three
departments maximum you could keep that information in tblEmployeeFacility.
However, if an employee could possibly work at more than three departments,
you're sunk. Even if it seems impossible now, I really think it is best to
allow for that. tblDepartment would be constructed similarly to
tblEmployeeFacility, except the relationship would be to tblEmployeeFacility
rather than to tblFacility.
This approach involves forms and subforms for entering and viewing data. If
the structure suggested works for you and you need assistance setting up the
forms and subforms, post back with your questions.

 

[Jamie Collins]

tblEmployeeFacility

EmployeeFacilityID (PK)
EmployeeID (FK)
FacilityID (FK)
FacilityHireDate

I'd suggest a unique constraint on (FacilityID,
EmployeeID) or make this the PK, even.

Jamie.

--

 

[LMB]

Thanks Bruce,

I hope to get some time tonight to draw this out. Just had 3 days of 16 hour shifts. Yes, the top SomethingID is my primary key and is an autonumber. I did actually know about the FK needing to be a number but I am glad you included that. I learned this the hard way.

The rest of the tables in the database will have the same field names but the data will be specific for each employee at a specific facility. An example is something we call the clinical ladder level. Each facility has 4 levels but an employee may be a level 3 at one facility and a 2 at another. Will this change the way I have to structure the database below? An idea came to mind and I was thinking that perhaps I would have the tables that were the same for each employee in one database and then have 3 separate databases that link to these tables. Is it possible to link to data in a query?

BTW, I think I discovered how some of you guys reduce the post size by taking out the headers....Learned something else from you.

Linda
:

I will assume that the field named (Something)ID in the first two tables is
the primary key (PK) field. Since each employee can work at many (i.e. more
than one) facilities, and each facility can have many employees working
there, there is a many to many relationship between employees and facilities.
To resolve that you will need a junction table. I suggest the following
modifications to tblEmployeeFacility (FK stands for foreign key).

tblEmployeeFacility
EmployeeFacilityID (PK)
EmployeeID (FK)
FacilityID (FK)
FacilityHireDate

Establish a relationship between the PKs from tblEmployee and tblFacility
and the FK fields from tblEmployeeFacility. Remember that you define the PK
in table design, but a field becomes the FK because of its relationship to
the PK. Also, if the PKs are of the data type Autonumber the FKs need to be
Number; If the PKs are other than autonumber, the FKs need to be the same.
You may already know this, but I don't know that you know.
Department should probably be a separate table linked to tblFacility and
tblEmployee. If each employee in their careers will work at three
departments maximum you could keep that information in tblEmployeeFacility.
However, if an employee could possibly work at more than three departments,
you're sunk. Even if it seems impossible now, I really think it is best to
allow for that. tblDepartment would be constructed similarly to
tblEmployeeFacility, except the relationship would be to tblEmployeeFacility
rather than to tblFacility.
This approach involves forms and subforms for entering and viewing data. If
the structure suggested works for you and you need assistance setting up the
forms and subforms, post back with your questions.

 

[LMB]

Jamie,

What is a unique constraint?

Thanks,
Linda

tblEmployeeFacility
EmployeeFacilityID (PK)
EmployeeID (FK)
FacilityID (FK)
FacilityHireDate

I'd suggest a unique constraint on (FacilityID,
EmployeeID) or make this the PK, even.

Jamie.

--

 

[Jamie Collins]

What is a unique constraint?

See:

Microsoft Jet SQL Reference: CONSTRAINT Clause
http://office.microsoft.com/en-us/assistance/HP010322141033.aspx

BruceM's design has nothing to prevent the following:

INSERT INTO tblEmployeeFacility
(EmployeeFacilityID, EmployeeID, FacilityID, FacilityHireDate)
VALUES (1,1,1,DATE());

multiple times, possibly with different dates.

Jamie.

--