I'm working on a shared booking system, tables would be a User table for each individual user and a Bookings table for the bookings.

A user can have multiple bookings and a booking can only have up to 8 users in each individual booking.

In each individual booking I need to store each user's id and their number of suitcases What would be an easy and efficient way to design this ? Seeing as in a booking there could be 8 different user foreign keys and 8 different number of suitcases related to their own user ?

Any pointers would be appreciated.

CodePudding user response：

That 8 users per booking limitation is a limitation of your system, not of any database. And it's an arbitrary one at that, but seeing the fields you're using it looks like homework more than anything actually useful, so that's a given I suppose. My point is that you can ignore that limitation when designing your database.

So with that in mind, what you have are three tables:

• User (id PK, name, address)
• Booking (id PK, suitcases)
• UserBooking (id PK, user_id FK(User), booking_id FK(Booking))

As to the 8 users per booking limit, either use triggers to reject inserts in UserBooking if the condition fails, or use a stored procedure and transactions to check before inserting.