✅ 1) SINGLE_TABLE
Pros:
✅ Fastest read performance → only one table, no joins or unions needed.
✅ Simpler to query → easy polymorphic queries.
✅ Simple schema → easier to manage in simple hierarchies.
Cons:
❌ Many nullable columns → wasted space when subclasses have unique fields.
❌ Poor schema normalization → not ideal if you care about database design.
❌ Adding subclass-specific fields increases table width → can hurt maintainability.
✅ 2) JOINED
Pros:
✅ Normalized schema → base and subclasses in separate tables → avoids nulls.
✅ Efficient storage for hierarchies with many subclass-specific fields.
✅ Cleaner data model → maps well to relational database best practices.
Cons:
❌ Slower reads → requires JOINs at runtime.
❌ More complex queries → can impact performance if you query large hierarchies frequently.
❌ Schema has more tables → increases database complexity.
✅ 3) TABLE_PER_CLASS
Pros:
✅ No null columns → each table has only relevant fields.
✅ Good when subclasses are radically different and rarely queried together.
✅ Simple table structure for each concrete class.
Cons:
❌ Data duplication → base fields repeated in every subclass table → harder to maintain.
❌ Polymorphic queries are slow and complex → require UNIONs across tables.
❌ Schema changes to the base class → need to modify multiple tables.
📊 Quick Comparison Table
| Feature | SINGLE_TABLE | JOINED | TABLE_PER_CLASS |
|---|---|---|---|
| Speed | Fastest (1 table) | Medium (joins) | Slowest (unions) |
| Schema | Denormalized, many nulls | Normalized, fewer nulls | Denormalized, duplicated |
| Polymorphic queries | Fast and simple | Medium (joins) | Slow (unions) |
| Storage efficiency | Poor with many subclasses | Good | Poor |
| Complexity | Simplest | Moderate | High |
📌 Key Takeaways
✅ SINGLE_TABLE: choose for performance + simple hierarchy → but avoid if you expect many subclass-specific fields.
✅ JOINED: best for normalized schema → ideal when subclass differences are significant.
✅ TABLE_PER_CLASS: use only if subclasses are rarely queried together → be wary of duplicated fields and UNIONs.