Table design

[Ernst Guckel]

Hey,

I have a payroll database in design. I have a few tables setup already
but am having a bit of trouble determining the design of the main 'Payroll'
table.

I have an employee table and a payroll table. The employee table contains
all employee data, IE: Payrate File# DOB , etc. The payroll table contains
each payroll run's data... IE: Payroll date, hours, etc. The delema is in
the payroll table. There are multiple pay types to be paid. Regular,
Overtime, Vacation, Sick, etc. There are two possibilities that I can come
up with.

1. Have the payroll table contain all of these fields in each record. But
each record will have empty data fields because each employee does not
recieve ALL pay typers each week...

2. To put a PayTypeID field and an Hours field that contains the associated
paytype in each record. More records but less empty fields... The downside
to this one is I'm not quite sure how to make data entry on forms seemless
with this approach.

eother way... I would prefer to setup the database correctly... Anyone have
advice on which way to go?

Ernst.

 

[Allen Browne]

One approach would be to have a main table to store the fact that a payment
was made to an employee on a date, and a related table for each line item in
the payment. The 'overtime' would be a separate line item.

tblPay
PayID AutoNumber
PayDate Date/Time (date of the payment)
EmployeeID foreign key to tblEmployee.EmployeeID

tblPayDetail:
PayID foreign key to tblPay.PayID
PayTypeID lookup values 'Regular', 'Overtime', ...
Hours number (Double)
PayRate Currency (dollars per hour)
You may also need an EndDate or date range in tblPayDetail, e.g. if you need
to pay ahead for 'Vacation' pay.

Then it is a matter of programmatically looping through all the timesheet
records that have not already been paid, up to the PayDate, creating records
in these 2 tables, and flagging each timesheet entry as paid. This process
would typically need a batch number, so you can undo the last batch, fix the
timesheets, and have another shot at it if necessary.

Your interface should lock the timesheet entries that have already been
'paid' so they can't be modified. If a correction is needed after the pays
have been administered, it should be by way of a reversal in the next pay
rather than allowing edits of the timesheets. (There will be some extra code
to get this right, e.g. if the timesheets recorded that the guy worked 42
hour week and so you paid him 40 hours regular + 2 hours overtime, and then
it was discovered that a timesheet was duplicated and he worked only 37
hours, you need 2 reversal entries: one removing 2 hours overtime, and
another removing 3 hours regular.)

Hope that at least takes you in a useful direction.

 

[Ernst Guckel]

Thanks for the info. This approach is very helpful but I am concerned about
the data entry aspect of each weeks payroll... Unfortunatley I need to create
a spreadsheet like data entry form with employees down the left and ALL
available hour types going along the top... Just not sure how to set this up
being that the table design is setup with a paytype as a seperate record
line...

Ernst.

 

[Allen Browne]

I don't think I would approach the table design like that.

"All available hour types across the top" means what? That it is a graphical
layout where if the person works from 9am to 5pm, you draw a line from 9am
to 5pm? Somehow I can't imagine that kind of structure coping adequately
with all the possible scenarios that could arise, e.g. where someone has to
leave at 3:25pm for some emergency, and then returns at 4:40 to resume the
last part of the day, or where someone works past midnight into the next
day.

Even if it is possible to create an interface that works as you describe,
I'm not sure that should dictate the data structure of the tables.

 

[Ernst Guckel]

No. Not like that... Each week we punch in TOTAL hours in the appropriate
catagories:

Name PayRate Hours OT Vacation Sick

John 8.00 40 2.5 0.00 0.00

This is the data entry aspect of what we are looking for. I understand that
the table design is first. I know how it is supposed to be done but should
the Customer's request for form interaction play some part as well? I just
don't see how I can make the data entry form work with your recomended table
design. Maybe I am just missing something...

I see your design recomendation as the right way to go jus tstuck on how to
implement such a design in the user interface aspect of the project.

Ernst.

 

[Ernst Guckel]

Come to find out 'Batch Payroll' Is what I am trying to do. Didn't know thee
was a term for it

Ernst.

 

[Craig Alexander Morrison]

Ernst the solution, well one solution that I have used is to create a table
or tables to accept the Data Entry in a simple format.

Then following the completion of the data entry the screen has a update
button or you tie it into the close function. This function would process
the data in the temporary table(s) via a series of queries to turn the
flat-wide data in the entry table(s) to the correctly structured deep-thin
data in the database along the lines already discussed.

You would need to ensure that the data entry screen had the necessary rules
in the AfterUpdate of fields to ensure that correct data for that field is
entered to avoid/reduce issues arising when the data is absorbed into the
relational database. These temporary tables maybe best implemented as a
separate MDB on the network to keep them from being confused as part of the
central relational database.

This approach is not a trivial undertaking but it can be a very effective
solution.

 

[Allen Browne]

Does Craig's suggestion help?

That approach makes sense if you are forced to go that way.