Golf Course Booking System

[Deeem]

Hi guys,

I am going to be creating a golf course booking system. I have a few questions firstly based on the database setup. If I give you the background behind the system to give you a better idea of the theory.

Background
They need a booking system whereby members and visitors can book tee off times based on the current availability. The tee off times will be based on 15 min increments and stored in the database along with the bookingID (primary, autoinc), playerID, bookingtime, bookingdate, no_of_players, etc. The methodology behind the database is the first thing that I need to get my head around, so a basic process scenario would be essential here. No cash transactions will be made during the process only booking times.

Preferred Methods/Questions
1. DATE: Booking Form displays a date picker whereby the player can choose a preferred date.
2. TIME (15 min increments): Once date is selected would it be easier to display all available times during that day or base it all around a search, whereby the player selects a preferred time?
3. Number of players: This isnt really essential, it is just a record to show the owners the number of players. Future bookings could be based around this number.

Conclusion
If someone could help me workout some database design or theory behind this scenario I would be very greatful as this is the first proper booking system I will be attempting.

Please excuse my droaning! Many thanks in advance

Damon

[buntine]

Will this system be storing the details of the customers (the players)? If so, you may have a many-to-many relationship considering each player can book several tee-off times and each tee-off can involve several players. This will cause loss of referential integrity and must be resolved with a bridging table.

So, your data model would be set out a bit like this:

Players
|
/\
PlayerBookings
\/
|
Bookings

This (horrible) diagram is depicting a resolved many-to-many relationship. The PlayerBookings table will have two foreign keys; one referencing a primary key in the Player table, another referencing a primary key in the Bookings table.

This is just the tip of the iceberg.

Regards.

[Deeem]

Yes, the PlayerID from the bookings table will be referenced from the playerID.

I guess it would be easier if they first initiated the playerID by registering? The PlayerID is available for both visitors and members. The members would be picked up by referencing a membership_no field in the players table.

The main query I have at the moment is how to layout the times and dates in the database. The procedure behind it, as I can imagne is quite resource hungry therefore needing an SQL table?

Thanks for the help so far!

Damon

[wmif]

i dont think this is out of access power. i personally wouldnt run access because i just dont like it, but it should handle it fine.

i see the issue that buntine brings up. although i think about it in a different way. if your client doesnt want to know about every single player thats in that tee-off time, then you can just use the playerid of the player that is reserving that time slot. this would eliminate that many to many problem.

now for the times, i would store the date just like normal for the day of the slot. then i think i would have a column for the hour, and a column for the 15 minute block. :00 = 1, :15 = 2, :30 = 3, :45 = 4.