I am trying to create a booking database...but the design I keep coming up with makes me a bit uneasy. I don't really like it (because of massive joins involved in determining what time slots are available) but I don't see any other way to do this. So this is my design.

--This table stores incidences of booked time slots
TABLE:Booking_Incidence
BookingID
BookingStartDate (DateTime)
BookingEndDate (DateTime)
FK_UserID

--This Table stores the generally available booking times for each week. Eg. If users can book from 9:00am to 5:00pm monday to friday then there would be an entry with
slotstarttime = 9:00, slotendtime=17:00,sun=false,mon=true,tue=true, wed=true, thur=true, fri=true, sat=false

TABLE:General_Avail_Booking
SlotStartTime (Time)
SlotEndTime (Time)
Sun (boolean)
Mon (boolean)
Tue (boolean)
Wed (boolean)
Thur (boolean)
Fri (boolean)
Sat (boolean)

--This table stores any possible exceptions that might occur outside of the general booking times (eg a holiday). bAvailable determines if the exception allows for a new booking, or cancels an existing one.

Table: Booking_Exceptions
SlotStartDate (DateTime)
SlotEndDate (DateTime)
bAvailable (boolean)


I use General_Avail_Booking and Booking_Exceptions to determine the possible times when users can actually book a time slot, and then store what time slots users book in Booking_Incidence. I'm sure there is a better way to create a booking system and I would really appreciate some help or some standard designs that others have made.

In a paper system a day is divided up into time units eg 1 hour. When a booking for a room is made the appropriate number of time units are reserved. There is no need for exceptions or start and end times. It is just like memory allocation if you like.