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

database-designerdpostgresql

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?

Best Answer

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.

Related Solutions

Multiple foreign keys with shared columns for weak entities

Both solutions for T or T-alternate are considered denormalizations. Denormalizations optimize a datamodel that is normally in third normal form into a structure that is more convenient for select queries. The correct data already exists in CC and CD. T or T-alternate are duplicating the data. With duplicate (denormalized) data you have to make sure that data cannot get out of synch with its parent.

I think the first option is the better one. The constraints are simple and straight-forward. There is no check constraint on multiple fields with nulls. You need to make sure that any insert into CC or CD also inserts into T. After-insert triggers on CC and CD would automatically insert A,B,C,null into T or A,B,null,D into T.

One thing to plan for is that a parent has two children, each with many rows per parent. Lets assume a row in P has 3 rows in CC, 0 in CD. If you write a query over P inner joining to CC and CD, it shows zero rows. An left outer join (parent on left) shows 3 rows.

Lets assume CC has 3 rows and CD has 4 rows. Inner join and outer join queries would show 3*4 = 12 rows.

Lets assume a 3rd set of data. CC is the main child table with 20 rows per P. CD is a descriptive text field with 3 rows per P. You could reduce the resulting row to 20 per P with an aggregation function like Oracle 11g's LISTAGG over CD. This combines the D descriptive field into a comma separated list "Planes, Trains, Automobiles".

Having CCA and CDA fields in T-alternative increases the chances for data to get out of synch, since CCA might not equal CDA when something goes wrong in the code. The T-alternative is not the standard way of approaching this problem. The first option does have some challenges, but it is simpler, more standard, and more likely to hold up under production systems.

Sql-server – Table design for child tables with multiple many-to-many relationships

I'd recommend creating a table which encompasses subnets, VLANs and VPLSs; you can then have a simple self-join table. If these three concepts each have a significant number of unique fields, you can use vertical partitioning and create a sister table for each of the three. Using pseudo-code:

* Networks
NetworkID PK

* Locations
LocationID PK

* SubNetworks  -- This concept encompasses SubNets, VLANs, and VPLSs
SubNetworkID PK,
NetworkID  NOT NULL REFERENCES Networks

* SubNetworkLocations
SubNetworkID NOT NULL REFERENCES SubNetworks,
LocationID   NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkID, LocationID)

* SubNets
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VLANs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VPLSs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* SubNetworkRelationships
SubNetworkA NOT NULL REFERENCES SubNetworks,
SubNetworkB NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkA, SubNetworkB),
UNIQUE (SubNetworkB, SubNetworkA)  -- For efficient querying from the other direction

As with any many-to-many table, be sure your sproc for inserting into SubNetworkRelationships checks for the existence of both { A, B } and { B, A } before inserting a new row. You can use check constraints to prevent a sub-network from relating to itself.

In this model, there's no RDBMS-level enforcement of the rule "if two sub-networks are related, they must have all of the same locations." I'm going to go out on a limb and assume that all such tied-together sub-networks must belong to the same Network, too. If you really want this:

* Networks
* Locations

* SubNetworkGroups
SubNetworkGroupID PK,
NetworkID         NOT NULL REFERENCES Networks

* SubNetworkGroupLocations
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
LocationID        NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkGroupID, LocationID)

* SubNetworks
SubNetworkID PK

* SubNetworkGroupMemberships
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
SubNetworkID      NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkGroupID, SubNetworkID)

* SubNets
* VLANs
* VPLSs

A group of SubNetworks belong to one Network but zero or more Locations. A SubNetwork can belong to multiple groups. You'd still need to enforce that the various groups a sub-network belongs to all have the same locations, so I'm not sure this buys you much.

Best Answer

Related Solutions

Multiple foreign keys with shared columns for weak entities

Sql-server – Table design for child tables with multiple many-to-many relationships

Related Question