SQL will always return tabular data, no matter how hard one tries to think of it as nested or treelike.

So given a row with book details you add columns for person data using a join. But you only get enough columns for a single joined person. If you want to join another person you need to do another join, which will one more set of columns to the resulting data.

That’s why your preload doesn’t work as you expect. You’re joining either an author or an illustator and then telling ecto to use that single set of data for both relations.

While this is generally true I doubt there’s many cases if at all where joining distinct data as separate rows over joining as separate columns will yield a performance boost. That’s something I’d only start touching as one of the later options once a query in question was proven to be a hotspot. Foreign keys on primary keys of other tables are hella fast, because the primary key is the the primary way to look up information on that table. It’s afaik always indexed and basically the first thing any db engine will make sure is fast.