That’s the recommended approach for Ecto
https://media.pragprog.com/titles/wmecto/code/priv/repo/migrations/20180620125250_add_notes_tables.exs
https://media.pragprog.com/titles/wmecto/code/lib/music_db/note.ex

So something like

[Document] <->  [Content]  <-> [Video] | [Image] | [Markup]
   id      <-- document_id
                
                video_id   -->   id
                image_id   -->             id
               markup_id   -->                       id

In the above Video, Image, and Markup can be referenced by multiple documents. If they strictly belong to one document maybe this would be more appropriate.

[Document] <->  [Content]  <->  [Video] |  [Image] | [Markup]
   id      <-- document_id
                
                   id      <-- content_id
                   id      <--           content_id
                   id      <--                     content_id

Given

I don’t think it makes sense to try to normalize Video, Image, Markup in to some normalized, uniform structure - so sticking with an association with Content probably makes sense.

I think that unless I find any issues after looking into the required queries further; this is the solution I’ll go with. I’ll also revisit my recursive structure in the CrossFit app and see whether this would simplify it — I suspect it would.

Thanks for all your help @peerreynders & @OvermindDL1

I’ll be sure to post up a gist of this solution in full once I’ve built it so that it can help others.

Naively asking before thinking about it deeper – have you considered trying PostgreSQL’s table inheritance?

While PostgreSQL itself is an excellent database, and the PostgreSQL documentation is usually very good, too… in my experience the PostgreSQL Wiki just none of those things. In my experience it tends to be outdated, sometimes on the order of a decade or more, and the advice is too frequently subpar as compared to other resources… even resources like Stack Overflow. When I search for info and the Wiki comes up, I typically just ignore those results.

This isn’t to say that the article you’ve linked is wrong or bad; I haven’t read the article you linked and I agree that table inheritance is usually a bad idea unless you know exactly how it works and why the nuances of your use case make it the right answer (in practice a very rare thing, but some ideas are still out there: Performing ETL Using Inheritance in PostgreSQL).

I comment only to suggest that citing the Wiki as an genuinely authoritative resource is not something I’d recommend.

I deleted this topic after deciding that my answers were already scattered around the forum in the various polymorphism and inheritance topics. Here I am a week later, still failing to find what I feel is an adequate solution to a relatively simple problem.

In a relational database (or any other table based one for that matter) how would you model the above — where a document/page/record/whatever is composed of many different types of content?

For example, the record that I’d want to pull from the DB would look something like this after whatever joins, etc were done:

%Document{
  name: "Example",
  id: 1,
  content: [
    %Video{
      index: 0,
      url: "something.com/ajsha",
      description: "all about the video",
      author_id: 3,  (%User{})
    },
   %Title{
     index: 1,
     text: "My title"
   },
   %Image{
     index: 2,
     url: "something.com/akshgdakj.jpg",
     caption: "Blah blah",
     uploader_id: 5, (%User{})
     photographer_id: 28, (%User{})
    },
    [...]
  ]
}

Thinking about it, this would have been equally relevant when I was putting together the schema for my CrossFit app recently. A solution to this could help there to.

Others have referred to this problem as a polymorphic has_many as opposed to the polymorphic belongs_to that is often talked about (and has solutions in the Ecto docs). That topic has been my main point of reference, although I’m not wedded to the idea of an Animal => Cat/Dog/Whatever relation as the original poster.

I’ve scratched my head, got my Google-fu on, read some books and even explored other means of storing the data and still can’t really work it out.

Help me!