Designing a database structure for Partners and Websites

database-design

I have two tables, Partners and Websites. I also have an "associative" table called Partners_Websites that retains the relationships "between" the rows of the tables Partners and Websites. In the Partners table, there are rows that I want to relate to all rows in the Websites table (even rows that can be added in the future).

Let's say I have P1, P2, P3 and W1, W2 and W3:

  • I want P1 related to W1 and W3

  • P2 related to W1 and W2

  • P3 related to all Websites

Then the Partners_Websites table will look like:

P1, W1
P1, W3
P2, W1
P2, W2
P3, W1
P3, W2
P3, W3

The question

If I add W4, I will need to add a new row in the Partners_Websites table to relate it to P3. How can I define "P3 is related to all Ws" without requiring the Partners_Websites table? I was thinking in adding a "flag" in Partners (that is, a boolean column named is_related_to_all) but I think it looks wrong (I may end up with hundreds of is_related_to_all = false).

Is there any way of doing this using relationships?

Best Answer

I would suggest that you would still want the relationships listed in your junction table, so that your data model is consistent. Also otherwise you end up having to UNION two results together all the time (one side for the "all"s and one side for the "some-or-none"s) or having filtering clauses of the form property1 = value OR property2 IN (<set>) - both of which can cause efficiency issues depending on the query planner of the engine you are using.

To enforce that P3 relates to all new entries in WebSites without needing to do that everywhere in your business logic that might add sites, you could perhaps define a trigger on that table which checks for Partner entries that are set as global and adds the relationship rows as needed. You need to take care with triggers as they can be quite inefficient (though for most database entities a performance hit on writing is worth a boost when reading, as read operations are far more numerous and concurrent). The other thing to be wary of with triggers is that magic actions behind the developer's back can make debugging more difficult, but if you make sure all is well documented and understood by your team that need not be an issue.

Related Question