Database Design for Scheduling

Question

I need to create a scheduling program for various locations at my work. I need to schedule fifteen minute time slots from 8am-5pm for each specific location. I'm trying to wrap my head around the database design needed.

Some parameters:

1. The schedule needs to go at least two weeks out.
2. Each location will have a unique schedule compared to the other locations.
3. The schedule must be in 15 minute blocks.
4. Each location will have different criteria for when a block is full. For example, one location could service 3 customers every fifteen minutes so their blocks would be in threes. Another location could service 5 customers every fifteen minutes so their blocks would be full after 5 people scheduled.

Every time i sketch this out I'm violating some rule of database normalization. The main goal is to be able to query a specific location for open "slots" and display them. Anyone know how I should build my tables so the query I just described will not have to work harder than necessary?

Answer 1

You will need a settings table for each location that contains information such as how many customers can be booked every 15 minutes, open and close times. You would essentially create a table of entries for each 'slot' that has a start and end time.

The rest of your parameters will have to be handled on the application layer, such as counting the amount of events in other locations and seeing if they are full.

event
-----
id
date_start
date_end
location_id

location
--------
id
name
max_customers
start_time
end_time

I would recommend that you read the Mozilla Calendar SQL Schema. It gives a good foundation for forming a solid scheduling calendar.