Database design issue regarding identifying relationships and many to many relationships

I have a weird database design issue that I'm not sure if I'm doing this right or not. Since my current design is really complicated, I've simplified it in the following diagram with a comparison using houses and occupants (not my actual entities).

So, here is what part of the database design looks like:

Standard Conditions:

• Multiple houses
• Multiple floors per house
• Multiple bedrooms per floor

Not-so-standard Conditions:

• Each occupant can live in multiple houses
• Each occupant can have multiple bedrooms per house
• Each occupant can only have one bedroom per floor, per house (this is the tricky part) For example, they can have one bedroom on floor 1, one bedroom on floor 2 and one bedroom on floor 3, but never two bedrooms on the same floor

Thus, what I'm trying to accomplish is this. In the app design, I know the house, I know the floor and I know the occupant. What I need to find out with this information without the user specifying is what bedroom the occupant has based on those 3 criteria. There are two solutions. The first is that in the occupants_has_bedrooms table, I make the primary key the occupants_id, bedrooms_floors_id and the bedrooms_floors_houses_id. However, when I take away bedrooms_id from the primary key, the table is no longer an identifying relationship to the parent (bedrooms). It is an identifying relationship though because it couldn't exist without the parent. Therefore, something tells me I need to keep all four ids as the primary key. My second option is a unique index between those three values, however this is when I considered I may be approaching this wrong.

How do I accomplishing this?

Here's a general database design strategy that is not specific to MySQL but should still be helpful.

It's good that you know how you are going to query your data, but don't let that overly affect your model (at least at first).

The first thing to be clear on is what is the PK for each table? It looks you are using composite keys for floors and bedrooms. If you used an informationless key (ID column per table) strategy for all tables except your intersection table Occupants_has_bedrooms, it would makes your joins simpler. I'm going to assume you can, so here's how to go from there:

The first thing I would change is to get rid of floors_house_id column in bedrooms - this is now redundant and can be gotten from a join.

Next, make the following changes to occupants_has_bedrooms:

1. The PK for should only be two columns, occupants_id and bedroom_id. (why? Because a primary key should only contain enough info to uniquely identify a row).
2. Remove the bedrooms_floors_houses_id, as that's determined by bedrooms_floors_id and is not needed.
3. add a unique constraint on (occupants_id, bedrooms_floors_id) to enforce your "not so standard" conditions.

Finally, do an inner join with all tables except Occupants, add your three conditions in the WHERE clause. This should get you the result you want. If you really want the composite keys, you can still do it cut it gets messy. Sorry I'm not near an editor or I'd diagram it for you.