Job Report Database

[jepoysaipan]

Hi,

My new task is to create an application that stores Job Reports on a daily basis, what I have in mind is to create the following tables:

1. Employee
2. Client
3. Client category
4. Job Category
5. Master with the ff: fields:
a. EmpCode (linked from Employee)
b. Date
c. JobOrderNo
d. ClientName
e. ClientCategory (linked from Client Category)
f. ClientLocation
g. JobDescription
h. JobCategory (linked from Job Category)
i. TimeStarted
j. TimeEnded
k. LaborCharge (computes the time spent to do the work)

Now I am having second thoughts on designing the form, at first I tried to use the per record form (user inputs every record one at a time) but its redundant since the employee can have 30-40 records to input in a day.

What I want is for the Employee to input only the details of his work within a day in a datasheet view.

Your immediate insight is highly appreciated.

Thanks in advance,

 

[The_Doc_Man]

This cries out for normalization.

Top level - employee data

Next level - employee daily (or even hourly) data

Another probable split-out - client data

Still one more - job data

Why are they different? Because they have different keys. The rules of database design say that when the keys for certain data elements differ, they don't belong together.

The employee data depends only on the employee ID number - so that is a high-level table. You can't charge unless you are an employee. You need a number of some sort as the prime key (PK) for this table. Your call as to what it is.

The client data depends only on the client data. No charges are allowed unless there is a client to foot the bill. Needs a PK. Probably you keep Client Location here unless that is multiple sites for a single contract. In which case work location is a child of the client table.

The job data depends on the client (probably) - in that I would guess a client could have more than one job to do or more than one work order. Needs a PK of its own but also will contain a foreign key (FK) of the client ID.

The bottom level is the hourly time entry, which depends on employee and job number. (Doesn't directly depend on client number because you could find that through the job number indirectly.) Has FK for the employee and another FK for the job. It is also an example of what we call a JUNCTION table because it implements a many to many type of relationship.

The labor charge goes where it has a dependency. From the data you gave us, I can't tell whether the charge depends on the employee, the client, or the job. But odds are that it does NOT go with the raw labor records. I'm thinking that if Job Category has any commonality across multiple jobs, then the labor charge might well go there. In which case the hourly charge table ALSO has an FK to the job categories table.

You will need a form with lookup for employee number and job number and maybe for job category, but then you can input the start and end time directly.