Ecto - Array of References?



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.

Hello Elixir World (Introductions thread)

You might want to start looking at multi-tenancy using Triplex. You could keep the users table in it’s own schema like public.users and store the tenants they belong to there etc.

Try a search for Triplex and should find a couple of threads here on the forum with more info on the subject.


I would advise against keeping references in array or json columns - the reason is that it makes it very easy to get stale data and there’s no way of enforcing foreign key constraints on a data in a “compound” column. I’d suggest using a join table and has_many through.


@l00ker @michalmuskala Thank you both for the suggestions. I’ll start reading up on has_many through first, and see if I can keep my dependencies minimal.


I’ll also add a link to this episode of Full Stack Radio, which helped me understand multi-tenancy and considerations. It focuses mostly on database design instead of language-specific solutions.


@michalmuskala Is there a reason why you’d suggest has_many through vs a many_to_many? I recently went down this route and ended up using a many_to_many relationship. I suppose the biggest difference is being able to have metadata on the join?


many_to_many is all about hiding the join table. In this use case, as far as I understand, a precise management of data in the join table is required and direct access to the other table (roles) is primarily for reading - that’s the use case of has_many through:.

In general, though, I personally find many_to_many not that useful and almost always go for has_many through. It gives me more control and with Ecto.Multi is not that much more code to do inserts and similar manually.

Why primary key does not autogenerate when doing many to many assoc?

This right here. It’s surprising what you end up adding to a join table over time.