Junction table joining multiple tables

Question

I have a single table with images which I need to link to 6 other tables. Let's say those tables are - Users, Tables, Foods, Restaurants, Categories, and Ships.

Should I create 6 different junction tables so each table has it's own junction table - Images_Users, Images_Tables, Images_Restaurants etc..?

Or is it better to create one table with a field to distinguish where it links - Images_Entity with fields- Id, Image_Id, Entity_Id, Entity_Type(I use this to distinguish whether its a user table, foods, or whatever). I don't like this solution since I will lack FK constraint in this case, but I'm leaning towards since the project will already have a large number of tables.

Perhaps there is a third approach? Create 6 image tables? Which solution is the best performance wise?

EDIT* Database will be used to display data, insert, update performance is not an issue, only select statements. I just figured out that no image can link to two entries(This makes the junction tables redundant).

Let me rephrase question entirely- What is the best way to connect Table with only one of the 6 other tables using a one to many association?

So Images table should contain FK and can link to only one of the 6 tables, never two at the same time.

The solution is based on how the data will be used. You give no indication of how the database will be used, so there is very little information to give an informed answer. — Gordon Linoff, Sep 21 '14 at 18:06
One to many in what direction? Can one restaurant have many images, or can one image belong to many restaurants? — GolezTrol, Sep 21 '14 at 18:50
One restaurant can have many images, one image may belong to only one restaurant. — Hovan Gourmet, Sep 21 '14 at 18:53

GolezTrol · Answer 1 · 2014-09-21T18:58:37.853

One possible approach is to add a UserId, RestaurantId, TableId, FoodId etc to the Images table.

That way, you can add a proper FK to each of those columns. Using a constraint or trigger (depending on the DBMS), you can enforce that exactly one of those fields is not null. The actual validation of that one filled in id is handled by the FK constraints.

This way it is fairly easy to enfore all the rules you want to have.

With separate junction tables this is harder to manage. When you insert a junction row for a Table_Image, you must validate whether there is no such record for any of the other entities in the other junction tables.

score 1 · Accepted Answer · edited May 23 '17 at 12:24

You could use exclusive FKs or inheritance as described in this post.

If you opt for the former, the CHECK employed for exclusive FKs would need to use slightly different syntax (from what was used in the link) to be practical:

enter image description here

CHECK (
    (
        CASE WHEN UserID         IS NULL THEN 0 ELSE 1 END
        + CASE WHEN TableID      IS NULL THEN 0 ELSE 1 END
        + CASE WHEN FoodID       IS NULL THEN 0 ELSE 1 END
        + CASE WHEN RestaurantID IS NULL THEN 0 ELSE 1 END
        + CASE WHEN CategoryID   IS NULL THEN 0 ELSE 1 END
        + CASE WHEN ShipID       IS NULL THEN 0 ELSE 1 END
    )
    = 1
)

The CHECK above ensures only one of the FKs is non-NULL at any given time.

BTW, your suspicions about "dynamic" FK (with the "type" field) were correct.

Junction table joining multiple tables

2 Answers2