Unanswered: Booking system that includes projected and actual headcount figures
I am new to creating a database system and have been asked to carry out a pretty big project. I am not too sure where i should start with this.
I am looking at creating a booking system that allows you to book a floor in a building by date and capacity number. It then needs to filter information and display all the floors available on that particular date that will accomodate for the capacity number i have entered, then have the ability to book that zone and update the zone availbility e.g.
Claudia wants to find a floor which will accomodate 10 people, how will it eliminate floor B and give the option to select A or C.
floor A capacity = 15
floor B capacity = 9
floor C capacity = 12
This is very similiar to the booking system at a cinema as the movie names, screens and dates change all the time. I just dont know where to start with this. my EARD is getting bigger and bigger and I think i am over complicating it.
You can accomplish that with two tables:
floors -> floor_id, capacity
floor_bookings -> booking_id, floor_id, start_date, end_date
Availibility can be determined with a query:
start_date BETWEEN '2013-04-01' AND '2013-04-13'
end_date BETWEEN '2013-04-01' AND '2013-04-13'
(start_date < '2013-04-01' AND datum_eind > '2013-04-13')
b.floor_id IS NULL
f.capacity >= 10