Multiple Table Inheritance Patterns in Ruby on Rails with PostgreSQL

Multiple Table Inheritance (MTI) is a database design pattern where related classes are stored across multiple tables, with each subclass having its own table that references a common base table. This approach provides better data normalization and type safety compared to Single Table Inheritance (STI) but comes with increased complexity.

What is Multiple Table Inheritance?

MTI splits inheritance hierarchies across multiple tables, where:

• A base table contains common attributes
• Each subclass has its own table with specific attributes
• Subclass tables reference the base table through foreign keys
• Rails models use associations to connect the tables

This pattern is also known as "Class Table Inheritance" in database design literature.

When to Use MTI

MTI is ideal when:

• Subclasses have significantly different attributes (less than 60% overlap)
• You need strong data consistency and type safety
• NULL values in STI would be excessive
• You want to enforce referential integrity
• Performance of specific subclass queries is critical
• You need to add indexes specific to each subclass

MTI Implementation Patterns

Pattern 1: Polymorphic Associations (Rails Way)

This is the most Rails-idiomatic approach using polymorphic associations.

Step 1: Create the Base Model

rails generate model Profile name:string email:string profileable:references{polymorphic}

Step 2: Create Subclass Models

rails generate model CustomerProfile phone:string address:text loyalty_points:integer
rails generate model EmployeeProfile employee_id:string department:string salary:decimal
rails generate model AdminProfile access_level:string last_login:datetime permissions:text

Step 3: Set Up Migrations

# db/migrate/create_profiles.rb
class CreateProfiles < ActiveRecord::Migration[7.0]
  def change
    create_table :profiles do |t|
      t.string :name, null: false
      t.string :email, null: false
      t.references :profileable, polymorphic: true, null: false
      t.timestamps
    end

    add_index :profiles, [:profileable_type, :profileable_id]
    add_index :profiles, :email, unique: true
  end
end

# db/migrate/create_customer_profiles.rb
class CreateCustomerProfiles < ActiveRecord::Migration[7.0]
  def change
    create_table :customer_profiles do |t|
      t.string :phone
      t.text :address
      t.integer :loyalty_points, default: 0
      t.timestamps
    end

    add_index :customer_profiles, :loyalty_points
  end
end

# db/migrate/create_employee_profiles.rb
class CreateEmployeeProfiles < ActiveRecord::Migration[7.0]
  def change
    create_table :employee_profiles do |t|
      t.string :employee_id, null: false
      t.string :department
      t.decimal :salary, precision: 10, scale: 2
      t.timestamps
    end

    add_index :employee_profiles, :employee_id, unique: true
    add_index :employee_profiles, :department
  end
end

Step 4: Set Up Models

# app/models/profile.rb
class Profile < ApplicationRecord
  belongs_to :profileable, polymorphic: true

  validates :name, presence: true
  validates :email, presence: true, uniqueness: true

  # Delegate methods to profileable
  delegate :specific_info, :dashboard_path, to: :profileable

  def full_name
    name.titleize
  end

  def profile_type
    profileable_type.underscore.humanize
  end
end

# app/models/customer_profile.rb
class CustomerProfile < ApplicationRecord
  has_one :profile, as: :profileable, dependent: :destroy
  has_many :orders, dependent: :destroy

  validates :phone, format: { with: /\A[\d\-\s\+\(\)]+\z/ }
  validates :loyalty_points, numericality: { greater_than_or_equal_to: 0 }

  def specific_info
    "Loyalty Points: #{loyalty_points}"
  end

  def dashboard_path
    '/customer/dashboard'
  end

  def add_loyalty_points(points)
    increment(:loyalty_points, points)
    save
  end
end

# app/models/employee_profile.rb
class EmployeeProfile < ApplicationRecord
  has_one :profile, as: :profileable, dependent: :destroy

  validates :employee_id, presence: true, uniqueness: true
  validates :department, presence: true
  validates :salary, numericality: { greater_than: 0 }

  DEPARTMENTS = %w[Engineering Sales Marketing HR Finance].freeze

  validates :department, inclusion: { in: DEPARTMENTS }

  def specific_info
    "Department: #{department}, Salary: $#{salary}"
  end

  def dashboard_path
    '/employee/dashboard'
  end

  def annual_salary
    salary * 12
  end
end

