We need to centralize our ability to manage the various schedules that our banking centers keep since it is a manual nightmare right now. Do you see any obvious gaps in this?
A schedule is defined by a set of Days of the Week, open times and close times.
A logical record may look like this:
service_location service_location_type day_of_week open_time close_time
Main Street Branch Lobby Monday 8am 5pm
Main Street Branch Lobby Tuesday 8am 5pm
Main Street Branch Lobby Wednesday 8am 12pm
I want to be able to reuse Schedules but am worried something here is a a bit off.
Best Answer
Since your plan is to re-use schedules I would recommend a change in your thinking as follows:
a) Each service location can have one or more schedules - called location schedules (basically this is an association between a location, a predefined schedule and other data)
b) Each location schedule can have a priority (which allows you to create a custom schedule that overrides the default schedule)
c) Each location schedule has a start date and an end date when its active - which allows you to define a custom schedule which is active for only a specific period of time
d) A schedule has a name and description
e) A schedule has one or more schedule details - day of week, opening time, closing time
I have included an explanatory data model (MySQL)