SQL structure for many, different child entities

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!

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

Truthfully I think I checked out Postgres’ table inheritance at some point but skipped over it after finding a reason it wouldn’t work/be ideal. I think it was something to do with Ecto not supporting it, but I’ve got no aversions to escaping into SQL if needs be.

I’ll take a proper look at it again and see whether it fits the bill or not.

A lot of the recent talk around here abut DB-less applications or making the DB an implementation detail has piqued my curiosity though. I’ve realised that when modelled without Ecto - just using Elixir structs - the problem is simpler and a lot of the code I’ve written thats only there to try and contort my data into tables just falls away.

I’m either still not “thinking in tables” or I genuinely have a use case where a different storage solution would be better (same goes for that CrossFit app). It’s kind of hard to know without more experience.

Yeah this seems like a classic use for postgresql inheritence. You can use foreign table links too. And yeah, Ecto could use some help in supporting it but it still ‘mostly’ works. ^.^;

The structs in structs method you mention would be like traditional foreign linked tables though, which is perfectly fine.

Of the two (inheritance and foreign table links) which would you opt for, and are there any “gotchas” that I should keep an eye out for?

(Trying to make up for my lack of experience here)

Honestly I’d just use foreign linked tables, easy to expand, easy to conditionally join (left_join’s), etc…

So you’d have a intermediate table that would hold the document_id, index and then a column for each content type (video_id, image_id, title_id, markdown_id`, etc) where only one can be set at a time - a.k.a. an exclusive arc?

Would having an additional type column on this intermediate help with joining on the correct column, or would there be some SQL’y way to join on the non-null column that I don’t know about?

Sorry for all the questions. I just want to properly understand what I’m doing and why, so that I can apply it to other scenarios.

Well I’d do it the other way, I’d have a document_or_whatever table with rows that have columns that everything shares, then other tables like video, image, etc… of who’s primary keys is not an auto-incrementing integer but is rather a foreign table link back to the document_or_whatever. This will be highly efficient and infinitely expandable.

You can actually create a constraint that enforces that when one is created then no others exist, this constraint can be shared by them all so only one can exist at a time easily as well.

So the two rows across the two tables that make up a complete record in effect have the same primary key? In the main document_or_whatever table (probably go with content) I have a column called id that matches up to a column an id column (the primary) in the video, image, etc tables?

So when querying the data, I’d find the right row(s) in the `contents table and then left join on all the possible other tables where the primary keys match?

I think I understand…

Yep yep, the main table defines the ID to use and everything else’s ‘primary key’ just matches it with an enforced foreign link.

Yep yep! You can even extend such joins dynamically if your system becomes pluggable later or so too.