Reservation system - Available tables

Question

I've been working on this for a few days and I can't figure it out. Ill hope you can help me out.

I'm working on a reservation system, I would like to check if there are table's availbe on DATE and TIME. This is how my database looks like.

I would like to put the available tables into a select dropdown menu, so on the website it only shows available tables.

If I use the following query I can get the reserverd tables

SELECT res.reserveringsnr, datum, begintijd, eindtijd, tafr.tafelnr FROM reserveringen AS res
INNER JOIN tafels_regel AS tafr
ON res.reserveringsnr = tafr.reserveringsnr
WHERE (res.datum = "2013/12/18" AND (res.begintijd BETWEEN "12:00:00" and "14:00:00"))

So that query put's out the reserverd table's, but here's where I'm stuck. I have no idea how to check if a certain table is reserverd on DATE and TIME.

(NOTE: The date and time will be filled in by an variable, but for the example I used just text)

Edit: Working on @sebt his solution

First of all, to answer your questions

1. Reservations are always on the same day
2. A reservation has a fixed time, 2 hours always.

I've edited sebt's his query to my values:

    SELECT t.* FROM
    tafels t
LEFT JOIN
    (SELECT tafelnr FROM
          tafels_regel tf
    INNER JOIN
          reserveringen res
    ON tf.reserveringsnr=res.reserveringsnr
    WHERE
    res.datum="2012/01/12" AND
    res.begintijd<"19:00:00" AND
    res.eindtijd>"21:00:00" 
    )
ReservationsAtThisTime
ON t.tafelnr=ReservationsAtThisTime.tafelnr
WHERE ReservationsAtThisTime.tafelnr IS NULL

This query output's the following result

As you can see, the query results shows all tafels (12).

I've also exported the tables reserveringen, tafels_regel and tafels to an .csv (see edited .csv below)

EDIT 2:

Well I've edited my query again, it works for a part. This is the query (Screenshot)

    SELECT t.* FROM
    tafels t
LEFT JOIN
    (SELECT tafelnr FROM
          tafels_regel tf
    INNER JOIN
          reserveringen res
    ON tf.reserveringsnr=res.reserveringsnr
    WHERE
    res.datum="2013/01/12" AND
    res.begintijd <= "19:00:00" AND
    res.eindtijd >= "21:00:00" 
    )
ReservationsAtThisTime
ON t.tafelnr=ReservationsAtThisTime.tafelnr
WHERE ReservationsAtThisTime.tafelnr IS NULL

There are 2 reservations on 2013/01/12. (Download edited .csv)

Reservation 1: from 19:00 to 21:00 on tafel 1

Reservation 2: from 20:00 to 22:00 on tafel 11 and tafel 12

When I run the query above, the only tafel not showing is tafel 1, since tafel 1 is reserverd from 19:00 till 21:00. But however, from 20:00 till 22:00 tafel 11 and 12 are also reserverd.

As you can see in the query results, only tafel 1 is reserverd. But when I would make another reservervation from 19:00 till 21:00 and I'm checking for available tafels, then tafel 11 and tafel 12 still shows.

The tafel will be free at 19:00, that's right, but since we always sit 2 hours we can't sit there.

Thanks again @sebt for your input, ill hope you can help me further with this query!

Problem solved

Thanks for all the help, this is the final query

SELECT t.* FROM
    tafels t
LEFT JOIN
    (SELECT tafelnr FROM
          tafels_regel tf
    INNER JOIN
          reserveringen res
    ON tf.reserveringsnr=res.reserveringsnr
    WHERE
    res.datum="2013/01/12" AND
    res.eindtijd > "19:00:00" AND
    res.begintijd < "21:00:00" 
    )
ReservationsAtThisTime
ON t.tafelnr=ReservationsAtThisTime.tafelnr
WHERE ReservationsAtThisTime.tafelnr IS NULL

Answer 1

I think your question is phrased a bit confusingly, because of the ambiguity between the SQL "Tables" (database tables), and one of the objects in your database, which is (I guess) a restaurant table, called "tafel" in Dutch?

