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