Design Issue

[Dashsa]

I am making a database to record data about timesheets using a Form.
The issue I am having is that I have one employee working on multiple tasks during the day and I need to be able to split his time up acordingly so that it can be billed to each job.

I have a singe table called Data wich the Form inserts the info into but the i need to be able to capture all the items he works on like Painting: 2 hours; Flooring: 1 hour;
etc.
Should I just have multiple fieldsets called hours, hours1, hours2.... Description, description1, description2 ect.?

any advice would be great thanks
D

 

[Remou]

You should almost never have multiple fields Hours, Hours1. Why can you not have:

EmployeeID
JobID
StartDateTime
EndDateTime

?

 

[Dashsa]

I need to be able to record all the tasks he did on each day So that I can bill each client, So if he works 3 hours for Mr. Brown and 5 hours for Mr. Red I need to be able to split that time up and also have a detailed description of what work he did.
In the Data table I currently have the following fields:
ID(Auto Number)
Employee Name(Look up to a Table)
Jobname(Look up to a Table)
Payroll Item
Service Item
Date
Clock in
Clock out
LunchStart
LunchEnd
Description of Work
JobCode
Hours On task

The Last three fields I need to be able to record multiple entries for.

 

[Remou]

You need another table for time-in detail records. The foreign key is probably ID (above).

 

[SkipVought]

You have
[tt]
ID(Auto Number)
Employee Name(Look up to a Table)[red]
Jobname[/red](Look up to a Table)
Payroll Item
Service Item
Date
Clock in
Clock out
LunchStart
LunchEnd[red]
Description of Work
JobCode[/red]
Hours On task
[/tt]
Are [red]these items[/red] related? is there ONE Description of Work & ONE JobCode per Jobname? Seems that's redundent data.

Then you want Hours On task. That's a CALCULATED value from what will be stored. Gen that at report time.

Lunch is a Job (non-working time) Should not be separate field(s)
[tt]
EmployeeID
JobID
DatePost
TimeIN
TimeOUT
[/tt]
give you everything that you need, I believe.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[BobRodes]

<almost never

I say NEVER!!! Seriously, this is one of the most egregious violations of database theory that exists.

Ok, I see three kernel entities here:

Employees
Jobs
Clients

And one associative entity:

Workunits

Attributes are as follows, given your data:

Employees
ID
Name
(etc)

Jobs
ID
JobName
JobType (payroll, service, lunch, etc)
(etc)

Clients
ID
Name
(etc)

WorkUnits (item of work performed, call it whatever)
ID(Auto Number)
EmployeeID
JobID
Date
Clock in
Clock out
Description of Work
(etc)

I'm assuming that description of work varies for each time a given job is performed ("detailed description of the work he did"). As Skip says, you don't need a lunchstart and lunchend, those are just another job. You don't want the jobname in the workunits table, either, the jobid is what you need. You almost certainly don't need hours on task either. While putting a calculated field in a table is a fairly common denormalization technique, it should only be done when you know that it will increase application performance to do so and can back it up with benchmarks. Until then, you should calculate it ad hoc as Skip says.

Some rules of database architecture, simplified:

1. Store data as economically as possible. There is a reason that this is rule number 1! Because, well, it is. Now, you may need to store more data to enhance performance, but the best way to do this is to first store as little as possible and then make changes that you KNOW will increase performance. Keep in mind that the less data there are, the quicker you can get to what you want. (Except when that's not so...)
2. Break all of your stuff into one to many relationships. Add associative tables to resolve many to many relationships, such that each table in the latter is in one to many relationship with the former.
3. Make sure all of your tables have a primary key, and use foreign keys to define your one to many relationships.
4. When you design your database, be very careful not to confuse data storage and data output. In particular, don't assume that something that is in your data output needs to be stored in data storage, for example calculated fields in a report.

HTH

Bob