Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Question 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.

    Thanks guys

  2. #2
    Join Date
    Mar 2013
    Location
    Breda, Netherlands
    Posts
    15
    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:
    Code:
    SELECT
        f.floor_id,
        f.capacity
    FROM
        floors f
    LEFT JOIN
        (SELECT
            floor_id
        FROM 
           floor_bookings
        WHERE
            start_date BETWEEN '2013-04-01' AND '2013-04-13'
            OR
            end_date BETWEEN '2013-04-01' AND '2013-04-13'
            OR
            (start_date < '2013-04-01' AND datum_eind > '2013-04-13')
        ) b
    WHERE
        b.floor_id IS NULL
        AND
       f.capacity >= 10

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •