Design Question: How do you handle many-to-many relationships with additional fields

Hey everyone,

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
  use Ecto.Schema
  schema "actors" do
    many_to_many :movies Movie, join_through: "movies_actors"

defmodule Movie do
  use Ecto.Schema
  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
  use Ecto.Schema
  schema "roles" do
    field :character_name, :string
    belongs_to :movie
    belongs_to :actor 

… and then use has_many :roles in both the Actor and Movie schemas instead of the many_to_many.

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 through with has_many!

I actually do many_to_many and 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 many_to_many relationship.

If I want to look up a field in the join table, I just preload the join table and access the field.

1 Like

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.

1 Like