Hi Everyone: I am trying to come up with a good db design for time tracking an employee's time on one of the client projects that I am working on. The background is as follows: 1). An Employee can work on administrative tasks and also some special projects. 2). An Employee can also work on customer issues. 3). If an employee is on a vacation he will not work neither on any projects or customer issues. The tables I have designed so far look like this: Table 1: Admin --------- AdminID AdminDesc The Admin Desc can include: Vacation,General tasks,Sick,Training etc. Example data AdminID Desc ------ ----- 1 Vacation 2 General 3 Sick 4 Training 5 Time Tracking Table 2: Projects --------- ProjectID ProjectDesc The Project Desc can include: ProjectA, ProjectB, ProjectC etc. Example data ProjectID ProjectDesc ------ ----------- 1 ProjectA 2 ProjectB 3 ProjectC 4 ProjectD 5 ProjectE Table 3: Customers --------- CustomerID Name ProblemType The Customer Name can include: Customer1, Customer2, Customer3 etc. The ProblemTypecan include: DR, Performance, Querytuning etc. Example data CustomerID Name ProblemType ------ ----- 1 Customer1 Disaster Recovery 2 Customer1 Performance 3 Customer2 Backup issue 4 Customer3 Query Tuning Finally My EmployeeTable for Time tracking would be: Table 4: Employee --------- EmployeeID EmployeeName AdminID ProjectID CustomerID Timespentinhrs Example Data: EmployeeID EmployeeName AdminID ProjectID CustomerID Timespentinhrs 10 Rob 4 NULL NULL 2 10 Rob NULL 2 NULL 10 10 Rob NULL NULL 1 25 10 Rob NULL NULL 1 10 10 Rob NULL NULL 2 3 The total time spent by an employee in the above case would be :50 I need to know if this can be designed in a better way? Also How do I track the problemtype for an employee for a customer? Also is it a bad idea to include the problemtype for the customer in the Employee Table? please give your thoughts and suggestions. Thanks Ankith.
