Hey Elixir community!

I am trying to conceive an application with many users and many organizations. Each user may be a member of multiple orgs, and each org may have multiple users. I’ve been reading up on authorization and relationships in the DB and contexts. @chrismccord’s article was very helpful in illustrating a couple of approaches to authorization.

This might be too simplistic, but I imagined a column (users.roles) with an array of maps similar to this:

[{org_id: 1234, role_title: :owner}, {org_id: 101, role_title: :contributor}]

where org_id would reference an orgs table, and role_title would reference a roles table.

Is it possible to set relations on an {:array, :map} column? I feel like the answer is no, but this is the first solution that comes to mind. I’m having trouble following the official schema documentation, so I really appreciate any guidance.

Thank you.