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 volunteers, or ANY volunteer

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_volunteers

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

schedule_id

volunteers_id

volunteers ???

• volunteer specific information could go here -- i.e. first volunteer date, status, reliability index, etc.
• if there's no specific volunteer 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

virtual_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 actual shifts, default = 1

job_id

volunteer_id

• null allowed, this volunteer will be assigned to slot #1 when actual shifts are generated

schedule_id

actual_shifts

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

virtual_shift_id

job_id

filled_shifts

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

starts_at

• start date and time of shift on shift date

ends_at

• end date and time of shift on shift date

volunteer_id

attendance

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

filled_shift_id

volunteer_id

status

• NO CALL NO SHOW, CANCELED, TARDY, and ATTENDED 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_volunteers

schedule = "Adoption Teachers" volunteer = "Joe"

schedule = "Adoption Teachers" volunteer = "Mary"

schedule = "Prebuild Interns" volunteer = "Fred"

schedule = "Prebuild Interns" volunteer = "Poindexter"

schedule = "Front Desk Interns" volunteer = "Flip"

schedule = "Front Desk Interns" volunteer = "Bozo"

volunteers

name = "Joe"

name = "Mary"

name = "Fred"

name = "Poindexter"

name = "Flip"

name = "Bozo"

virtual_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"

volunteer_id = NULL

schedule => "Prebuild"

actual_shifts

SAMPLE RECORD B

virtual_shift => SAMPLE RECORD A

job => System Evaluation

filled_shifts

SAMPLE RECORD C

actual_shift => SAMPLE RECORD B

starts_at = 11:30

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

volunteer => Doogie (Doogie doesn't have a schedules_volunteers record, but does exist in the volunteers table)

attendance

filled_shift => SAMPLE RECORD C

volunteer => Doogie

status = TARDY

Functionality

Basic add, delete, update, list for each table

Generate actual shifts

select begin and end date

loop through dates, checking for day of week

find virtual shifts that fit

for X = 1 to slot_count, generate an actual 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 actual shifts

copy actual shifts

edit actual shifts

take attendance

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

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