# app/models/admin_profile.rb
class AdminProfile < ApplicationRecord
  has_one :profile, as: :profileable, dependent: :destroy

  validates :access_level, inclusion: { in: %w[super_admin admin moderator] }

  def specific_info
    "Access Level: #{access_level}"
  end

  def dashboard_path
    '/admin/dashboard'
  end

  def permissions_list
    permissions.split(',').map(&:strip)
  end

  def can_manage_users?
    access_level.in?(%w[super_admin admin])
  end
end

Step 5: Factory Pattern for Creation

# app/services/profile_factory.rb
class ProfileFactory
  def self.create_customer(attributes)
    ActiveRecord::Base.transaction do
      customer = CustomerProfile.create!(
        phone: attributes[:phone],
        address: attributes[:address],
        loyalty_points: attributes[:loyalty_points] || 0
      )

      profile = Profile.create!(
        name: attributes[:name],
        email: attributes[:email],
        profileable: customer
      )

      profile
    end
  end

  def self.create_employee(attributes)
    ActiveRecord::Base.transaction do
      employee = EmployeeProfile.create!(
        employee_id: attributes[:employee_id],
        department: attributes[:department],
        salary: attributes[:salary]
      )

      profile = Profile.create!(
        name: attributes[:name],
        email: attributes[:email],
        profileable: employee
      )

      profile
    end
  end

  def self.create_admin(attributes)
    ActiveRecord::Base.transaction do
      admin = AdminProfile.create!(
        access_level: attributes[:access_level],
        permissions: attributes[:permissions]
      )

      profile = Profile.create!(
        name: attributes[:name],
        email: attributes[:email],
        profileable: admin
      )

      profile
    end
  end
end

Pattern 2: Shared Primary Key (Foreign Key Inheritance)

This pattern uses the same primary key across all tables.

Step 1: Create Base Table

# db/migrate/create_users.rb
class CreateUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.string :name, null: false
      t.string :email, null: false
      t.string :type, null: false
      t.timestamps
    end

    add_index :users, :email, unique: true
    add_index :users, :type
  end
end

Step 2: Create Subclass Tables

# db/migrate/create_customers.rb
class CreateCustomers < ActiveRecord::Migration[7.0]
  def change
    create_table :customers, id: false do |t|
      t.references :user, foreign_key: true, primary_key: true
      t.string :phone
      t.text :address
      t.integer :loyalty_points, default: 0
      t.timestamps
    end

    add_index :customers, :loyalty_points
  end
end

# db/migrate/create_employees.rb
class CreateEmployees < ActiveRecord::Migration[7.0]
  def change
    create_table :employees, id: false do |t|
      t.references :user, foreign_key: true, primary_key: true
      t.string :employee_id, null: false
      t.string :department
      t.decimal :salary, precision: 10, scale: 2
      t.timestamps
    end

    add_index :employees, :employee_id, unique: true
    add_index :employees, :department
  end
end

Step 3: Set Up Models

# app/models/user.rb
class User < ApplicationRecord
  has_one :customer, dependent: :destroy
  has_one :employee, dependent: :destroy
  has_one :admin, dependent: :destroy

  validates :name, presence: true
  validates :email, presence: true, uniqueness: true
  validates :type, inclusion: { in: %w[Customer Employee Admin] }

  def profile
    case type
    when 'Customer' then customer
    when 'Employee' then employee
    when 'Admin' then admin
    end
  end

  def specific_info
    profile&.specific_info
  end
end

# app/models/customer.rb
class Customer < ApplicationRecord
  belongs_to :user
  has_many :orders, dependent: :destroy

  validates :phone, format: { with: /\A[\d\-\s\+\(\)]+\z/ }
  validates :loyalty_points, numericality: { greater_than_or_equal_to: 0 }

  delegate :name, :email, to: :user

  def specific_info
    "Loyalty Points: #{loyalty_points}"
  end

  def dashboard_path
    '/customer/dashboard'
  end
end

Pattern 3: PostgreSQL Table Inheritance

PostgreSQL supports native table inheritance, which Rails can leverage.

Step 1: Create Base Table with Inheritance

-- This needs to be done via raw SQL in migration
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL,
  email VARCHAR NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);

CREATE TABLE customers (
  phone VARCHAR,
  address TEXT,
  loyalty_points INTEGER DEFAULT 0
) INHERITS (users);

