In this page we will look at creating a timesheet entity and will show how we link this to both a Project and an Employee.

Employees work on 1 or many projects.

A Project can have 0, 1 or many employees working on them.

The rules for this are

1.Employees can have 0, 1 or many (∞) Projects

2.A Project can have 1 or many (∞) Employees working on it

How is this handled?

• If we added ProjectID to tblEmployee that would mean an employee could only work on 1 project

• If we added EmployeeID to tblProject that means only 1 employee could be assigned to a project

• So, to resolve this we create a bridging or join table, that will contain both the EmployeeID and the ProjectID.

• In this case we can create a Timesheet that records the work an Employee does on a Project.

When working on a Project an Employee keeps a timesheet.

So rewriting the rules to include a timesheet we get

1.Employees can have 0, 1 or many (∞) Timesheets

2.Projects have 1 or many (∞) Timesheets

3.Timesheets are for 1 employee

4.Timesheets are for 1 Project

If you look carefully you will see that the "many" relationships "Flip"

Fully attributed Entity Relationship Diagram

For the timesheet we want to record some additional data. For our example, this will include, Date, Start and End Times and a Description of the work done.

Note: A PK can be made up of two or more Foreign Keys (e.g., EmpID* + ProjectID*), but in this case an employee may work on a project more than one so this won't work and it is better to have a special TimesheetID. You could add the TsDate to the PK (so the PK is EmpID* + ProjectID* + TsDate) but this makes working with the data that much more difficult (e.g., creating a form or report).