The reason for this is that there’s no “the best” solution to this problem in SQL in general. There are several options, each with different trade-offs that @ndac_todoroki beautifully outlined. The best solution often depends not only on the data itself but also on the particular usage patterns.
This means there won’t be a single definitive answer to the question of “how to do this with Ecto”, since there’s no single definitive answer to the question of “how to do this with SQL”.
