Database Structure For Application

TheBoffin

I am in the process of working with a MySQL database for resource bookings for a community center (volunteered again)

I have many resources (rooms) with set opening and closing times (i.e. all rooms are available from 9am to 5pm daily except Sunday

Bookings are broken down into hourly slots

I have 3 tables in the database, Resources, Clients, Bookings.

Resources contains the time, date, booking ID
Clients contains client information, client ID
Bookings basically marries up Resources and clients to form a booking

Any tips on how i should proceed with DB, is what i have the leanest way of achieving this.

Thanks
Very Sleepy Developer

cython

If I were doing that, I would probably move the date and time info associated with the booking to the bookings table, as that way you have largely unique information in each row of the resource/rooms table, and in the clients table. Under your approach it seems that for each booking, you would need to add a row in both the resources and booking tables, and potentially prepopulate the resources table for each slot to be booked, in turn linking them to the client in the booking. By moving the date/time as I mention, then all the "transactional" data is consolidated in one table, and the client/resources are largely static data

Deliverance XXV

As it is not a very complex application I keep it simple and would go with something like:

Room
id *
roomNum
desc

Client
id *
clientInfo/etc

Booking
id *
clientID (Foreign key)
roomID (Foreign key)
bookingInfo/times/dates/etc

Room and Client tables holds static information, and the Booking table holds all the booking records.