Review my logical model for gaps

Question

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.

Answer 1

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)