Modelling a relationship which has multiple one-to-many parents

Question

I am attempting to model a relationship which could have two parents, one of them being optional, and the other being mandatory. The design should have the following rules,

• A House must exist as the top level parent
• A Bedroom must have a house as a parent
• A House can have many Bedrooms
• A House can have many Photos of the house
• A Photo could also optionally be linked to a Bedroom, i.e. it is a photo of a bedroom in a house
• A User should only be able to access a House and any child (Bedroom and Photo resources) that are owned by them (through the UserID field in House table)

My current design looks like this:

    +---------+         +-----------+
    | House   |         | Bedroom   |
    +---------+         +-----------+
+---> HouseID <---+     | BedroomID <----+
|   | UserID  |   +-----+ HouseID   |    |
|   | ...     |         | ...       |    |
|   +---------+         +-----------+    |
|                                        |
|                                        |
|           +-----------+                |
|           | Photo     |                |
|           +-----------+                |
|           | PhotoID   |                |
+-----------+ HouseID   |   OPTIONAL     |
            | BedroomID +----------------+
            | ...       |
            +-----------+

I currently enforce that a Photo must be linked to a House through the FK, so I can perform a JOIN to get the original UserID, as I know this key will already exist, but it feels like this is not the right way to go about it.

I ideally want to return Photos that are only linked to the House when querying data about the House, and then only Photos linked to a Bedroom when querying data about a Bedroom. I know this is quite easy to do via JOINs, but it makes me question whether this is the right design, as it seems to be more "working around the design" than fixing the root problem.

Would it be more useful to use mapping tables here? Or any further ideas on how to improve it?

Answer 1

I think your table design is good and normalized.

One thing that is not guaranteed yet is that if a photo references a bedroom, that bedroom should belong to the same house as the photo.

You could get that with an additional UNIQUE constraint:

ALTER TABLE bedroom
   ADD UNIQUE (houseid, bedroomid);

Then the foreign key constraint could be defined as:

ALTER TABLE photo
   ADD FOREIGN KEY (houseid, bedroomid) REFERENCES bedroom (houseid, bedroomid);

An extra index is not nice from a performance angle, but you want an index on the houseid column of bedroom anyway because of the foreign key, so an additional column shouldn't hurt.