How to handle conditional associations in ecto?

Hi there, I have a situation like the following:

  • A Workspace has many Projects.
  • A Workspace has many members (Users)
  • A User can be in multiple Workspaces
  • A Project has many contributors (Users) who MUST be Workspace members

How would you organise this in Postgres + ecto?

Two options I’ve considered:

  1. many_to_many workspaces_users table + projects_users table and enforce the member constraint at insertion
  2. use a many_to_many table between projects and workspaces_users. i.e. projects_workspaces_users, this will enforce the member constraint but feels like it will make the code more complex

Or maybe there are better ways? Let me know!


There’s also another solution I’ve thought of - what if I add an extra foreign key to the projects_users table? I.e.

create table(:projects_users) do
   add :project_id, references(:projects, on_delete: :delete_all)
   add :user_id, references(:users, on_delete: :delete_all)
   add :workspace_user_id, references(:workspaces_users, on_delete: :delete_all)


This feels like it would keep the code relatively simple, whilst enforcing the workspace membership constraint.

Maybe add primary key to workplace_users, create a WorkplaceMembership schema to model it, and associate that with projects?

1 Like

Thanks! Yes, that’s option 2 in my first post. I guess I was put off because it feels complicated and will involve frequently mapping from the membership table to the user, but maybe it’s the best way?

Personally I depend on DB constraints when convenient, or when absolutely critical, it doesn’t really seem like either is true here, so I would probably just have separate associations and enforce the validation with app logic.


Something to ponder: is this always true?

What about a user who contributes to a project but then tries to leave? Do their contributions disappear? Are they unable to leave because of foreign-key constraints?

1 Like

I think you’re both right:

  1. This feels unnecessarily complex and can be solved using app logic
  2. This constraint might not always be true in future (e.g. guest users etc), so a more flexible approach might be best

Will stick with app logic for now. Appreciate the replies. :heart: