1

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?

  • 1
    And what if the flag is not consistent with Partners_Website? – paparazzo Dec 13 '17 at 17:59
2

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.

0

Don't try to shortcut the insertion of data into your tables at the expense of making data retrieval faster. As a rule of thumb, data is read many more times than it is written. That means you should keep your data and your code as simple as possible for reading and if you need a bit of extra code for inserts (or updates or deletes) then that's where you should prefer to have it.

This doesn't necessarily mean that you don't want to have a flag on PARTNERS that indicates that new websites should be linked automatically. Here's why:

One question you need to ask yourself is what happens when you add new entries at either end of your many-to-many relationship. You can think of your intersection table as being a snapshot of the current state of relationships between existing partners and websites.

Could it not be the case that one partner happens to (coincidentally) be related to all current websites, but that a new website would not be related automatically? If this is conceivable then you need to distinguish between who's related to who right now and what happens when a new entry is added? You can't necessarily infer one from the other.

Having a "goes with everyone" flag will tell you that when you insert a website, that it needs to relate to the flagged partners automatically. It also tells you that if you insert a new partner with this flag on, you need to insert a relationship record for each existing website to go with the new partner! - So it goes both ways.

You should therefore consider fully populating your PARTNERS_WEBSITES table and at the same time, consider adding a PARTNERS.IS_RELATED_TO_ALL flag that tells you how to handle inserting both new websites and new partners.

0

Given that you have groups of sites, do they have persistent or meaningful relationships outside of the context of a partner? You already mentioned you want an all-sites-group, so that implies other groupings might be important to you as well. You could consider adding a SITE_GROUP table. You would define a group of W1 and W3, then relate your partner P1 to that group, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.