Slot Reservation System

[fatmcgav]

Hi there,

I'm working on my A2 IT Access project atm, and my aim is to produce a fully functional db for use by a golf course, incorporating membership information and tee-time reservation system.

I've got the membership side pretty much sorted, so now I need to start work on the reservation system.
But where to start?

Basically, the user needs to be able to view slot availablity on a specific day, and make/change/delete bookings quickly and easily. The booking also needs to contain the party size. The bookings themselves should relate back to the specific member that made it via the MemberID field.
The user should be able to make bookings upto 6months in the future.
Block bookings should also be possible, so that the admin can block an entire day out for a tournament therefore preventing member bookings.
The first available and last available slots also need to be editable by the system admin to take into account time of year, light conditions etc.

Right, so i know there's quite a lot there, and i'm sure i'll come up with more features that would make it better.
But as it stands, that's something to get me started.

I'm not asking for someone to do the project, just to point me in the right direction as to suitable table structure, how to "make" the slots, how to store the bookings in the table, etc.

So, any input and ideas are greatly appreicated.

Cheers
Gavin W

 

[KenHigg]

Nail down the deal-breaker features before you start. Then you can have a list of pie-in-the-sky features to work on if you have time.

Just my opinion...

 

[fatmcgav]

Nail down the deal-breaker features before you start. Then you can have a list of pie-in-the-sky features to work on if you have time.

Just my opinion...

Ok, so i want it to be able to:

Booking facilities:

• View bookings by date - pop-up calendar facility already in db - see here
• Make bookings by double-clicking on a time slot - pop-up form to complete booking info(booking member number, party size)
• Be able to amend a booking by double clicking on an existing booking - same pop-up form in edit mode
• Be able to delete a booking by double clicking and selecting delete on the edit form
• Be able to block book times - eg All morning, all day, etc to cater for tournaments, events, etc

Admin Facilities:

• Change the first available and last available slot time depending on time of year, etc
• Clear booking for whole days/weeks quickly and easily
• Possible - View the most active times, members, etc to help increase bookings - MIS stylee.

Techincal Info:

• Slots should be available every 10mins from the first available slot time to the last available slot time.
• Slots be color-coded - red for taken, green for available, brown for block etc
• All bookings be related back to memberID, so it is possible to see the most active members, etc.

I think that should about cover the must-have features...

So, what'd ya say?


Cheers
Fatmcgav

 

[KenHigg]

I'm guessing you should be able to pull out your entities, their attributes, and the relationships from that and do a proper data model.

 

[fatmcgav]

I'm guessing you should be able to pull out your entities, their attributes, and the relationships from that and do a proper data model.

Ya wat???

*Runs off to google *
Any more info?

 

[fatmcgav]

Right, lets get on

Ok, so i've just started to work again on this.

I've started to model everything in Visio, so that i know wats going on etc.
Attached is a copy of the current Database Layout Diagram, showing everything that i feel is relevant to the Slot Reservation System - i've got the membership side modelled seperatly.
As you can see, tblMemberInfo has a one-to-many relationship with tblBookings on MemberID. The PK of Bookings is BookingID.

Is that the best possible layout for tblBookings? Or am i better off using something like week # & day number? So for Fri 11/11/05, it would be Wk 46, day 5 (assuming the wk started on Monday)???

The Slot System works by generating a slot number from the FirstSlot time in tblSlotTimes corresponding to the week number of the date.

eg: If the user wants to make a booking on the 11/11/05, the system will query tblSlotTimes for the first available slot time of week 46. If the value is 9:30, then the system then generates slot times based on the start time of 9:30 at 10 minute intervals. So if they wanted to book 10:10, then the slot number would be 5 - 9:30, 9:40, 9:50, 10:00, 10:10. And so on.

Know wat i'm not sure of is how to actually create the slot times? Can I create a module to call on the booking form opening, and whenever they change the date? How would i code it? I know i'd need to query tblSlotTimes, but how would i populate the slot times/numbers?

Also, wat do i do with "old" bookings, that is ones that actually have dates before today. Is it best to store them in another table?

There's a couple of other things i need to work on, but i can have a look at those after i've actually got the system working.

Right, i've prob included TOO much there, but hey, i thought i'd give u guys as much info as possible.

Any help is greatly appreicated.

Cheers
Fatmcgav

 

[fatmcgav]

Ok, i've got plenty of time to get on with this now, so anybody got any ideas?

Cheers
Fatmcgav

 

[fatmcgav]

Right, lets get going then

I've changed the table layout slightly, so that the bookings are made by date & time, rather than having to generate a slot number.
I've also started designing the input forms, and queries behind them.

I've got one query which will pull all the bookings for a specific date, selected on the top of the form. However, the query is duplicating all the results multiple times. There are 3 results in the table for the 15/11/2005, but the queries pulling up 156 records :S :S Any ideas what might be causing this to mess up?
THis also messes the booking form up.
The biggest problem i've got now is how to show empty slots aswell as booked ones.
This screenshot shows the access-form - basically wat i want to do is show empty times aswell as booked times, with the start and finish times being pulled from tblSlotTimes. Any ideas on how i'd go about doing this???

Any help would be greatly appreicated.

Cheers
Gavin

 

[fatmcgav]

Cmon ppl - one of u must be able to help.