Step-2: Develop the data model

The diagram below shows the timesheet data model. It consists of nine tables. These tables can be categorized as lookup tables that consist of data that doesn't change often, and transactional tables that consist of data that changes often as end-users use the application.

Lookup tables

Out of the nine tables, the following seven tables can be considered as the lookup tables for the Timesheet Application:

1. Customer
2. Project
3. Task
4. Users
5. Usertask
6. Timeofftype
7. Timesheetstatus

These tables are defined below:

1. Customer - consists of a list of the organization's customers

2. Project - consists of a list of projects for each customer

3. Task - consists of a list of tasks for each project

4. Users - users of the Timesheet Application. They can take on the role of an employee, a manager, a developer, or an admin

5. Usertask - the list of tasks assigned to a employee. In this application, employees can complete and submit timesheets. An employee completes the daily timesheet by allocating X number of hours to each customer/project/task. The list of tasks available for an employee to fill the hours may be different from those available to other employees. Therefore, an Admin will assign the list of tasks to each user in the employee role, and this information is stored in this Usertask table

6. Timeofftype - consists of the different types of time offs taken by an employee such as Paid Time Off and Company Holiday

7. Timesheetstatus - consists of the status types for a timesheet such as open, submitted, and rejected

Transactional tables

The following tables can be considered as transactions tables for the Timesheet Application.

1. Timesheetdetails - timesheetdetail consists of Customer/Project/Task/Hours completed by an employee. A working day may have one or more timesheetdetails.

2. Timesheet - a timesheet is composed of one or more timesheetdetails for a period of 2 weeks. For example, if an employee has worked on 4 different tasks each day for two weeks, the number of timesheet details in that 2-week period would be 4 x 10 = 40.