Just having a liitle problem here.

I have identfied three entities, Bookings, Guests and Rooms for a reservation system for a hotel.

A guest can have many bookings.
A booking can have many guests.
A room can have many guests.
A guest can book many rooms.
A booking can have many rooms.
A rooms can have many bookings.

This would mean that all the entites would have a many-to-many relationship between each of them. Since you can't have many-to-many relationships in Access how would I correct this.


Recommended Answers

All 5 Replies

Trying to implement many-to-many is impractical. It can't be implemented in any database.

How would I then go about solving this problem

You should have status and booking from and booking upto dates in the rooms table .

There should be a booking ID in booking table, which is unique.

I would have Booking be the base.

A Booking has a Booker (link to guest)
A Booking has 1 or more Rooms
A Room has 1 or more Guests (which might not include the Booker from above).

You would use intermediate tables to represent the relationship between a particular booking and the rooms. Same for rooms to guests.

I would not put dates in the Rooms table as suggested by debasisdas. Rooms are static, they don't change (most hotels don't add new rooms to the building that often).

The Booking would have begin/end dates.

You should probably not try to represent rooms, but rather room reservations which are a combination of room type and date from/to

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.