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.
