[thelist] Storing Schedules in a database - Best Practices???

[Luther, Ron]

Jay Turley asked about best practices for a work schedule table:

>>I'm not sure what the best way is to represent the scheduled shifts.


Hi Jay,

I don't do this stuff, but it is an interesting question and one where a little more information on what you are trying to do may have been helpful for at least two reasons.

* If you talk shifts and hours then its easy to see this as an after-the-fact payroll application with shift differentials where you may or may not need a shift table, depending on whether you already have that delta factored into the salary table.  Pull the time clock data like Rahaman suggested and you should be able to do most anything you want.

* Folks in different industries may view the term 'shift' differently.  In manufacturing someone who works 'second shift' may work 4pm to midnight, every day, five or six days a week.  I'm guessing your 'shifts' are more akin to restaurant or nursing occupations where someone may be 'scheduled' to work 8am to 5pm on Monday and then 3pm to 10pm on Wednesday.  [If it were an area like consulting and/or oil field services you may also need to add 'location' and 'client' fields to the table for account planning and billing purposes.]


If that is the situation, then I think I would probably start with a couple basic tables:

One to associate the worker to the supervisor:
Employee_name, Emp_ID, Supervisor_ID

One to hold the work schedule:
Emp_ID, Schedule_ID, Time_Period_ID, Time_Period_Name
{You'll be awanting different schedules for different months or weeks or shop-months or something, eh?}

One to associate a supervisor to a schedule:
Emp_ID (supervisor), Schedule_ID, [schedule_name]
{I'll get to that in a bit.}

One to define the schedule (multi-record):
Schedule_ID, Day_Of_Week, Start_Time, End_Time, etc.
{Because some supervisor may want to define 3pm to 11pm Monday, Wednesday, and Friday as a single shift that s/he assigns workers to.}

Maybe another table to define the Time Period - which you'll need to build those cool looking 'calendars' that need to print out for the workers, work groups, supervisors, managers, etc.


-- I'm thinking you'll need a screen or three to allow each supervisor to enter and define the 'standard' shifts they want to use for scheduling their people.  Need to be able to add new shifts, modify existing shifts.  Naturally they should only be able to modify/delete shifts that they created.  {With 'delete' naturally being a 'disabled' flag instead of a true delete so you don't hose up your historical data and reporting ... and so you don't ever end up reusing a schedule_ID.}

-- You'll need a few screens to allow the supervisor to schedule their people.  I imagine from a gui perspective that they would want to the entire planning calendar [week or month] at one time.  A side panel containing the names of their defined 'standard shifts' that they can click and add them to the calendar (and possibly color code).  And then let them click on the added shifts and add people to season to taste.  {Ooops, wrong app!} Naturally, they can only schedule people that work for them, by name rather than number.
- They might want a button that gives them an 'instant' summary of hours by worker for the planning period open on the screen so they can judge 'fairness'.  A really good sup may want to see a trend of hours over multiple periods for their workers as well.

-- You may as well plan now for the "copy the schedule from [period x]" capability that the sups will demand.

-- Visibility by worker to their own and possibly their workgroup schedule.

-- Summary reports for supervisors.  Various mgmt reports.

-- If your workplace allows it, you may also need to build a few screens to facilitate 'schedule trading' among workers (The "I'll work your Friday if you take my Saturday" thing.) ... with appropriate approvals.

-- May also need a 'bidding' screen for those lucrative OT and Holiday shifts.  ;-)


HTH,
RonL.

>>My biggest worry is getting the data for an entire month out in a
>>reasonable amount of time, given an organization with several hundred workers.

Naw ... Processing is quick ... It's getting the data input in the first place that takes time!  Good Luck!