抽象实体Campaign及派生实体的数据库设计方案咨询

Hey there! Let's walk through the most suitable relational database design approaches for your Campaign inheritance setup, since you're exploring relational options. Your core pain points here are changing shared fields across all campaign types and evolving unique fields for each concrete campaign (FooCampaign, BarCampaign, etc.), so we’ll focus on solutions that balance maintainability and flexibility.

1. Single Table Inheritance (STI) #

This is the simplest approach for inheritance in relational databases:

• How it works: Create one single campaigns table that includes all shared fields plus every unique field from every concrete campaign type. Add a campaign_type column (e.g., 'foo', 'bar') to identify which concrete type each row represents.
• Pros:
  • No joins needed for queries—super fast for fetching full campaign data.
  • Changing shared fields only requires modifying one table, which is low-effort.
• Cons:
  • You’ll end up with lots of NULL values (e.g., BarCampaign rows will have NULLs for all FooCampaign-specific fields), which can feel messy and waste storage.
  • As you add more concrete campaign types, the table will grow wider and harder to manage.
  • Risk of field name conflicts if two concrete types accidentally use the same column name for different purposes.
• Best for: Scenarios where concrete campaigns have minimal unique fields, or you prioritize query performance over strict data structure cleanliness.

2. Class Table Inheritance (CTI) #

This is the most normalized approach for inheritance:

• How it works:
  • Create a base campaigns table that holds only the shared fields (id, created_at, shared_name, etc.).
  • Create separate tables for each concrete type: foo_campaigns, bar_campaigns, etc. Each of these tables has a foreign key to campaigns.id, plus their own unique fields.
• Pros:
  • No redundant NULLs or duplicate data—data is neatly partitioned.
  • Changing shared fields only affects the base campaigns table; modifying a concrete type’s fields only touches its specific table.
  • Enforces data integrity (you can’t have a FooCampaign without a corresponding base Campaign row).
• Cons:
  • Fetching a full concrete campaign requires joining the base table with the specific child table, which can slow down complex queries.
  • Adding a new concrete campaign type means creating a new table, which adds a small amount of overhead.
• Best for: Most scenarios where you need clean, maintainable data structure—especially if concrete campaigns have distinct, evolving unique fields. To simplify queries, you can create database views that pre-join the base and child tables for common access patterns.

3. Concrete Table Inheritance #

This approach treats each concrete campaign as entirely separate:

• How it works: Create a separate table for each concrete type (foo_campaigns, bar_campaigns) that includes both shared fields and unique fields for that type. No base campaigns table exists.
• Pros:
  • Querying a single campaign type is lightning fast—no joins needed.
  • Concrete types are fully independent; changes to one don’t affect others.
• Cons:
  • Major maintenance headache: If you need to add/remove a shared field, you have to modify every concrete table. This gets worse as you add more campaign types.
  • Querying all campaigns together requires messy UNION operations across all tables.
• Best for: Rare cases where concrete campaigns have almost no overlap in usage, and you never need to query all campaigns as a unified set.

Bonus: Hybrid Approach (CTI + Flexible Fields) #

If your concrete campaign fields change very frequently (e.g., adding new unique fields every few weeks), consider combining Class Table Inheritance with a flexible column type like PostgreSQL’s JSONB or MySQL’s JSON:

• How it works: Use the base campaigns table for shared fields, and for each child table (or even directly in the base table), add a custom_fields JSONB column to store unique, evolving fields as key-value pairs.
• Pros:
  • No need to alter table structures every time you add a new unique field—just update the JSON data.
  • Still maintains the clean separation of shared fields in the base table.
  • You can index specific keys in the JSONB column for fast queries on custom fields.
• Cons:
  • Less strict data validation (you’ll need to enforce field rules at the application level instead of the database).
  • Not ideal if you need complex queries across custom fields (though modern databases handle JSON queries better than ever).

Final Recommendation #

For your use case—where both shared and unique fields are likely to evolve over time—the Class Table Inheritance (CTI) + JSONB flexible fields hybrid is the sweet spot. It balances:

• Easy maintenance of shared fields (only one table to update)
• Flexibility for unique fields (no schema changes for new fields)
• Clean data structure that avoids redundancy

If you don’t need extreme flexibility for unique fields, plain CTI is a solid, battle-tested choice that keeps your database schema organized.

内容的提问来源于stack exchange，提问作者super.t