Booking system - Date and time availability query

[robtyketto]

Greetings again,

Carrying on with my sports hall booking database Im looking to implement a method that checks when a coach is available to hire.

My table "Coach" looks as follows:

Coach
=====
Coach_Id
Coach_Name
Availabity_Monday
Availabity_Tuesday
Availabity_Wednesday
Availabity_Thursday
Availabity_Friday

The coaches will be assigned set days of work, i.e Coach 1 works Tues & Fris and Coach 2 works Mon & Thurs.

Also coaches are assigned specialised sports, which Ive implemented a Coach rates table to peform the lookup function.

Coach_rates
========
Coach_Id
Coach_price
Sport

I dont like the idea of having a field per day of the week.

The booking table contains fields
Date, Start_time and End_time.

Perhaps I could use a numeric value to represent day value and then workout day of the week in my booking form.

Another complication is that bookings are made in 30 min installments
and at the moment and this could change I havent catered for the coach to work anything other than a whole set day (as opposed to half days) and to work the same set day every single week. Maybe I could use a week field to allow flexability.

Ideally though I want to keep it simple and working, rather than complicated and not fully functional !!!

Any suggestions would be helpful, thanks again for those who helped on my previous query.

 

[VicRauch]

Rob,
First, remove the "buckets" of days (Monday, Tuesday, etc.) from the coach table. Go ahead a create another table for coach available.
Now I'm into suggestions. This is just ramblings as I thought of them. Hope this helps.
I would suggest the coach available table to be a table that describles a "typical" week, with a record for each day of the week. Sunday=1, Monday=2, etc. I would also think that having a routine that can take this "typical" week and create a detailed "Available" table with actual dates and the detail times. This detail "Available" table would only go out a couple of weeks, or months, depending how far in advance you will book time. Once a week, or once a day, old records would be removed from this "Available" table. Any exceptions to the "typical" week can be entered into the "Available" table. This tables could have it's own "detail" table with times listed. For the times table, I would suggest a table that defines each time slot during the day.
TimeID..........TimeSlot
....1...............8:00a - 8:30a
....2...............8:30a - 9:00a
etc.
Anywhere the system refers to a time, having this system will greatly simplify the time aspects. Your actual books table, can also be considered by the system as another UnavailableTime type of table.