I plan on building a web based room booking application for meeting rooms at a company. The requirements are not set in stone and are pretty much left to me, as long as these core ones are met:

  1. Users can view monthly schedule for meeting room, so see what days and times there are bookings for a particular room.
  2. Users can book a meeting room
  3. Users can edit,delete a booking once made.
  4. Receptionists have power to cancel bookings, even though they were not the ones that made them.

If I have missed any obvious ones, please point them out.

So with the above in mind, I have come up with the following ERD.

erd room booking

Can you see any obvious issues with the above ERD? I have gone through the above scenarios / requirements, and I believe the above design satisfies them all, but it is possible that I am very much mistaken (has happened before).

Any help and feedback would be greatly appreciated.

  • So far nothing prevents multiple people from booking one and the same room for one and the same day.
    – A-K
    Jan 25, 2014 at 16:45
  • @AlexKuznetsov People are allowed to do that (e.g. book Picaso Room for 10:00, then make another booking for Picaso room for 15:00)
    – J86
    Jan 26, 2014 at 11:25
  • Can you have two meetings going on in the same room at the same time? If not, how will your design prevent it?
    – A-K
    Jan 26, 2014 at 22:20
  • @AlexKuznetsov I thought one would check that through code before attempting to write to Database. How would you prevent that at the Db level?
    – J86
    Jan 27, 2014 at 8:42
  • Сan you show me the final Room Booking System ER-diagram?
    – Stanislav
    Dec 23, 2019 at 21:32

3 Answers 3


How do you know how long a room is booked for? Your model indicates when a booking starts, but when does it end?

Also, your ERD shows foreign keys from BOOKING to USER and ROOM but these columns aren't acknowledged in your BOOKING table. Some other answers have interpreted that as the columns being missing. You should be explicit in your diagram to avoid confusion (or add the FK columns if you missed them!)

  • Good catch about the end of a booking @JoelBrown. Thanks. As for showing the FK columns ... I use Entity Framework, and it handles it for me, but since this is about Db Design, I have gone ahead and updated my ERD. Thx
    – J86
    Jan 26, 2014 at 11:30

I agree with Wiggler Jtag's recommendation. Here's why:

Describe the relationship between USER and ROOM. If it is "a user can have zero, one, or more rooms booked" then you have a one to many relationship.

Describe the relationship between ROOM and USER. If it is "a room can have zero, one, or more bookings by a user" then this is also a one to many relationship.

Using normalization, you would define an intersection table. You have this defined as BOOKING. BOOKING should contain the PKs (primary keys) from USER and ROOM. PKs should be unique. The BOOKING entity would have attributes USER_ID, ROOM_ID, DATETIME, and TITLE.

If you wish to ensure that only one USER can book a ROOM for a particular DATETIME, then you can add a unique concatenated index on BOOKING of ROOM_ID, DATETIME. This will ensure that there will be only one row for a room at a particular time.

Primary index (or unique index) on the PKs of USER and ROOM will also ensure that you don't have more than one row per user or one row per room.

  • The relationship is: "a user can have zero, one, or more bookings for the same or different rooms, as long as time does not overlap."
    – J86
    Jan 26, 2014 at 11:37

The problem with the current design is that we are unable to uniquely identify which room a user has booked if he has booked more than 1 room .

To solve the problem , we should add another column : Room Number to the TABLE ROOM

This allows the composite key : Room Number& ID to form a primary key which would uniquely identify any booking made by a user.

The Room Number & ID would be foreign keys in the TABLE BOOKING

  • Either I am going mad, or missing something so obvious that I'll hate myself for it later, but I just can not see how "we are unable to uniquely identify which room a user has booked when he has booked more than 1 room" :/ As far as I can tell, the diagram allows this easily.
    – J86
    Jan 26, 2014 at 11:35
  • @Ciwan Assuming a user books 2 room , how would you identify which 2 rooms he has booked without ID Jan 26, 2014 at 17:48
  • You will get a list of the user's bookings, and through that you can tell what booking has what room .. no!?
    – J86
    Jan 27, 2014 at 8:43

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.