Best Design for a booking system

[joelwenzel]

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.

 

[ceh4702]

Hmm an event can have several sites booked by several parties on multiple dates and times.

A contract should cover the number of people, seating required, resources required, Setup time, event time and Cleaning time. We also include security for the event for which there is a charge.

Well you could have some kind of table that just shows avail booking that has fields like

Venue_Usage_Table
year
month
day
5.min.time.slots.1-288 boolean

That way you can search by month by day and by year. Basically you would build 365 records with no time slots filled. Then use your events to mark the time slots filled and every time you have every 5 min interval marked for free or not. Does not have to be 5 min intervals. You could use 15 min or 10 min intervals or half or full hour intervals. You could use the date instead of month day year or use it as an alternete index.

Every time you book an event you mark your table to show occupied or when deleting events change their boolean status on the table. You might want some way to rebuild it in case of a problem. The think is you really need every day of the year built before you can store the results from a booking.





If you do not like my post feel free to point out your opinion or my errors.

 

[joelwenzel]

I like your post. It is a good example. I was hoping I could avoid mapping out everyday of the year however Anyway, my system has a couple considerations that makes it hard to adapt to your system. First, I think your sysem requires all the time slots to be the same length right (eg all 5min, or all 10 min, etc). I need a way to have variable timeslots. Also, I was really really hoping not to map out every day of the year (cause this means that I have to constantly be adding new booking times to my table as time progresses).

One thing I noticed was that in your Venue_Usage_Table, you broke out the date into 3 columns, year, month, and day. I've seen this before but I've never really understood why it is necessary. If you just had a single date field, you could still search by day, by month, and/or by year using month(date), day(date), year(date). Why do people break up the date field?

 

[alvechurchdata]

Why do people break up the date field?

Depends on the language. Some (like FoxPro) have good commands that can take a date field to bits and give you the day, the month, the year, the name of the day or the name of the month or the week number or the Julian Day or whatever you want. Others don't and it's easier to store the day, month, and year as three separate items.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ceh4702]

On the date if you want to look at only wednesdays or only at august how would you do that without using a function and taking a longer time to run the querry? Also you may want to look at only one year or for three months. I know in out database we use the date as a number of days since some arbitrary date and use a conversion string when we display it.

Anyway on this subject we also use a room scheduling/event software called R25 or Resource 25. We use it with Datatel Colleague Education software, I dont know if it works with other products or not. We are still working on implementing it but it uses formulas to write contracts, an d schedules rooms etc.



If you do not like my post feel free to point out your opinion or my errors.

 

[joelwenzel]

Ok, I realized a major problem with my design. I was thinking of it as one system (for booking) but what it really is is two systems...one for storing bookings, and one for showing a schedule of available time slots. It is the schedule part of this problem that I am having trouble with. Does anyone have a suggestion for this?

 

[joelwenzel]

ok, I've come up with a design for showing the schedule. This will work in combination with a simple booking system. What do you think? I realize that my syntax is not proper. Also, I know it is bad to select everything. I was just trying to get the idea out there.


-stores the regular weekly schedule
Reg_Schedule
FK_RoomID
FK_TimeSlotID
EffectieStartDate (the start date for using this regular schedule)
EffectiveEndDate (the end date for using this regular schedule)
SchedDay INT (1 to 7...1 for sun, 2 for mon, etc)

-stores exceptions in the regular schedule (eg new booking times, etc)
sched_excep
ExceptionID
FK_RoomID
FK_NewTimeSlotID
StartDate
EndDate

-this table stores the time slot IDs that a particular exception replaces
exception_replacements
FK_ExceptionID
FK_TimeSlotID

Now, to get the schedule for a particular week, I need a stored procedure. This is the basic idea. I'm not sure about using the line ws.FK_TimeSlotID IN (SELECT FK_TimeSlotID FROM exception_replacements WHERE FK_ExceptionID = s.ExceptionID). I think this might be slow and bad programming although I don't really know.

SP_GetWeeklySchedule(startDate DATE, mRoomId INT)

DECLARE Day1 to Day7 as dates starting from the startdate

SELECT ws.*,s.* FROM
(SELECT r.*,w.SchedDate FROM RegSchedule r
INNER JOIN
(SELECT Day1 as SchedDate UNION SELECT Day2 UNION ... UNION SELECT Day7) as w
On DAYOFWEEK(w.SchedDate) = r.SchedDay
WHERE r.FK_RoomID = mRoomID AND
w.SchedDate>=r.EffectveStartDate AND
w.SchedDate<r.EffectiveEndDate
)
AS ws

FULL OUTER JOIN

sched_exceptions s ON
ws.SchedDay >=s.StartDate AND
ws.SchedDay <=s.EndDate AND
ws.FK_TimeSlotID IN (SELECT FK_TimeSlotID FROM exception_replacements WHERE FK_ExceptionID = s.ExceptionID)
WHERE
s.FK_RoomID = mRoomID AND
s.StartDate >= Day1 AND
s.EndDate <= Day7

 

[MajP]

If your still interested, I just built something very similar. It is a booking system for 25 different rooms, with varying time periods. You can click on a Calendar day view (kind of like outlook) and add or modify reservations. The strategy I use to determine availability is eloquent, but simple. Very few joins. They do not allow users to post email addresses, but I can FTP it if you are interested or have another method.