Reservation system with multiple users per time slot

Databases
1

I need to design a database that lets users reserve rooms by the hour (in 15 min increments) BUT the subtlety is multiple users can reserve a room at the same time (till it hits a limit).

rooms(id, title, max_users)
users(id, name)
reservations(room_id, user_id, starts_at, ends_at)

So for example, lets say you have rooms A, B and users X, Y, Z, and A.max_users = 2.

X can reserve A from 2pm-5pm
Y can reserve A from 1pm-3pm
Z cannot reserve A from 2pm-3pm (but Z can reserve A from 1pm-2pm)

Anyone know if there’s a better way to design the db than what I’m thinking? I’m going to have to display the availability of hundreds of rooms by the hour for each day (so for the above example it would show that A is available between 1pm-2pm) and I’m worried that it will be very inefficient the way I have it designed.

2

your table design is fine, it’s your application code that’ll be horrendous

one alternative is to have each 15-minute slot represented, that way you will not need to do the very complex “overlap” detection if you store only the start and end time (e.g. 2pm-5pm) – there will be more rows, but disk space is cheap, and your application code will not be as messy