General scheduling application

Diagram

Schema notes

groupings

• a group of schedules that can be displayed together

name

• self-explanatory

groupings_schedules

grouping_id

schedule_id

schedules

• for grouping related shifts

name

• self-explanatory

eligibility_type

• Who can fill shifts on this schedule? Either a LIMITED list of people, or ANY person

shift_type

• Can the shift be split up into chunks? Either RIGID (one person per shift), or SPLITTABLE (multiple people with non-overlapping, consecutive times can fill this shift)

schedules_people

• if the schedule eligibility type indicates that a limited list of people can takes shifts, then a record linking the person to the schedule needs to be here in order for the person to be appear in the selection widget

schedule_id

person_id

people ???

• person specific information could go here -- i.e. first person date, status, reliability index, etc.
• if there's no specific person info to track, we could just use the contacts table from the main database.

contact_id

name

• until hooked up to main app when name will come from contacts table

jobs

• job to be performed during the shift

name

• self-explanatory

schedule_id

default_shifts

• representing a shift or shifts on an "ideal" schedule with no actual date yet attached

effective_date

• first date this shift can appear on an actual schedule

ineffective_date

• last date this shift can appear on an actual schedule

day_of_week

• day of week, 0-6 = sun-sat

start_time

end_time

slot_count

• 1-N for use in producing multiple shifts, default = 1

job_id

schedule_id

default_assignments

• list of people who will be assigned to shifts when they are generated

starts_at

• start date and time of shift on shift date

ends_at

• end date and time of shift on shift date

default_shift_id

person_id

shifts

• representing a single shift on an actual schedule with a defined date

default_shift_id

job_id

assignments

• representing the filled part of an shift -- that is, someone has signed up for that shift

starts_at

• start date and time of shift on shift date

ends_at

• end date and time of shift on shift date

shift_id

person_id

attendance

• for tracking who actually shows up for shifts and who does not, etc.

assignment_id

person_id

status

• NO CALL NO SHOW, CANCELED, TARDY, and ARRIVED would be useful statuses

Sample data values

groupings

name = "Adoption View"

name = "Prebuild View"

name = "Volunteer Interns"

name = "Adoption Classes"

schedules

type = "limited", name = "Adoption Teachers"

type = "all", name = "Adoption Class Students"

type = "limited", name = "Prebuild Interns"

type = "limited", name = "Front Desk Interns"

type = "all", name = "Prebuild"

groupings_schedules

grouping = "Adoption View", schedule = "Adoption Teachers"

grouping = "Adoption View", schedule = "Adoption Class Students"

grouping = "Prebuild View", schedule = "Prebuild Interns"

grouping = "Prebuild View", schedule = "Prebuild"

grouping = "Volunteer Interns", schedule = "Adoption Teachers"

grouping = "Volunteer Interns", schedule = "Prebuild Interns"

grouping = "Volunteer Interns", schedule = "Front Desk Interns"

schedules_people

schedule = "Adoption Teachers" person = "Joe"

schedule = "Adoption Teachers" person = "Mary"

schedule = "Prebuild Interns" person = "Fred"

schedule = "Prebuild Interns" person = "Poindexter"

schedule = "Front Desk Interns" person = "Flip"

schedule = "Front Desk Interns" person = "Bozo"

people

name = "Joe"

name = "Mary"

name = "Fred"

name = "Poindexter"

name = "Flip"

name = "Bozo"

default_shifts

SAMPLE RECORD A

effective_date = 2009-08-01

ineffective_date = NULL (never)

day_of_week = 2 (Tuesday)

start_time = 11:30

end_time = 15:30

slot_count = 7

job => "System Evaluation"

person_id = NULL

schedule => "Prebuild"

default_assignments

starts_at

ends_at

default_shift => SAMPLE RECORD A

person => "Pointdexter"

shifts

SAMPLE RECORD B

default_shift => SAMPLE RECORD A

job => System Evaluation

SAMPLE RECORD C

default_shift => SAMPLE RECORD A

job => System Evaluation

person => "Pointdexter"

assignments

SAMPLE RECORD D

shift => SAMPLE RECORD B

starts_at = 11:30

ends_at = 15:00 (person needs to leave early)

person => Doogie (Doogie doesn't have a schedules_people record, but does exist in the people table)

SAMPLE RECORD E

shift => SAMPLE RECORD C

starts_at = 12:00 (person arrives late)

ends_at = 15:30

person => Pointdexter (this was assigned when the shift was generated, because Pointdexter has a matching record in default_assignments table)

attendance

assignment => SAMPLE RECORD D

person => Doogie

status = TARDY

assignment => SAMPLE RECORD D

person => Poindexter

status = ARRIVED

Functionality

Basic add, delete, update, list for each table

Generate shifts

select begin and end date

loop through dates, checking for day of week

find default shifts that fit

for X = 1 to slot_count, generate an shift

display schedules

display schedule groupings

select from list of defined schedules or groupings of schedules

select start and end dates

find all shifts that match

display in a manner similar to current schedule, with links for filling shifts, editing shifts, etc.

fill shifts

copy shifts

edit shifts

take attendance

when person checks in at front desk, staff person can set a status by selecting an option for that assignment

this inserts a record into the attendance table and sets the status