What should be my MongoDB schema for room booking?

Question

I am building a room booking system in nodejs. Currently I have hotels , rooms and bookings as collections.

rooms is referenced to hotels and bookings is referenced to rooms.

booking.js

const bookingSchema = new mongoose.Schema({
    room: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'rooms'
    },
    start: Date,
    end: Date

});

rooms.js

const roomSchema = new mongoose.Schema({
    roomid: String,
    hotel: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'hotel_managers'
    },
    type: String,
    price: Number,
    capacity: Number,
    facilities: [String],
    amenities: [String],
    img: [String]

});

hotels.js

const hotel_manager_schema = new mongoose.Schema({
    username: {
        type: String,
        required: true,
        unique: true
    },
    password: {
        type: String,
        required: true
    },
    hotelname: {
        type: String,
        required: true
    },
    role: {
        type: String,
        default: 'manager'
    },
    location: {
        type: String,
        required: true
    },
    img:{
        type: String,
        required: true
    }
})

N.B. This is a service provider ended system, so a hotel is basically a hotel manager with his credentials.

What i want to achieve is when a user sends a query for a given date range, I want to return all the available hotels as well as rooms in a hotel that don't have any booking in the query date range.

I am new in MongoDB so any tips or suggestions on how I can do what I want would be of great help.

root · Accepted Answer · 2021-11-24T01:01:48.947

3

Here's what you can do according to your schema model architecture; we wanna list all available hotels as well as rooms in hotels that don't have any booking at a given date range.

So to achieve this, we're gonna fetch all bookings that overlaps with date range provided in query and return their room ids; after that we fetch all rooms excluded the array of room ids returned from bookings.

const bookings = await Booking
      .find({
          $or: [
              { start: { $gte: from_date, $lte: to_date } },
              {
                  end: { $gte: from_date, $lte: to_date }
              },
              {
                  $and: [{ start: { $lte: from_date } }, { end: { $gte: to_date } }]
              },
          ],
      })
      .select('room');

const roomIds = bookings.map(b => b.room);

const availableRooms = await Room.find({ _id: { $nin: roomIds } })

You can extract hotel's data by populating Rooms hotel property field:

const availableRooms = await Room
  .find({ _id: { $nin: roomIds } })
  .populate('hotel', 'username password hotelname role location img')

I hope this would work for you.

edited Nov 24 '21 at 01:01

answered Nov 23 '21 at 18:14

root

151
5

I tried as you showed but for some reason it returns all the current bookings in the first query even if i query for a date range not intersecting any bookings. If it returns all the bookings, according to the queries it shouldn't have returned any rooms related to the bookings but surprisingly it returns the rooms containing the bookings. I ust be doing something wrong but I am not sure what it is. – f4him Nov 23 '21 at 19:55
1

I've made some changes, check them out, I believe this should work. – root Nov 24 '21 at 01:03
thanks it worked, really appreciate your effort on the booking overlap logic. – f4him Nov 24 '21 at 13:42
PS: The bigger bookings collection gets the slower your request query will be, if we're talking 500000 docs or more, you're gonna feel the difference; so try to delete old booking docs or archive them, and try to index room property field too, and select just room field when fetching for bookings as mentioned above, this way it'll help a little bit. – root Nov 24 '21 at 14:28
yes regarding that i guess i might run a schedule task to delete older bookings and thanks for the tips, i will keep those in my mind. – f4him Nov 24 '21 at 17:22

score 0 · Answer 2 · answered Nov 23 '21 at 16:02

0

i am expecting your database is already accumulated with some data and considering that all you have to do is just make a query in your bookingSchema.

    const availableRoom = await Booking.find({ //query today up to tonight
    created_on: {
        $gte: new Date(2012, 7, 14), 
        $lt: new Date(2012, 7, 15)
    }
})

Here Booking is a model. You can find details how to create model over HERE

You can find HERE how to query using dates

answered Nov 23 '21 at 16:02

Tanjin Alam

1,728
13
15

existing booking entries will always have a datetype start and end value. I want to return rooms available in a new date range based on the current bookings. I already know about the date queries but i am not being able to work with relational schemas. – f4him Nov 23 '21 at 16:36

What should be my MongoDB schema for room booking?

2 Answers2