Need help on a database model design

Question

I am creating a small booking application with Laravel. It lists some centers. Each center has its own unique rooms. Each room may be booked several times (with dates). From my application, I post the 'from' and 'to' dates. I need to add the date range as booking dates of a particular room. Remember, a room can be booked more than once, but to a limited number of times. I need this because I want to mark the booked days with different colors in a calendar in the front-end.

Just in case you want to know, I'm more concerned about the booked dates more than everything else.

Thanks.

UPDATE:

I want advice on how to setup the database tables and columns. Then, how to set the booked dates of a room. Is it enough if I set a Start and End date to the booking, or do I need a row for each day that is booked?

Thanks.

Answer 1

Your question is not very clear on this point but I'm assuming you want to find overlaps with a proposed, new booking.

Let the values for the proposed booking be held in the variables @StartDate, @EndDate and @RoomID. The SQL will be

select
    RoomID,
    StartDate,
    EndDate
from TheBookingTable as t
where t.RoomID =  @RoomID
and T.StartDate <= @EndDate
and T.EndDate >= @StartDate;

This query will return all current bookings which overlap your proposed booking, or an empty set if none exist.