databasedev.co.uk - database solutions and downloads for microsoft access

Employees and Projects Scenario

The following data model is designed to hold information relating to Employees working on company Projects. For this scenario we need to define the following facts:

These facts define the requirements which the Database must meet and should be agreed between the Database User and the Database Designer prior to physical creation.

The draft facts have been defined as:

The Entities required should include:

• Employees
• Projects
• Departments

The Entities are related as follows:

• An Employee can work on Many Projects
• A Project can have Many Employees associated with it
• A Department can have Many Employees
• An Employee can only work in One Department

The design allows recording of how long an Employee has spent on a given project by including Work_Date and Hours in the LINK table that is supplied to break up the Many-To-Many relationship between Employees and Projects.

When asking questions of the database we may need to know:

1. Which Employees are working on a certain Project
2. How many Hours have been spent on a certain Project
3. How many active Projects are currently being worked upon

The following data model allows these questions to be answered and allows the information contained above to be stored logically and in a structured manner.

To view the model please go to: Relationships for Employee Projects

Please download the data model relating to Relationships for Employees and Projects which is contained in the zipped up Microsoft Word document.