For the following hypothetical use case, I'm trying to understand why it may be desirable to have a pivot table instead of an alternative solution (outlined below).
Let’s say that a movie has many actors and that an actor can belong to more than one movie.
As outlined in this lesson (using Elixir's Ecto library), the "standard" solution recommends using a movies_actors pivot table, and both the movies and actors tables reference this movies_actors table.
Instead, could we achieve the same result by having the concept of a list of ids?
actor belongs to one or more movies by having the actors table include a movie_ids field (which is a list)movie has many actors by having the movies table include a actor_ids field (which is a list)Is one solution preferable? Why?
I think you should not use the "alternative solution" of storing arrays of referenced ids to model an many-to-many relationship. It seems simpler at first glance, but it will hurt you later.
You should write a simple test case for both scenarios and create test tables with a realistic number of entries and relationships (it doesn't matter if the data are artificial and repeating). Then try to write a join between the two tables. You will find that with the "alternative solution", the query looks much more complicated (at best, it will involve strange operators like @>) and doesn't perform as well (you can only get a nested loop join).
There is a good reason to keep data in the first normal form – it is better adapted to the way relational databases process data.
Of course this "normal form" stuff has to be taken with a grain of salt: it is fine to use an array to store data, as long as you don't use individual array entries in your query processing. But by joining over array elements you certainly step over that line.