General scheduling application

Diagram
digraph g {

node [shape="box", fontname="Helvetica", fontsize="11"] edge [fontname="Helvetica"]

groupings -> groupings_schedules [arrowtail = "crow", arrowhead="none"] groupings_schedules -> schedules [arrowhead = "crow"]

schedules_people -> people [arrowhead = "crow"] schedules_people -> schedules [arrowhead = "crow"]

schedules -> default_shifts [arrowhead = "crow"] schedules -> jobs [arrowhead = "crow"] schedules -> shifts [arrowhead = "crow"]

default_shifts -> shifts [arrowhead = "crow"]

jobs -> shifts [arrowhead = "crow"] jobs -> default_shifts [arrowhead = "crow"]

default_shifts -> default_assignments [arrowhead = "crow"] people -> default_assignments [arrowhead = "crow"]

shifts -> assignments [arrowhead = "crow"] people -> assignments [arrowhead = "crow"]

people -> attendance [arrowhead = "crow"] assignments -> attendance [arrowhead = "none"]

resources -> default_shifts_resources [arrowhead = "crow"] resources -> shifts_resources [arrowhead = "crow"]

default_shifts -> default_shifts_resources [arrowhead = "crow"] shifts -> shifts_resources [arrowhead = "crow"]

}

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


 * resources
 * usually the room where a class or meeting takes place, but also things needed for exclusive use of the shift (like the truck or a projector)
 * name


 * default_shifts_resources
 * resource_id
 * default_shift_id


 * shifts_resources
 * resource_id
 * shift_id

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