SQL structure for many, different child entities

[OvermindDL1]

@jdumont

But when loading all the media records for a document, I’d have a Media struct for each with a nested Image, Video, whatever struct.

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. ^.^;

[jdumont]

@peerreynders

Focusing on this statement in particular I’m not convinced you are dealing with a polymorphic entity in your domain.

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.

@peerreynders

so it may be enough to have a Content table which identifies (document and) type (inferring a distinct table) and a key into that table.

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).

@peerreynders

How much information do you need to grab on this query?

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).

@OvermindDL1

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

That’s what I’m after.

@OvermindDL1

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. ^.^;

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.

[peerreynders]

@jdumont

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).

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

---

@jdumont

all the fields in whatever schema it links to.

Given

@jdumont

where each has a different structure and fields,

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.

[jdumont]

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.