Counting nonexisting records with foreign key

Question

I have two tables Room and RoomDate. Here's the structure for Room

Room
roomID
checkInDate
checkOutDate
roomType

Here's the structure for RoomDate. They have a one to many relationship.

RoomDateID
checkInDate
checkOutDate
RoomID // foreign key

Now I want to find how many rooms per roomType are available in a specific date range. That is, if a date range with a specified roomID does not yet exist in the RoomDate table, it can be added. This is how I do it.

• Count all records in the Room table per type. RoomTypes are single, double and triple.
• Count all records in the RoomDate that already have the inputed date range per type (E.G. if exist in single, cannot be added in single but can still be added in other room types).
• Subtract the RoomDate results from Room to get all rooms that are still available per type. So if there are no records in RoomDate, all rooms are available.

Sample input:

number of rooms in the database (RoomDate does not have records yet):
single rooms: 5
doubleRooms: 6
triple rooms: 7

User: 2015-11-11 - 2015-11-21 - 5 single rooms

Sample output:

five single rooms with the date range of 2015-11-11 - 2015-11-21 is added to the RoomDate table.
number of rooms in the database without the range 2015-11-11 - 2015-11-21:
single rooms: 0
doubleRooms: 6
triple rooms: 7

Next time the user enters a date in the range of 2015-11-11 - 2015-11-21 and again chose any number of single rooms, he cannot insert it in the database because there aren't any single rooms left. But he can still insert it with other room types double and triple as long as they are still available.

I used inner join before, but it does not count null records in RoomDate, so I mad individual queries instead.

Please review my code. I think this is too slow (6 HQL select statements), how can I improve this code and make it more efficient?

public long[] countRooms() {
    Session session = getSessionFactory().getCurrentSession();
    long[] count = new long[3];
    String query = "select count (r) from Room r where r.roomType=";
    count[0] = (long)session.createQuery(query+"0").uniqueResult();
    count[1] = (long)session.createQuery(query+"1").uniqueResult();
    count[2] = (long)session.createQuery(query+"2").uniqueResult();
    return count;
}

public long[] countAvailableRooms(LocalDate checkInDate, LocalDate checkOutDate) {
    Session session = getSessionFactory().getCurrentSession();
    String query = "select count(d) from Room r join r.roomDates d where d.checkInDate=:checkInDate and d.checkOutDate=:checkOutDate and r.roomType=";
    long[] count = countRooms();
    count[0] -= (long)session.createQuery(query+"0").setParameter("checkInDate", checkInDate).setParameter("checkOutDate", checkOutDate).uniqueResult();
    count[1] -= (long)session.createQuery(query+"1").setParameter("checkInDate", checkInDate).setParameter("checkOutDate", checkOutDate).uniqueResult();
    count[2] -= (long)session.createQuery(query+"2").setParameter("checkInDate", checkInDate).setParameter("checkOutDate", checkOutDate).uniqueResult();
    return count;
}

Answer 1

Database modeling

Is the presence of check-in/check-out dates on both tables intentional?

If RoomDate is more like a reservations table, then having the check-in and check-out dates in it makes sense. How is the pair of date columns in the Room table used then?

Determining occupancy

Getting occupancy by using an = check is quite simplistic, as that does not take into account later check-ins, or earlier check-outs. If you want the check to be more robust, it can be something along the lines of:

-- please check if BETWEEN includes the values or not
d.checkInDate BETWEEN :checkInDate AND :checkOutDate OR
d.checkOutDate BETWEEN :checkInDate AND :checkOutDate OR
d.checkInDate <= :checkInDate AND d.checkOutDate >= :checkOutDate

This counts existing reservations with check-in or check-out dates that either fall within or span over the requested date range.

Efficient SELECT

Could you not use a GROUP BY SQL statement (or the HQL equivalent) so that you make one query instead of three? - myself

I'm not very familiar with Hibernate, and we don't even know your choice of DBMS, so the following is borderline pseudo-query:

SELECT r.roomType, r.total - b.booked AS remaining FROM
    ((SELECT roomType, COUNT(1) AS total FROM Room GROUP BY roomType) AS r JOIN
        (SELECT roomType, COUNT(1) AS booked FROM RoomDate d JOIN Room USING roomID 
            WHERE d.checkInDate BETWEEN :checkInDate AND :checkOutDate OR
                d.checkOutDate BETWEEN :checkInDate AND :checkOutDate OR
                d.checkInDate <= :checkInDate AND d.checkOutDate >= :checkOutDate
            GROUP BY roomType) AS b USING roomType
    )

The idea here is not to fire 6 separate SQL statements, as you have found to be 'unsatisfactory' and 'inefficient'. What you need is one representation from your Room table that shows the total rooms:

$$\begin{array} {|r|r|} \hline roomType & total \\ \hline 0 & 5 \\ \hline 1 & 6 \\ \hline 2 & 7 \\ \hline \end{array}$$

A representation from your RoomDate table that shows the booked, i.e. unavailable rooms (e.g. after the five-single-rooms booking is done):

$$\begin{array} {|r|r|} \hline roomType & booked \\ \hline 0 & 5 \\ \hline 1 & 0 \\ \hline 2 & 0 \\ \hline \end{array}$$

Joining both on the roomType column:

$$\begin{array} {|r|r|r|} \hline roomType & total & booked \\ \hline 0 &5 &5 \\ \hline 1 &6 &0 \\ \hline 2 &7 &0\\ \hline \end{array}$$

And finally subtract the two columns:

$$\begin{array} {|r|r|} \hline roomType & remaining \\ \hline 0 & 0 \\ \hline 1 & 6 \\ \hline 2 & 7 \\ \hline \end{array}$$

You may want to consider if the modeling can be further improved to remove the inner JOIN. This is just something to get you started with.

edit This might be how you can do it in Hibernate/HQL... Again, I'm not familiar with Hibernate, and the casting may be done incorrectly... please test and experiment carefully.

// Assuming you now have a RoomType `enum`
Map<RoomType, Integer> map = new EnumMap<>(RoomType.class);
for (Object[] row : session.createQuery(query).list()) {
    map.put(RoomType.values()[(int)row[0]], row[1]);
}
System.out.println("Room type and free rooms:\n" + map);

Java code review

As you can see, the main solution to your problem lies in doing a more efficient SELECT once. With that out of the way, you may want to consider using a model class to represent the results, instead of a simply long[] array. It is not evident from the array itself how the indices are being mapped... if you decide to use a non-contiguous values to represent new room types, you may encounter issues mapping those.

You can also consider modeling your room types as a Java enum type, for starters.