CREATE TABLE employees (
  employee_id VARCHAR NOT NULL UNIQUE,
  department VARCHAR,
  salary DECIMAL(10,2)
) INHERITS (users);

Step 2: Rails Migration

class CreateInheritedTables < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name VARCHAR NOT NULL,
        email VARCHAR NOT NULL UNIQUE,
        created_at TIMESTAMP NOT NULL,
        updated_at TIMESTAMP NOT NULL
      );

      CREATE TABLE customers (
        phone VARCHAR,
        address TEXT,
        loyalty_points INTEGER DEFAULT 0
      ) INHERITS (users);

      CREATE TABLE employees (
        employee_id VARCHAR NOT NULL UNIQUE,
        department VARCHAR,
        salary DECIMAL(10,2)
      ) INHERITS (users);
    SQL
  end

  def down
    drop_table :customers
    drop_table :employees
    drop_table :users
  end
end

Step 3: Configure Models

# app/models/user.rb
class User < ApplicationRecord
  self.table_name = 'users'

  def self.all_including_inherited
    find_by_sql("SELECT * FROM users")
  end
end

# app/models/customer.rb
class Customer < ApplicationRecord
  self.table_name = 'customers'

  def self.with_base_data
    find_by_sql("SELECT * FROM customers")
  end
end

# app/models/employee.rb
class Employee < ApplicationRecord
  self.table_name = 'employees'

  def self.with_base_data
    find_by_sql("SELECT * FROM employees")
  end
end

Advanced MTI Patterns

Pattern 4: Concern-Based Approach

Create shared behavior using concerns:

# app/models/concerns/profile_behavior.rb
module ProfileBehavior
  extend ActiveSupport::Concern

  included do
    validates :name, presence: true
    validates :email, presence: true, uniqueness: true

    scope :by_name, ->(name) { where(name: name) }
    scope :recent, -> { where('created_at > ?', 1.week.ago) }
  end

  def full_name
    name.titleize
  end

  def initials
    name.split.map(&:first).join.upcase
  end
end

# app/models/customer_profile.rb
class CustomerProfile < ApplicationRecord
  include ProfileBehavior

  # Customer-specific behavior
end

Pattern 5: View-Based Queries

Use PostgreSQL views for complex queries:

# db/migrate/create_unified_profile_view.rb
class CreateUnifiedProfileView < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      CREATE VIEW unified_profiles AS
      SELECT
        p.id,
        p.name,
        p.email,
        p.profileable_type as type,
        p.profileable_id,
        p.created_at,
        p.updated_at,
        CASE
          WHEN p.profileable_type = 'CustomerProfile' THEN cp.phone
          WHEN p.profileable_type = 'EmployeeProfile' THEN ep.employee_id
          ELSE NULL
        END as identifier
      FROM profiles p
      LEFT JOIN customer_profiles cp ON p.profileable_id = cp.id AND p.profileable_type = 'CustomerProfile'
      LEFT JOIN employee_profiles ep ON p.profileable_id = ep.id AND p.profileable_type = 'EmployeeProfile'
      LEFT JOIN admin_profiles ap ON p.profileable_id = ap.id AND p.profileable_type = 'AdminProfile';
    SQL
  end

  def down
    execute "DROP VIEW unified_profiles"
  end
end

# app/models/unified_profile.rb
class UnifiedProfile < ApplicationRecord
  self.table_name = 'unified_profiles'

  def readonly?
    true
  end
end

Querying MTI Models

Basic Queries

# Polymorphic approach
profiles = Profile.includes(:profileable)
customers = Profile.joins(:profileable).where(profileable_type: 'CustomerProfile')

# Shared primary key approach
all_users = User.includes(:customer, :employee, :admin)
customers = User.joins(:customer).where(type: 'Customer')

Complex Queries

# Find all profiles with specific attributes
high_loyalty_customers = Profile.joins(:profileable)
  .where(profileable_type: 'CustomerProfile')
  .where('customer_profiles.loyalty_points > ?', 1000)

# Cross-type queries
recent_profiles = Profile.includes(:profileable)
  .where('created_at > ?', 1.month.ago)
  .map { |p| { name: p.name, type: p.profileable_type, info: p.specific_info } }

Performance Optimization

# Use specific subclass queries when possible
CustomerProfile.includes(:profile).where('profiles.created_at > ?', 1.week.ago)

