Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Structure Review

Structure Review

Structure Review

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:



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?  



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

RE: Structure Review


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.



Grinding away at things Oracular

RE: Structure Review

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

- 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).

- 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


Daniel Vlas
Systems Consultant


RE: Structure Review

Thanks for the suggestions.  Dan here's a more detailed view of the table information:

JurNum (PK)
PubEmp  (T/F)

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

JurNum (FK)
TimeType (indicates Orientation, Daily Call, Juror - not important to this)

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!


RE: Structure Review

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.


Daniel Vlas
Systems Consultant


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close