CODEWITHSUNDEEP
ruby-on-railsdatabasee-commerce
newBike
newBike

Reputation: 15002

Design Room reservation availability check function

I'm struggling on designing a good reservation system

In the scenario, each room has many room_skus,

each room_sku record represents a physical room

because we can have many real rooms belongs to the same room type.

However, I don't have any idea to implement the availability.

What I can think of is that add a new model RoomAvailablity.

If a user books a single room (room_number: 413) from 2016-04-11 to 2016-04-13

I will create three records on the table RoomAvailablity

Then, when another user tries to book (room_number: 413) from 2016-04-12 to 2016-04-14

I will make a query like count = RoomAvailablity.where(room_number:413, occupied_date >= 2016-04-12 and occupied_date <= 2016-04-14)

If the count > 0, then the room is not available during the duration.

Is there any good recommendation to improve my thought?

Room

HAS_MANY RoomSku

id: integer
name:string
type:string (Double, Single, Triple)
description:string

RoomSku

belong_to: Room
HAS_MANY: RoomAvailablity
id: integer
name:string

RoomAvailablity

belong_to: RoomSku
occupied_date: date
room_sku_id: integer

Upvotes: 0

Views: 828

Answers (1)

Hass
Hass

Reputation: 1636

You're close, add a start date and end date to each availability.

belong_to: RoomSku
start_date: date
end_date: date
room_sku_id: integer

Query

RoomAvailablity.where(room_number: 413).where("start_date <= ? AND end_date >= ?", end_date, start_date).count

Or you can use it alternatively.

Find Available Rooms on date range

RoomAvailability.where.not("start_date <= ? AND end_date >= ?", end_date, start_date)

If user has booked the room for only 1 day, use the same start and end date.

Upvotes: 1

Related Questions