# Is it possible to display this restriction in an ER diagram?

I have a question about modeling a particular kind of restriction, but I don't know the technical term for it, so I will illustrate it with an example.

I wish to make an ER diagram for a hotel room booking. Is it possible in an ER diagram to show that no two bookings of the same room, in the same hotel, in an overlapping time interval can occur? In my initial diagram, I have three entity types: Hotel, Room and Booking. But I don't know if I should use a ternary relationship or just three binary ones, or even if I should have three entity types. Any help would be greatly appreciated.

Edit: One booking can have many rooms in the same hotel. Also, I need to be able to book the same room, in the same hotel on distinct bookings, as long as the start date and end dates of booking X doesn't overlap with the start date and end date of booking Y

My problem is that (if I understand correctly) there is no way to tell from this diagram that two distinct bookings can't book the same room, in the same hotel in an overlapping time interval. Can I somehow modify this picture to show that constraint?

• I don't think you have a ternary relationship, but it looks like a binary. Bookings don't have to refer to the hotel since one room can only be in one hotel. As far as showing the constraint, I believe you don't show check constraints in ER diagrams. Also, I think you'll need an intermediate table between booking and rooms that stores what rooms are needed for which booking and potentially the start and stop dates if you'll allow different dates for different rooms in the same booking. Commented Feb 2, 2016 at 16:30

Entity relationship diagrams can be used to show constraints based on the relationships between entities. However, the particular constraint you want to show (that bookings do not overlap in time) is a constraint between rows in the same table, not between two or more tables.

The only single table constraint that can be illustrated in most common entity relationship diagrams is uniqueness, since ER diagrams can illustrate a primary key.

So, you have the following entities: Hotel, Room and Booking, right? Each Hotel has `N` Rooms, that's a 1-Many relations. Now, is the relation Room-Booking a 1-1 or 1-Many?

I believe that in the 'Room 1-Many Bookings' model, the relationship itself could have an attribute with the specific Date-Time of each booking.

Using the 1-1 relationship, you'd have to update the booking when it's already in the database everytime a new booking in that specific room is made. You can choose which one is better for you.

• Sorry, I forgot to include that one booking can have many rooms in the same hotel. Also, I need to be able to book the same room, in the same hotel on distinct bookings, as long as the start date and end dates of booking X doesn't overlap with the start date and end date of booking Y. Commented Feb 2, 2016 at 15:46
• So: One Hotel has Many Rooms, Each Room has Many Bookings as Each Booking can have many Rooms. In the model, I believe you can either specify it in the relationship itself (attributes Start Date and End Date) and work from there, or (I wouldn't recommend those), specify it in the Booking entity or make a new entity where to sole purpose is to inform the Start and End dates. Commented Feb 2, 2016 at 16:01
• I thought about specifying Start Date and End Date, but what I don't understand is how I can see that restriction from the diagram? Or maybe I have the wrong idea about the use of ER diagrams? I will add a picture to show what I mean. Commented Feb 2, 2016 at 16:12
• I think the booking entity can't have a relationship with the Hotel entity. You book a room, and THAT room belongs to the hotel. You find the hotel through the room. Made quick model: here. The restriction is implied in the ER by the relationship itself, since it has the attritibutes for Start/End Time, you read it as: Each `Booking` in a `Room` needs a `Start/End Time`. Commented Feb 2, 2016 at 18:18