(Let's go on calling them "tafel", as this removes the ambiguity)

To SELECT only the tafels which are unreserved, you can LEFT join all the tafels to the set of reservations which are current at [desired time], and return only the tafels which don't have such a reservation:

SELECT t.* FROM
    Tafels t
LEFT JOIN
    (SELECT tafelnr FROM
          Tafels_regel tf
    INNER JOIN
          Reserveringen res
    ON tf.reserveringnr=res.reserveringnr
    WHERE
    res.Datum=[desired date] AND
    res.begintijd<[desired time] AND
    res.eindtijd>[desired time]
    ) ReservationsAtThisTime
ON t.tafelnr=ReservationsAtThisTIme.tafelnr
WHERE ReservationsAtThisTime.tafelNR IS NULL

This would perhaps still need some tweaking, as:

1. Reservations spanning days (i.e. staring on one day and ending on another) would not be handled properly. (But maybe you don't need this)
2. A table would be shown as available at say 10/01/2012 12:30, when it's in fact reserved from 10/01/2012 12:45 (perhaps no-one is interested in a table that isn't free for at least X minutes - 1 hour? - AFTER their desired start time?).

EDIT (to answer OP's comment below):

Looking at your data, I can see that you have Tafel 1 reserved from 19:00 to 21:00 on 12/01/2013. I can see why you're not getting the expected results (i.e. Tafel 1 should not be shown, as it's not free):

1. The date you're using in the query is in 2012 rather than 2013. (This originated perhaps in my example, where I used 2012)
2. I designed my example to use a single, point-in-time "desired time". My thoughts were that the "desired time" would be a point (e.g. 19:30) - and so a reservation starting (e.g.) 19:00 and ending 20:00 would make this desired time unavailable. Because the "desired time" you're using is a range (19:00 to 21:00), and it happens to be exactly the same as the range of the reservation, the "<" and ">" in the query are failing to pick up this reservation as conflicting with the "desired time" ("<=" and ">=" instead would fix this).

However, since you said that all reservations are 2 hours, it would be easier to specify a "conflicting reservation" just using the desired Start time, like this: a "conflicting reservation" is one which occupies any time in the 2 hours following the desired Start time:

a. res.Datum = [desired date]; and b. res.eindtijd>[desired start time]; and c. res.begintijd<[desired start time + 2 hours]

I'm not completely sure how the code would work to do the addition of 2 hours (my speciality is MS-SQL rather than MySQL, and date/time operations can be a bit specific to the version of SQL).

EDIT2 (answering OP's re-redited question):

To help make things clear, I'll define these terms:

Existing booking: already there in the database. Has a TafelNr, Date, StartTime (begintijd) and EndTime (eindtijd)

Proposed booking: the one the user would like to make - naturally it doesn't exist yet in the database, or have a TafelNr. Has a Date, StartTime, EndTime.

(You've implemented the query using both a StartTime and EndTime for the Proposed booking. I suggested just using a StartTime, and then using [StartTime+2 hours]. But which of these ways you use doesn't make a difference to the question we're talking about)

You're right that tafels 11 and 12 shouldn't be shown, if the query was working properly. Your Proposed booking from 19:00 to 21:00 should run into a conflict with the Existing booking from 20:00 to 22:00. But as you've found, this conflict is not detected as it should be.

The reason is that you've got the Start/End Time comparisons slightly wrong. The way you've coded it:

res.begintijd <= [Proposed Start time] AND
res.eindtijd >= [Proposed End time]

would only find an Existing booking to be conflicting if it spans the proposed booking: exactly filling the same time, or extending before and/or after the proposed booking.

Imagining the two bookings (existing and proposed) like parallel blocks of Lego sliding along adjacent lines is helpful. They can make no contact at all (good); or just make contact (one's End=the other's Start: good, no conflict - just!); or the end of one can extend over the start of the other (conflict); or one can span the other, starting at the same time or before the other, and ending at the same time as the other or later (conflict).

This is a trick I've worked out from working with booking systems years ago. The correct comparison (as in my previous edit above) to find a conflict is:

ExistingBookingEndTime>ProposedBookingStartTime AND
ExistingBOokingStartTime<ProposedBookingEndTime

or, using your object names:

res.eindtijd>"19:00:00" AND
res.begintijd<"21:00:00"

This may not make sense at first, but if you think about the Lego blocks it'll make sense.

Note that:

1. I'm using < and >, not <= and >=, because I assume bookings can extend right up to each other and "touch", as long as they don't overlap;

2. Watch out when comparing dates and times - it can give odd results just comparing them as if they were numbers or strings. I don't know how MySQL handles this, but it's something I'm always careful/paranoid about.

Answer 2

Well, I do not really understand your database structure. But to get you on track:

1. You can simply query all your tables
2. You exclude reversed tables from the result set

Something like:

SELECT tafelnaam FROM Tafels WHERE tafelnr NOT IN (SELECT tafelnr *OF YOUR RESEVED TABLES*)