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

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"

groupings_schedules

grouping = "Adoption Schedule", schedule = "Adoption Teachers"

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

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

effective_date

ineffective_date

day_of_week

start_time

end_time

slot_count

job_id

volunteer_id

schedule_id

actual_shifts

virtual_shift_id

job_id

filled_shifts

starts_at

ends_at

volunteer_id