Re: Design of a work time calender
Posted by: Rick James
Date: December 29, 2014 06:39PM

Do not have repeated columns (mon_start, tues_start, etc). For "work history", have a table with these columns just 4 columns, but multiple rows:
emp_id
date
start_time
end_time

You can discover "Monday" from the `date`; don't store it.

A similar table could could list "assigned work times":
emp_id
day_of_week -- 0-6 or 1-7 or whatever you like
start_time
end_time
There might be about 10 rows per full-time employee.

As for holidays, you might create another table (say, Holidays), that has an array of dates. Each year you add a dozen or so extra rows to the table. It might need a second column to indicate, for example, that the Friday before Easter is a "half day holiday".

If you are saying that some employees work on holidays and some do not, then it gets much messier. Perhaps another table with just those who will be working on holidays:
emp_id
date -- the holiday date
start_time
end_time

If you have graveyard shifts (start one evening, end the next morning), then `start_time` and `end_time` becomes messy because they are not ordered. In that case:
Plan A: start_time and end_time should be stored as minutes after midnight of the `date`, hence could be more than 24*60. (SMALLINT UNSIGNED)
Plan B: Replace date + start_time + end_time with start + end, where they are DATETIME instead of splitting DATE and TIME.

Plan A probably works better, since it could be done for the work assignments table since the is no `date`, only a day_of_week.

Vacations?? Another table perhaps with
emp_id
start_date
end_date
One row per employee per vacation.

Sick time? Emergency leave (death in the family)?

Well, where is this all headed? Who reads the data? Are you printing out a list of who's on duty for the next week? Or what? Even when developing the CREATE TABLE, it is wise to think about the SELECT statements that you will ultimately need.

Regardless of how it is done, you will need code to put the data together. Remember that a database should contain just the raw data; the code (PHP, VB, Java, etc) using the database is where you add the 'business logic'. It is likely to be folly to try to code it entirely in SQL.

Options: ReplyQuote


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


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.