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