SQL structure for many, different child entities

Huh? No?

You’d just have a schema for each link, mapping to the tables directly most easily.

Honestly I rarely if ever do that, I use schemas to just get what I want and use a query to build it up linking it all within the query itself, but I’m a lot more comfortable in SQL than most. ^.^;

You’re correct. As I’ve read more about this, I’ve learnt that polymorphism isn’t the right term for what I’m after. It applies to the inverse of what I’m after, but both requirements bring up a lot of the same discussion about inheritance (single table, concrete, etc) and also polymorphism.

That’s the conclusion I’ve been circling around, regardless of whether I use foreign linked tables or inheritance. I’d use a column per type for the ID (exclusive arc) rather than a single column for ID that uses the type column to determine which table to lookup (would compromise foreign keys).

In this case, the index that would be on the Content table and all the fields in whatever schema it links to. In the case of the CrossFit app, there would be lots of joins happening from each schema (off to many different other tables, depending on schema).

That’s what I’m after.

I think this is the issue here, I’ve got enough SQL knowledge to do the basics, but anything more complex is all greek to me.

I think in light of this conversation, I’ll go away and map out exactly what data I need to be storing and importantly how I’ll be querying / joining on it. That should give me a better idea of how best to approach the problem.

At this stage I think I understand the various solutions, I just have to pick the most appropriate one.

1 Like

That’s the recommended approach for Ecto

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


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.