# Batch loading for polymorphic associations
profiles = Profile.includes(:profileable).limit(100)
Profile.preload_polymorphic_association(profiles, :profileable)

Testing MTI Models

RSpec Examples

# spec/models/profile_spec.rb
RSpec.describe Profile, type: :model do
  describe "polymorphic associations" do
    it "works with customer profiles" do
      customer = create(:customer_profile)
      profile = create(:profile, profileable: customer)

      expect(profile.profileable).to eq(customer)
      expect(profile.profileable_type).to eq('CustomerProfile')
    end

    it "delegates methods correctly" do
      customer = create(:customer_profile, loyalty_points: 100)
      profile = create(:profile, profileable: customer)

      expect(profile.specific_info).to eq("Loyalty Points: 100")
    end
  end
end

Factory Setup

# spec/factories/profiles.rb
FactoryBot.define do
  factory :profile do
    name { "John Doe" }
    email { "john@example.com" }

    trait :with_customer do
      association :profileable, factory: :customer_profile
    end

    trait :with_employee do
      association :profileable, factory: :employee_profile
    end
  end

  factory :customer_profile do
    phone { "555-1234" }
    address { "123 Main St" }
    loyalty_points { 0 }
  end

  factory :employee_profile do
    employee_id { "EMP001" }
    department { "Engineering" }
    salary { 75000 }
  end
end

Performance Considerations

Indexing Strategies

# Polymorphic indexes
add_index :profiles, [:profileable_type, :profileable_id]
add_index :profiles, [:profileable_type, :email]

# Subclass-specific indexes
add_index :customer_profiles, :loyalty_points
add_index :employee_profiles, [:department, :salary]

Query Optimization

# Use eager loading
Profile.includes(:profileable).limit(10)

# Use joins for filtering
Profile.joins(:profileable).where(profileable_type: 'CustomerProfile')

# Use specific model queries
CustomerProfile.includes(:profile).where('profiles.created_at > ?', 1.week.ago)

Comparison: MTI vs STI

| Aspect | MTI | STI | | --------------------- | --------------- | ------------------- | | Data Normalization | Excellent | Poor | | Query Performance | Good (specific) | Excellent (general) | | Storage Efficiency | Excellent | Poor (many NULLs) | | Complexity | High | Low | | Flexibility | High | Medium | | Referential Integrity | Excellent | Good |

Best Practices

1. Choose the Right Pattern

• Polymorphic: Most flexible, Rails-idiomatic
• Shared PK: Better performance, more complex
• PostgreSQL Inheritance: Database-level benefits, Rails complexity

2. Optimize Queries

# Bad: N+1 queries
profiles = Profile.all
profiles.each { |p| puts p.profileable.specific_info }

# Good: Eager loading
profiles = Profile.includes(:profileable)
profiles.each { |p| puts p.profileable.specific_info }

3. Use Transactions

def create_customer_profile(attributes)
  ActiveRecord::Base.transaction do
    customer = CustomerProfile.create!(customer_attributes)
    Profile.create!(profile_attributes.merge(profileable: customer))
  end
end

4. Handle Associations Carefully

# Good: Proper cleanup
class CustomerProfile < ApplicationRecord
  has_one :profile, as: :profileable, dependent: :destroy
end

# Good: Cascade deletes
class Profile < ApplicationRecord
  belongs_to :profileable, polymorphic: true, dependent: :destroy
end

Common Pitfalls

1. Forgetting Indexes

Always index polymorphic columns and foreign keys.

2. N+1 Query Problems

Use includes for polymorphic associations.

3. Transaction Management

Wrap multi-table operations in transactions.

4. Inconsistent Data

Implement proper validations and constraints.

Conclusion

Multiple Table Inheritance provides excellent data normalization and type safety at the cost of increased complexity. The choice between different MTI patterns depends on your specific requirements:

• Use Polymorphic Associations for maximum Rails compatibility
• Use Shared Primary Key for better performance with increased complexity
• Use PostgreSQL Inheritance for database-level benefits

MTI is ideal when subclasses have significantly different attributes and you need strong data consistency. While more complex than STI, it provides better long-term maintainability for diverse inheritance hierarchies.

The key to successful MTI implementation is careful planning of your table structure, proper indexing, and consistent use of transactions for multi-table operations.