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)