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