Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I would add one more ID column to ROOM table, because what if 1 user ID wants 2 rooms, so you would like to index each room with unique id. Just a tip so you would have no problems in future :) –  Wiggler Jtag Jan 24 at 22:23
    
So far nothing prevents multiple people from booking one and the same room for one and the same day. –  AlexKuznetsov Jan 25 at 16:45
    
@WigglerJtag Can you please elaborate on what you mean? As far as I can tell, people can make a booking for different rooms, as long as time of booking isn't exactly same. –  Ciwan Jan 26 at 11:24
    
@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) –  Ciwan Jan 26 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? –  AlexKuznetsov Jan 26 at 22:20

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!)

share|improve this answer
    
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 –  Ciwan Jan 26 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.

share|improve this answer
    
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." –  Ciwan Jan 26 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

share|improve this answer
    
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. –  Ciwan Jan 26 at 11:35
    
@Ciwan Assuming a user books 2 room , how would you identify which 2 rooms he has booked without ID –  Computernerd Jan 26 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!? –  Ciwan Jan 27 at 8:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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