General scheduling application

Diagram

Schema notes

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

groupings

• a group of schedules that can be displayed together

name

groupings_schedules

grouping_id

schedule_id

jobs

• job to be performed during the shift

name

schedule_id

schedules

• for grouping related shifts

type

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

name

schedules_volunteers

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

schedule_id

volunteers_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

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

Sample data values

groupings

name = "Adoption Schedule"

name = "Volunteer Interns"

name = "Adoption Classes"

schedules

type = "discrete", name = "Adoption Teachers"

type = "all", name = "Adoption Class Students"

type = "discrete", name = "Prebuild Interns"

type = "discrete", 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"

actual_shifts

virtual_shift_id

job_id

filled_shifts

starts_at

ends_at

volunteer_id

jobs

name

schedule_id

schedules_volunteers

schedule_id

volunteers_id

virtual_shifts

effective_date

ineffective_date

day_of_week

start_time

end_time

slot_count

job_id

volunteer_id

schedule_id

volunteers

contact_id

name