Tables & Relationships

[JoJo]

I need some help! My company wants to track its employees by the department
in which they work, the job assignments, and the numbr of hours assigned to
each job. Employees can only be assigned to one department, but departments
can have many employees or none. Each department has a supervisor even if it
doesn't have any employees. Employees need to be assigned at least one job
but can have multiple assignments. The jobs themselves can have many
employees working on them or none at all.
Can anyone suggest what info to use in my tables and how I can link the
tables?
Any guidance is extremely appreciated!

 

[Jeff Boyce]

It sounds like you have:

* Employees
* Departments
* Jobs

and

* Employees-in-Departments
* Employees-assigned-Jobs

This sounds like 5 tables. The first three have data specific to the
titles. The last two have data specific to the combinations of Employee &
Department, and Employee & Job -- these are sometimes referred to as
"junction" tables (or "resolver" tables, or ...).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

To amplify slightly on Jeff's reply you might or might not need a table to
model the relationship between Departments and Employees. It depends on
whether or not you wish to record only the employee's current department, or
whether you wish to record their employment history if they move from one
department to another. In the former case you'd simply need a DepartmentID
foreign key column in Employees referencing the primary key of Departments.
In the latter case you'd have a separate DepartmentEmployees table with
foreign keys DepartmentID and EmployeeID, along with columns such as
DateJoined, DateLeft etc to model the attributes of each employees period of
employment in a department.

As the relationship between Employees and Jobs is Many-to-many you will of
course require a JobEmployees table to model that with foreign key columns
EmployeeID and JobID, along with columns such as HoursAssigned etc to model
other attributes of each assignment.

If you are recording employees' history you might also want to include a
DepartmentID column in this JobEmployees table so that you can identify which
department the employee was in when assigned to this job, i.e. this table
would be related to the DepartmentEmployees table on the composite key made
up of the EmployeeID and DepartmentID columns, rather than to the Employees
table on the EmployeeID key. That would be fine if an employee only works
for one distinct period of time with any one department, but if an employee
might work for a time with one department, then with another, but then move
back into the first department (in a more senior position for instance) you'd
also need to bring the date of the job into the relationship so that each row
in DepartmentEmployees maps to only one row in JobEmployees.

Ken Sheridan
Stafford, England