Ecto - Array of References?

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.

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