Member Avatar for crapmyster

Hello,

Just having a liitle problem here.

Basically,
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.

Thanks,
Mark

  • 4 Contributors
  • 5 Replies
  • 1K Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by dansteel
Member Avatar for debasisdas

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

Member Avatar for crapmyster

How would I then go about solving this problem

Member Avatar for debasisdas

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.

Member Avatar for Momerath

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.

Member Avatar for dansteel

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, networking, learning, and sharing knowledge.