What associations do I need between models in this scenario?

I am making a booking system using rails.

I have 3 models - visitors, rooms and bookings.

Visitors can stay in rooms.

A booking can have many visitors and be in many rooms (or shared). A booking also has a start/end date.

Visitors could potentially move between rooms within a booking, and the start/end dates of these room allocations would need to be known.

Would a single join table suffice here? I.e. vi开发者_开发百科sitor_id, room_id, booking_id?

Or how would the associations between these models look? Would I need additional tables i.e. room allocation.

Thank you very much.

I think you should have room_allocations table.

Visitors could potentially move between rooms within a booking, and the start/end dates of these room allocations would need to be known.

Is it possible to create new booking if the visitor change the room? In this case you don't need the additional table.

A booking can have many visitors and be in many rooms (or shared). A booking also has a start/end date.

It is not clear what do you mean by 'or shared'.

Otherwise, a general procedure is to list your entities and then define relationships between them. It would go something like this - and I am not trying to give definitive answers here, but to illustrate how thinking about it might go:

Entities

First try to define your attributes and group them into entities. Making a data dictionary is nice, especially if working in teams.

Relationships

Try to be specific - saying that booking can have many visitors is less helpful compared to giving the relationship a name (semantics is very important in system design).

Saying booking made by visitor(s)/visitor(s) booked a booking is better, because you it allows you to distinguish between a relationship with a name: booking is made for visitors/visitors booked (and also helps you realise if you have both and helps determine cardinality; naming them in both ways also can help, although sometimes you end up with awkward language - which might or might not be indication of design problems; for example here visitor(s) booked a booking sounds bad and makes me question if booking is not a relationship table in itself - a weak entity).

So, if you do go and name all the relationships we could answer you much more precisely.

Note: Having a three way many-to-many relationship table can be denormalized in a non-trivial way. And one of the reasons that normalization is very important is not to have beautiful, idealistic database design, but to be able to manage potential inconsistencies in your database (as in - if you decide to have denormalized design to know what kind of anomalies your database could represent; which are fairly obvious up until 3NF).

Something like this should get you started:

There are probably neater ways to cover Booking & Room Booking. In OO terms, you could model it using the composite pattern. That would probably make the logic cleaner. You'd still need to map to tables of course; whether it's worth exploring depends on the wider needs of your app.

hth.