Best way to put date range in a table in mySQL

Question

I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in designing at the availability table.

This is the structure in my rooms table,

room_id
room_type
room_name etc...

This is my current availability table structure

room_id (int)
week_id (int)
date (datetime)
availability (boolean)

is there any better way to achieve this?please help. even the tiniest advice will be greatly appreciated

Answer 1

Note: not a MySQL guy, I use MS SQL. I would try to focus less on if a room is available at the start, and try to focus on marking the dates the room is reserved. If you use a table that stores reservations;

CREATE TABLE [Reservations] (
ReservationID INT NOT NULL,
RoomID INT NOT NULL,
StartDate SmallDate,      --first day of reservation
Duration INT)             --number of days being reserved

Then when you want to see what rooms are available for a day, you can search for what rooms are being used that day (using the startdate and duration columns) and get the inverse. This also frees you up from trying to allocate space for each possible reservation slot, but just add a record into the reservations table when they are reserved.