Billiam:
When it comes to non-worked time which is credited to an employee, i.e. for
which they are paid, such as annual, public or concessionary holidays, sick
time, compassionate leave etc, there are I think basically two options.
Firstly they can all be treated as one entity type, with the 'category' as an
attribute of the entity type. In this case the time would be entered in a
single table, differentiating worked from non-worked times by the category.
The second is to regard them as different entity types, with a table for each
category. This makes the interface design easier as the times can be entered
as durations rather than start and end times, e.g. as the number of days.
However, I'm not keen on the latter approach as I think its really bending
the reality to suit the model rather than the model reflecting the reality.
My gut feeling is that we are really dealing with a single entity type and
the model should reflect this. It does mean, however, that for non-worked
time computations its necessary to assume standard start and end times per
day, or half day if pre and post lunch times are recorded as two rows rather
than a day as a single row with a subtraction for lunch. Wherever I've
worked we've always recorded time as separate pre and post lunch start and
end times.
If a single table is used then there needs to be some machinery for entering
periods of leave en bloc rather than having to enter each day separately.
This is not too difficult, however, as it can be done via a separate form to
the day-to-day data entry form for normal worked time. At its simplest the
user would enter either start and end dates, or a start date and number of
days, the category (annual leave, sick leave etc) and on confirmation via a
button some code would loop through each day in the range, inserting rows
into the table with the standard start and end times per day or half-day,
excluding the weekend and public/concessionary holiday days in the same.
These standard times should themselves be values in a table which can be
referenced in the SQL statements to insert the rows.
For reporting purposes it would then be a simple task to design a report
grouped by year (either calendar or accounting year), employee and time
category and restricted only to non-worked categories, which counts the
number of rows per category, dividing the result by two if two rows (pre and
post lunch) per day are used.
One caveat, however. The real world is not always neatly arranged. As an
example my own wife works part time, roughly half the standard working week
of her organisation. When she takes a public holiday, therefore, the time
with which she is credited is a pro rata portion of a standard day. As it
happens public holidays here are usually a Monday, which is a day on which
she normally works a full day, so she is benefiting from a full days absence
from work, but as she is only credited with the pro rata portion of a
standard day she then has to work an additional half-day during that week to
make up the difference. Consequently the system at her workplace has to take
account of this, crediting her with half a day's time for the public holiday,
and also with the additional half day she works in that week. The latter is
no problem of course as she logs in and out on that day, but if the system
applied a blanket full day's credit to everyone for the public holiday then
she'd be credited with too much time. In database terms one way of looking
at this is that the credited time for a public holiday is not functionally
dependent on the holiday per say (i.e. on the key of the holidays table), but
on that and the employee, i.e. on the key of a table which models the
relationship between employees and public holidays, in which there will have
to be columns for the standard public holiday times per employee. Or an
alternative approach would be to have a credited standard time per public
holiday, and for part-time employees like my wife, apply a factor to this on
the basis of her standard weekly hours as a proportion of those of full time
employees. I've no idea exactly how their system does it (not always
efficiently to judge by the number of times she has to get her times
corrected!), but however it is done, it does serve to illustrate the need to
take such variation in work patterns into account when designing timesheet
applications.
Ken Sheridan
Stafford, England
Hello Ken,
It's always an honour when you respond! Yes, I think your approach might
make more "sense" in that the error cannot occur at all your way...
Unfortunately, like today AND yesterday, I am working on a timesheet DB for
my non-profit organization, which I had hoped would be finished
tommorrow...gulp. Oh well, if they're that mad I guess they"ll just have to
find another sucker dumb as I am , and good luck with that, because I must be
dumb to be working on this glorious weather day, LOL! My real point being, I,
at least, work on weekends, so I would need to add that in.
Perhaps I could ask your opinion, Ken, as to handling large blocks of time
easily for the enduser when they are entering in vacation time or sicktime...
Currently, I have only given the enduser a DateWorked field, now going to be
a cbo based on the payperiod thanks to you (well, at least I hope it will)
and a cboOtherTime for vacation day, sick day, statuatory holiday which have
their own cboWorkCode to display them.
I suppose I may be allowed by the bookeeper to have one entry for vaction,
and the cboOtherTime with a values of 8 hours to 80 hours, however, I think
it would be nice to be able to have the enduser click on a report so they can
see how many days they have used during the year,when they used them, and I
suspect then that this would not be the way to do it. For that matter, I
suppose someone could be sick for quite a number of days as well...
Any advice on how to handle this would be sincerely appreciated!
Warm Regards, and thanks for taking the time out of your day to help me...I
hope someone passes that kindness on to you ten fold!
Best Regards,
Billiam
Another approach you might like to consider is to use a combo box as the
control bound to the DateWorked field. You can then restrict its list to
[quoted text clipped - 72 lines]
thankful for some help on this...
Billiam
