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.

1 Like

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.

3 Likes

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.

7 Likes

@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.

2 Likes

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.

1 Like

@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.

1 Like

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