Optimal database schema structure strategy for a given situation

I'm developing an unique application that uses a MySQL database and I'm seeking some advice on my database schema strategy before I go and implement it.

(Note: this is an abstract description of what I'm doing, the actual application i'm building is very similar in structure but not consisting of the actual components I name below.)

I'm building an application that will consist of: 1. Room Managers 2. Rooms 3. Guests

Every room manager is allowed to pick any number of rooms to 开发者_如何学JAVAmanage, and guests can sign up to be in the rooms that are managed by a room manager. Guests can also select other guests to be part of their 'entourage' in the room. I've sketched out a diagram of the tables I'm thinking about using for this system's database implementation:

One other important thing to note is that this process will repeat itself once a week. Managers will pick rooms to manage, and guests will sign up to be in those rooms and bring an entourage of other guests. So as time goes on the sizes of the 'managers_rooms,'guest_reservations,' and 'guest_entourage' tables will increase the fastest. I want to give managers the ability to see all of the guests that have signed up to be in their rooms along with that guest's entourage, so that means i'll have to query the guest_reservations and guest_entourage tables (presumably with a join operation).

My question is: is this overall a good strategy, and will it cause problems as it scales and grows? I'm using MySQL with InnoDB tables and foreign key constrains + btree indexes where it would be appropriate. Any pointers? Tips? precautions? Thanks!

EDIT2: I will be making my 'managers_rooms' table require a unique combination of 'managers_id, rooms_id, and date' as a key. 'date' using the MySQL DATE field.

---

"good" is really hard to evaluate - the only criteria you mention are scalability.

In general, I'd say your proposal should work fairly well - the only refinement I'd suggest (similar to PPrice) is that if you have indeed got the concept of "period" - for instance a week - you might want to model that as a separate table in your database, and link reservations to that period.

This might reduce the need for date arithmetic when querying, and increase the likelihood of hitting an index; I'd only do it if this "period" concept is indeed a core business domain entity, though.

In order to evaluate your design, I'd think through the business logic and draw up the most likely queries you will have to support - "find all reservations for guest", "find all guests who have reserved a room from a given manager", "find all unbooked rooms for period" etc. and sketch out how you'd implement them using your design. In my experience, 90% of queries are obvious - 5% take some time, and 5% show a missing concept in the data model.

I'd also look at other evaluation criteria - maintainability is often overlooked.

---

This structure should work fine. Just be sure to have indexes on the columns that will be in your query criteria, like the date column. Also, I would suggest that you pre-load a test database with a few million records and run your queries while profiling. This will help find any scaling issues or missing indexes early on.