I am interested in your opinion on designing a system with many-to-many relationships in an SQL database using Ecto.
Say, we have a table of
actors and a table of
movies. We can easily create a many-to-many relationship using a
movies_actors table, so that each actor can be part of multiple movies and each movie can have multiple actors.
This would look something like this:
defmodule Actor do
schema "actors" do
many_to_many :movies Movie, join_through: "movies_actors"
defmodule Movie do
schema "movies" do
many_to_many :actors Actor, join_through: "movies_actors"
But what if we want to add a flag to the
movies_actors table to indicate the name of the character played by the actor in this movie?
The obvious solution seems to be to create a new module
Role like this:
defmodule Role do
schema "roles" do
field :character_name, :string
… and then use
has_many :roles in both the
Movie schemas instead of the
What do you think? Are there more elegant solutions to this?
If you have additional fields you should likely drop
many_to_many all together and go for
has many ..., through: ... and a proper schema for the intermediate table.
Oh, I totally missed that you could use
I actually do
has_many at the same time for one table.
An example would be the actor schema will have a
many_to_many association with movies and also have a
has_many to the join table between actor schema and movie schema. The movie table will have a
many_to_many with actor schema and a
has_many with the join table between actor and movie. The join table will have two
has_many association, one for actor schema and the other for movie schema.
It works out pretty great. The
many_to_many relationship gives me a lot of flexibility so most of my tables are
If I want to look up a field in the join table, I just preload the join table and access the field.
Depending on how complex and important your relations between entities get and how much the questions revolve around these relationships you might actually want to consider looking at a graph-based solution.
I’m saying that because I have been working on something similar the last few weeks and really struggled with PostgreSQL, even going as far as having to do recursive joins.
As it turned out the problem was inherently graphy and everything fell into place as soon as I tried to model it with a graph db (Neo4j to be specific).
Interestingly they actually use the movies/actors/directors example a lot in their documentation, which is why I feel the need to mention it. If you want to learn more I can suggest this talk/training which I felt was a perfect introduction into the topic.