✅ Short Answer
- SINGLE_TABLE → best for simple hierarchies with similar subclasses, prioritizing read performance.
- JOINED → best for normalized schemas when subclasses have many unique fields → prioritizes schema design and data integrity.
- TABLE_PER_CLASS → best when subclasses are very different and rarely queried polymorphically → but should be used with caution.
🔎 Detailed Explanation
🔹 SINGLE_TABLE
✅ Choose when:
- You want fast reads (just one table, no joins or unions).
- Subclasses share most fields → minimizing unused (NULL) columns.
- Schema simplicity is more important than normalization.
❌ Avoid if: - Subclasses have many unique fields → leads to lots of NULLs.
🔹 JOINED
✅ Choose when:
- You want normalized tables → avoids many NULL columns.
- Subclasses have significant differences in their fields.
- You care about data integrity and clean schema design.
- You don’t mind slightly slower reads due to JOINs.
❌ Avoid if: - You need maximum read performance → JOINs introduce overhead.
🔹 TABLE_PER_CLASS
✅ Choose when:
- Subclasses are completely unrelated in their fields → e.g., base class used only for a common interface (like abstract base).
- Polymorphic queries (across subclasses) are rare → since they need slow UNIONs.
❌ Avoid if: - You frequently query across the entire hierarchy → UNION queries will kill performance.
- You need clean normalization → base class fields are duplicated in each table.
📊 Decision Table
| Scenario | Recommended Strategy |
|---|---|
| Fastest read performance | SINGLE_TABLE |
| Few subclass-specific fields | SINGLE_TABLE |
| Many subclass-specific fields | JOINED |
| Prioritize normalization | JOINED |
| Subclasses are very different, unrelated | TABLE_PER_CLASS |
| Rarely query the hierarchy polymorphically | TABLE_PER_CLASS |
📌 Key Takeaways
✅ Use SINGLE_TABLE for performance-critical apps with simple hierarchies.
✅ Use JOINED when you need normalized design and subclasses have many exclusive fields.
✅ Use TABLE_PER_CLASS only when subclasses are radically different and polymorphic queries are rare.