Changing table design needed?

[algecan]

Hi All,

I’m hoping you can help me with a problem.

I created a database for work a few years ago, that amongst other things, is used for storing details of jobs and also the time spent against these jobs. For these functions I used three table:

tblJob
Job_Ref PK
EmployeeID FK
Job_Year
Time Allocated

tblTime_Card
Time_Card_ID PK
WC_Date
EmployeeID FK

tblTimeSheet
Timesheet_ID PK
Time_Card_ID FK
Job_Ref FK
Monday_Time
Tuesday_Time
Wed…

All works well. Each employee gets assigned a job in tblJob and an amount of time to complete the job. They complete their timesheets for this and other jobs so we can monitor how much time they have spent against their allocated jobs and other employees jobs. We also have admin ‘jobs’ stored in tblJobs. These aren’t allocated to a specific employee and do not have an allocated time but employees can still record time to these admin jobs so we can monitor how much is being spent doing photocopying for example. One thing we do like about this is that the admin job codes never change. For example photocopying might be reference 1001 for example, it just makes it slightly easier to use, whereas every time an employee get a new job to complete they get a new job reference.

The problem is that we now want to allocate time for each employee to these admin jobs so that we can monitor time spent against allocated time just like normal non-admin jobs. However I can’t think of a way to implement this whilst appeasing management who want to make sure the admin jobs keep the same reference.
Using the current table structures, to allocate an employee and time to an admin job, Photocopying might be 1001 for employee 1 in 2015, 1002 for employee 2 in 2015, 1067 for employee 1 in 2016, 1068 for employee 2 in 2016 and so on and so. This might work for normal jobs because once they have finished that job they won’t code time to it anymore so job references don’t matter. However admin jobs will always be here hence why management don’t want their reference codes to change. Plus it would be a nightmare trying to report on all time spent to photocopying if there are dozens of different codes for it.
I did try and have a play about with creating different tables for admin codes, and keeping allocated times and employees in it that way but then this would mess up the timesheet as there is just one field to select a job reference from, unless we then have a job reference field and an admin reference field linking to the two relevant tables.

Any ideas on how to implement this would be greatly appreciated.

 

[plog]

First and least, tblTimeSheet is set up improperly. You shouldn't store relevant data in table/field names. Monday_Time, Tuesday_Time, etc. shouldn't be fields. Instead of growing tables horizontally (with more fields), they should grow vertically (with more rows). So Monday's time would go in 1 record, Tuesdays in another, etc..

However, when I look at them as a whole, your tables don't make sense.

1. tblJob shouldn't have [Time Allocated] as a field. Isn't that just the summation of the tblTimeSheet table values? Don't store calculated/redundant data.

2. tblTime_Card is a confusing table. I think a better table for this is tblPayPeriods (this is based on my guessing WC stands for something like WeekClosed and represents when a pay period ends). I don't think EmployeeID should be here, instead EmployeeID should be in tblTimeSheet and a foreign key to tblPayPeriod.

3. I think EmployeeID should be changed to ProjectManager. It sounds like you allocate a person to job to be in charge of it, but anyone can allocate time to a project (e.g. admin jobs). Calling it EmployeeId is confusing and makes it seem only 1 person can allocate time to a Job.

Here's how I think those 3 tables should be set up (I'm using my naming style):

tblJob
job_ID, autonumber, primary key
ID_Manager, number, foreign key to Employees
job_StartDate, date, date job was created
job_Completed, date, date job was finished and no longer available to designate time to

tblPayPeriod
pp_ID, autonumber, primary key
pp_EndDate, date, end date of pay period

tblTimeSheet
ts_ID, autonumber, primary key
ID_Job, number, foreign key to Jobs
ID_Employee, number, foreign key to Employees
ts_Date, date, date work was done
ts_Hours, number, number of hours allocated (this could be ts_Minutes depending on how granular you want to store allocated time)

Looking at it, I'm not in love with tblPayPeriod. That could be done logical by looking at ts_Date and using an expression to allocate it to the correct week.

 

[algecan]

Hi plog, thanks for the response.

The timesheet element was set up as above as time sheets are done on a weekly basis. Hence why tblTime_Card has just the week commensing date and employee ID, that way tblTimeSheet will show all the jobs that an employee has coded time to for that week. It may not be the perfect design but it certainly works for us and any redesign of these tables would surely require a lot of additional redesign of the numerous queries, forms and reports built on top of these. Time allocated isn't a calculated field, this is the amount of time an employee has been given to complete the job. For example they may have 10 days to complete job 2198. We can then query the timesheets to see how much time has been coded to 2198 and compare it to this field to see if there is any overrun etc.

The issue I am having at the moment isn't with the timesheets however, it is I believe with tbljob. For a table that was only set up to have 1 employee allocated, 1 amount of allocated time, how do I then change this to certain jobs (admin jobs) have more than 1 employee allocated each with there own set amount of allocated time. Would this be another table?
tblJob
Job_Ref PK
Job_Complete



tblJob_Details
JobDetails_ID PK
Job_Ref PK
Employee_ID
Year
Allocated_Time

 

[plog]

Yes, it would be another table as you have laid out. Actually, year may remain in tblJob, depending on if that is really part of the job or part of the allocation. And I wouldn't use the name 'Year', its a reserved word and will make things a little more difficult to code.


I still advise fixing everything though.