How to handle conditional associations in ecto?

Phoenix Forum Questions / Help
1

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!

Thanks.

2

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)

   timestamps()
end

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

3

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

1 Like
4

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?

5

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.

2 Likes
6

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
7

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: