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_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

  • I just want to store availability information for certain resource(Eg: Room) for certain date.(1 reservation is for whole day). I thought of having a row for weeks and then duplicate weeks with each day of the week. but i understand it's not good. if certain room is booked for certain date availability for that room should be false, so it can be skipped in searches. any idea how to accomplish that?
    – Van Dyke
    Commented Aug 6, 2019 at 3:56
  • It should be fairly simple. each resource (Eg: Room) is reservable on per date basis. and yes whole 365 days per year, I'm sorry i'm quite new to this and I don't know about Boolean arithmetic nor about scaling. I just want to store availability status for certain room on certain date. how can I do that? I'm open to even completely abandon my current table design and go into a new one
    – Van Dyke
    Commented Aug 6, 2019 at 3:59

1 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,
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.