Re: Design of a work time calender
Posted by: Rick James
Date: December 31, 2014 02:32PM

It seems to me that `interval` is essentially always 7, so not necessary to generalize on.
NULLable end date -- yes.
Etc -- folded into...

The `RegularHours` table would have the weekly assignments for each person -- only a few rows each. Only 1 or 2 rows for that Monday-only person. The "normal" person would have 10 rows -- morning + afternoon for 5 weekdays.

The `Person` table would have hire date and (initially-null) termination date. This would add (via your code) a restriction on top of what RegularHours says. Note that if a person starts or stops in the middle of the week, this works fine.

The `Vacations` and `Holidays` tables are list of exceptions.

To get the week's assignments, fetch all the data from RegularHours, then delete information based on the other three tables.

Something to note... If you change the data in RegularHours, you loose the ability to see what happened last month.
Plan A: Live with the deficiency.
Plan B: Have an start_date and end_date (null for current?) listing the effective date range for each row, then add new rows with non-overlapping date ranges when you adjust their schedules. The NULL is handy -- that says which row is needed for current computations.
Plan A is easy; Plan B needs a bunch of code.

Please understand, I am making up all this on the top of my head. I have not implemented such.
And, keep in mind that the database is merely a source of truth -- your code must derive information from it. (Hence my Plan A/B choice in RegularHours.)

> How should I get the data with the Select ?

What language are you using for your API? PHP, VB, Java, etc are likely candidates. Often the SELECT would be simply SELECT * FROM TABLE WHERE end_date IS NOT NULL. Then do the rest of the manipulating in your code. Or, if you are gathering data for one person, WHERE user_id = ?, adding on the NULL check if appropriate.

Only some simple queries can be done via SELECT but without user code to massage the exceptions, start/end_dates in the middle of the week, etc. (Yeah UNION may work, but it makes my brain hurt.)

Options: ReplyQuote


Subject
Written By
Posted
December 29, 2014 03:46AM
December 30, 2014 08:29AM
Re: Design of a work time calender
December 31, 2014 02:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.