Structure Review

[lespaul]

I would like suggestions/feedback on how you would handle this kind of data situation.

I have a jury management program that calculates the pay a juror is due. However, people who are public employees do not get paid for jury service because the public employers are required to allow employees to serve and must pay them, so the court does not. But if you are a public employee serving jury duty and you are at the court outside of your normal working hours then you DO get paid.

I'll be our example. I work from 7 am - 4 pm everyday. If I get called for jury service and I'm there:

Day 1: 3:30 pm - 5:30 pm (pay from 4:00 pm - 5:30 pm)
Day 2: 9:00 am - 11:00 am
Day 3: 2:00 pm - 7:00 pm (pay from 4:00 pm - 7:00 pm)

I would get a check for 4.5 hours that I worked on my own time.

Now this works well for those that work M-F 8 hour days. I run into real problems with firefighters and other emergency personnel. Let's say the fireman works two days on and two days off. Or someone who works from 8:00 pm - 5:00 am. or a weekend shift Saturday - Thursday. If they get called in on a Friday, they should get paid for all hours.

How would you structure the data?

I've thought of something like:

DaysOfWork
MoTuWeThFr

Or maybe have the day of week and indicate if they work:

Mon   Tue   Wed   Thur   Fri   Sat  Sun
Y     Y     Y     Y      N     N     Y

Any other ideas/suggestions?

Thanks!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Thargy]

lespaul,

A nice predicament indeed. Herewith my three hap'orth:-

I presume that you have a PUBLIC_EMPLOYEES table containing all the various categories, such as fire fighter, policeman, ambulance driver etc. Your data should obviously relate to this in some way, so that the database can answer the questions "How much did we pay public employees for jury service out of normal hours this month?" and "How much did we pay fire fighters on jury service last quarter?"

The database obviously can't keep track of every possible combination of shift, day on/off rotas that such folk work, so don't try. I suggest that you have a NORMAL_DAY_OFF (NDO) boolean flag field, which indicates whether or not the person would normally have been at work on that day, regardless of the day of the week. Thus a Sunday would not be paid to a fire fighter, unless the flag was set, as he was due on shift anyway. If he'd worked Friday and Saturday and was due to be resting Sunday and Monday, if these days had their NORMAL_DAY_OFF flag set true, payment should be made.

I further suggest that you have an integer field storing the amount of normal time off worked on that NDO, to your finest calculating granularity. Ideally, the integer number of minutes worked, as this allows you to do modulo 15, 30 or 60 arithmetic to calculate payments to the nearest quarter, half and whole hour respectively, depending on how your payroll system works. By calculating to the minute, you can deal with persons whose shifts end part way through a day.

Although you could store the integer number of seconds worked, this seems like overkill to me.

Alternatively, have two date/time fields called START_NORMAL_TIME_OFF and END_NORMAL_TIME_OFF. Simple date arithmetic should give you all the info you need to make payments. In fact, that seems better than my first idea, as you can have multiple entries for an individual, if for example the trial spans a long period of time, and they're due payments on numerous occasions.

Regards

Tharg



Grinding away at things Oracular

 

[danvlas]

It might be of interest how and when the data is entered into the database.
I suppose at the end of a jury session, and my suggestion is based on this assumption.
It could make sense to suppose that the juror can give the information about his working hours BEFORE entering the session.

I presume you have a table tblJurors with an attribute 'PublicEmployee' in it. PK would probably be jurJurorID


tblSessions:
- sesID (PK)
- sesTrialID (FK to the trials table)
- sesSessionStartDate
- sesSessionEndDate


You may create an 'Exceptions' table which would store just the exceptions, which should be documented by the juror (as he is the beneficiary of the payment).

tblJuryAttendanceExceptions:
- jaeID (PK)
- jaeSessionID (FK to Sessions)
- jaeJurorID (FK to Jurors)
- jaeWorksFrom
- jaeWorksTo

It becomes a problem of comparing periods (SessionStart-SessionEnd to WorksFrom-WorksTo) in a Left Join.

If SessionEnd<WorksFrom then pay for the entire session
If SessionStart>WorksTo then pay for the entire session
If SessionStart<WorksFrom then pay for the difference
If SessionEnd>WorksTo then pay for the difference

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[lespaul]

Thanks for the suggestions. Dan here's a more detailed view of the table information:
[tt]
JMPMAIN
JurNum (PK)
Name
Address
etc.
PubEmp (T/F)

JMPPEMPLE (stores public employee info)
JurNum (FK)
Employer
RegTimeIn
RegTimeOut
PartTime (T/F) }
Substitute (T/F) }
Other (T/F) } If any of these are true the Public Employee is paid for ALL hours

JMPHOURS
JurNum (FK)
ServDate
TimeType (indicates Orientation, Daily Call, Juror - not important to this)
TimeIn
TimeOut
[/tt]

I've been pulled onto other things and haven't had a chance to get back to this, but I appreciate the suggestions and hopefully between all of us I will be able to come up with a solution!

Leslie

 

[danvlas]

In my opinion, JMPPEMPLE handles the regular situation, not the exceptions you described in your first post.
Since a model cannot be built for all exceptions that may occur, an Exceptions table looks necessary in any case.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html