Polymorphic/Many-to-Many table relationship with dynamic IDs

Hiya, we’re designing PostgreSQL schemas for an app that has Users, and the user has Things of different types as well as Affinities for those Things, through a dynamic many-to-many table:

users_things_and_affinities table:

user_id
thing_type
thing_id
affinity_id

The idea is to have various many-to-many relationships between users and different tables within a certain category through this table (as an efficiency for a different feature of the app).

Things can refer to various different tables (e.g. tools, locations, buildings, etc)

Example record:

user_id = 1, thing_type = buildings, thing_id = 5, affinity_id = 4

Is there an efficient way to make dynamic foreign keys such as this using Ecto, or are we better off with a table with nullable Thing columns such as:

user_id
affinity_id
building_id
tool_id
location_id
person_id
activity_id

where a possible record could be:

user_id = 1
affinity_id = 4
building_id = null
tool_id = 10
book_id = null
person_id = null
activity_id = null

We’re opting to go with a dynamic many-to-many table such as this one rather than creating a bunch of different user_x_and_affinity many-to-many tables so we don’t have to do lots of joins/subqueries when calculating this user data altogether, but I am very open to other viewpoints :stuck_out_tongue:

1 Like

You could use an additional intermediate table as a “handle” to the many kinds of things:

user_things_and_affinities:
user_id
thing_id
affinity_id

things:
type
building_id
tool_id
location_id
person_id
activity_id
etc

This way, for M users and N things there are only N rows with mostly NULL instead of O(M*N).

You could also copy some data to the things table from each kind of record - for instance, a “display name” so that UI code doesn’t need to load the associated record just for a name.

2 Likes