Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Service Location Schedule - Logical Model

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.

share|improve this question
    
To those who wondered: the image can bee seen at normal size as well. –  dezso Jun 22 '12 at 19:47
add comment

1 Answer 1

up vote 1 down vote accepted

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)

Solution Data Model

share|improve this answer
1  
I like that much better. Much cleaner and will support use cases with less fuss. –  Bob Probst Jul 2 '12 at 1:31
    
You are welcome –  ssmusoke Jul 2 '12 at 4:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.