Database design: Normalizing a "(many-to-many)-to-many" relationship

Question

Short version

I have to add a fixed number of additional properties to each pair in an existing many-to-many join. Skipping to the diagrams below, which of Options 1-4 is the best way, in terms of advantages and disadvantages, to accomplish this by extending the Base Case? Or, is there a better alternative I haven't considered here?

Longer version

I currently have two tables in a many-to-many relationship, via an intermediate join table. I now need to add additional links to properties that belong to the pair of existing objects. I have a fixed number of these properties for each pair, though one entry in the property table may apply to multiple pairs (or even be used multiple times for one pair). I'm trying to determine the best way to do this, and am having trouble sorting out how to think of the situation. Semantically it seems as if I can describe it as any of the following equally well:

1. One pair linked to one set of a fixed number of additional properties
2. One pair linked to many additional properties
3. Many (two) objects linked to one set of properties
4. Many objects linked to many properties

Example

I have two object types, X and Y, each with unique IDs, and a linking table objx_objy with columns x_id and y_id, which together form the primary key for the link. Each X can be related to many Ys, and vice versa. This is the setup for my existing many-to-many relationship.

Base Case

Now additionally I have a set of properties defined in another table, and a set of conditions under which a given (X,Y) pair should have property P. The number of conditions is fixed, and the same for all pairs. They basically say "In situation C1, pair (X1,Y1) has property P1", "In situation C2, pair (X1,Y1) has property P2", and so on, for three situations/conditions for each pair in the join table.

Option 1

In my current situation there are exactly three such conditions, and I have no reason to expect that to increase, so one possibility is to add columns c1_p_id, c2_p_id, and c3_p_id to featx_featy, specifying for a given x_id and y_id, which property p_id to use in each of the three cases.

This doesn't seem like a great idea to me, because it complicates the SQL to select all properties applied to a feature, and doesn't readily scale to more conditions. However, it does enforce the requirement of a certain number of conditions per (X,Y) pair. In fact, it is the only option here that does so.

Option 2

Create a condition table cond, and add the condition ID to the primary key of the join table.

One downside to this is that it doesn't specify the number of conditions for each pair. Another is that when I am only considering the initial relationship, with something such as

SELECT objx.*, objy.* FROM objx
  INNER JOIN objx_objy ON objx_objy.x_id = objx.id
  INNER JOIN objy ON objy.id = objx_objy.y_id

I then have to add a DISTINCT clause to avoid duplicate entries. This seems to have lost the fact that each pair should exist only once.

Option 3

Create a new 'pair ID' in the join table, and then have a second link table between the first one and the properties and conditions.

This seems to have the fewest disadvantages, other than the lack of enforcing a fixed number of conditions for each pair. Does it make sense though to create a new ID that identifies nothing other than existing IDs?

Option 4 (3b)

Basically the same as Option 3, but without the creation of the additional ID field. This is accomplished by putting both original IDs in the new join table, so it contains x_id and y_id fields, instead of xy_id.

An additional advantage to this form is that it doesn't alter the existing tables (though they aren't in production yet). However, it basically duplicates an entire table multiple times (or feels that way, anyway) so also doesn't seem ideal.

Summary

My feeling is that Options 3 and 4 are similar enough that I could go with either one. I probably would have by now if not for the requirement of a small, fixed number of links to properties, which makes Option 1 seem more reasonable than it otherwise would be. Based on some very limited testing, adding a DISTINCT clause to my queries doesn't seem to impact performance in this situation, but I'm not sure that Option 2 represents the situation as well as the others, because of the inherent duplication caused by placing the same (X,Y) pairs in multiple rows of the link table.

Is one of these options my best way forward, or is there another structure I should consider?

Answer 1

• Option 1

  *This doesn't seem like a great idea to me, because it complicates the SQL to select all properties applied to a feature…

  It does not necessarily complicate query SQL (see conclusion below).

  …and doesn't readily scale to more conditions…

  It scales readily to more conditions, as long as there are still a fixed number of conditions, and there aren't dozens or hundreds.

  However, it does enforce the requirement of a certain number of conditions per (X,Y) pair. In fact, it is the only option here that does so.*

  It does, and although you say in a comment that this is "the least important of my requirements", you haven't said it doesn't matter at all.

• Option 2

  One downside to this is that it doesn't specify the number of conditions for each pair. Another is that when I am only considering the initial relationship…I then have to add a DISTINCT clause to avoid duplicate entries…

  I think you can dismiss this option because of the complications you mention. The objx_objy table is likely to be the driving table for some of your queries (eg "select all properties applied to a feature", which I am taking to mean all properties applied to an objx or objy). You can use a view to pre-apply the DISTINCT so it is not a matter of complicating queries, but that's going to scale very badly performance-wise for very little gain.

• Option 3

  Does it make sense though to create a new ID that identifies nothing other than existing IDs?

  No, it doesn't — Option 4 is better in every regard.

• Option 4

  …it basically duplicates an entire table multiple times (or feels that way, anyway) so also doesn't seem ideal.

  This option is just fine — it is the obvious way of setting up the relations if the number of properties is variable or subject to change

Conclusion

My preference would be option 1 if the number of properties per objx_objy is likely to be stable, and if you can't imagine ever adding more than a handful extra. It is also the only option that enforces the 'number of properties = 3' constraint — enforcing a similar constraint on option 4 would likely involve adding c1_p_id… columns to the xy table anyway*.

If you really don't care much about that condition, and you also have reason to doubt that the number of properties condition is going to be stable then choose option 4.

If you aren't sure which, choose option 1 — it is simpler and that is definitely better if you have the option, as others have said. If you are put off option 1 "…because it complicates the SQL to select all properties applied to a feature…" I suggest the creating a view to provide the same data as the extra table in option 4:

option 1 tables:

create table prop(id integer primary key);
create table objx(id integer primary key);
create table objy(id integer primary key);

create table objx_objy(
  x_id integer references objx
, y_id integer references objy
, c1_p_id integer not null references prop
, c2_p_id integer not null references prop
, c3_p_id integer not null references prop
, primary key (x_id, y_id)
);

insert into prop(id) select generate_series(90,99);
insert into objx(id) select generate_series(10,12);
insert into objy(id) select generate_series(20,22);

insert into objx_objy(x_id,y_id,c1_p_id,c2_p_id,c3_p_id)
select objx.id, objy.id, 90, 91, 90+floor(random()*10)
from objx cross join objy;

view to 'emulate' option 4:

create view objx_objy_prop as
select x_id
     , y_id
     , unnest(array[1,2,3]) c_id
     , unnest(array[c1_p_id,c2_p_id,c3_p_id]) p_id
from objx_objy;

"select all properties applied to a feature":

select distinct p_id from objx_objy_prop where x_id=10 order by p_id;

/*
|p_id|
|---:|
|  90|
|  91|
|  97|
|  98|
*/

dbfiddle here

Answer 2

I believe any of these options could work, but I would go with option 1 if the number of conditions is truly fixed at 3, and option 2 if it is not. Occam's razor also works for database design, all other factors being equal the simplest design is usually the best.

Although if you want to follow strict database normalization rules I believe you'd need to go with 2 regardless of whether the number of conditions is fixed.