General scheduling application

Diagram

Schema notes

groupings

• a group of schedules that can be displayed together

name

groupings_schedules

grouping_id

schedule_id

schedules

• for grouping related shifts

type

• either limited list of volunteers can take shifts, OR all volunteers can take shifts

name

schedules_volunteers

• if the schedule type indicates that a limited list of volunteers can takes shifts, then a record linking the volunteer to the schedule needs to appear here

schedule_id

volunteers_id

volunteers

• volunteer specific information could go here -- i.e. first volunteer date, status, reliability index, etc.

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

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

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

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)

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