Loading learning content...
Table-Per-Type (TPT), also known as Class Table Inheritance or the Multiple Table Approach, represents the most normalized strategy for mapping specialization hierarchies. The fundamental principle: create a separate table for the supertype and each subtype, connected through foreign key relationships.
Unlike STI's single-table approach, TPT embraces the relational model's normalization principles. Each table contains only attributes relevant to its entity type—no NULLs, no wasted columns, clean separation of concerns.
The Central Trade-off:
TPT trades query simplicity for data integrity and storage efficiency. Queries spanning the hierarchy require JOINs, but you gain precise schema control, foreign key enforcement for subtype relationships, and natural handling of overlapping hierarchies.
This is the approach favored by database purists and required when data integrity cannot be compromised.
By the end of this page, you will master TPT implementation including table design, primary/foreign key patterns, join strategies, constraint enforcement at the database level, performance optimization, and handling of deep inheritance hierarchies. You'll understand exactly when TPT is the superior choice.
TPT implementation follows a systematic pattern: supertype table plus one table per subtype, all linked by shared primary keys.
Design Principles:
Let's build the complete Person hierarchy using TPT:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109
-- ═══════════════════════════════════════════════════════════════-- TABLE-PER-TYPE (TPT) IMPLEMENTATION-- Complete university Person hierarchy-- ═══════════════════════════════════════════════════════════════ -- ───────────────────────────────────────────────────────────────-- SUPERTYPE TABLE: Contains all shared attributes-- ───────────────────────────────────────────────────────────────CREATE TABLE person ( -- Primary key - shared across all subtypes person_id SERIAL PRIMARY KEY, -- Optional discriminator (not required but useful) -- Helps avoid joining all subtype tables to determine type person_type VARCHAR(20), -- Shared attributes - common to ALL persons name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, date_of_birth DATE NOT NULL, address TEXT, phone VARCHAR(20), -- Audit columns created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Optional: Validate discriminator if used CONSTRAINT chk_person_type CHECK (person_type IN ('STUDENT', 'FACULTY', 'STAFF') OR person_type IS NULL)); -- ───────────────────────────────────────────────────────────────-- SUBTYPE TABLE: Student-- Contains ONLY student-specific attributes-- ───────────────────────────────────────────────────────────────CREATE TABLE student ( -- Primary key that is ALSO foreign key to person -- This is the characteristic TPT pattern person_id INTEGER PRIMARY KEY REFERENCES person(person_id) ON DELETE CASCADE, -- Student-specific attributes only student_number VARCHAR(20) NOT NULL UNIQUE, enrollment_date DATE NOT NULL, gpa DECIMAL(3,2) CHECK (gpa >= 0.0 AND gpa <= 4.0), major VARCHAR(50), minor VARCHAR(50), credits_earned INTEGER DEFAULT 0 CHECK (credits_earned >= 0), expected_grad DATE, academic_status VARCHAR(20) DEFAULT 'ACTIVE' CHECK (academic_status IN ('ACTIVE', 'PROBATION', 'SUSPENDED', 'GRADUATED'))); -- ───────────────────────────────────────────────────────────────-- SUBTYPE TABLE: Faculty-- ───────────────────────────────────────────────────────────────CREATE TABLE faculty ( person_id INTEGER PRIMARY KEY REFERENCES person(person_id) ON DELETE CASCADE, -- Faculty-specific attributes only faculty_id VARCHAR(20) NOT NULL UNIQUE, rank VARCHAR(30) NOT NULL CHECK (rank IN ('LECTURER', 'ASSISTANT_PROFESSOR', 'ASSOCIATE_PROFESSOR', 'PROFESSOR')), tenure_status VARCHAR(20) NOT NULL DEFAULT 'NON_TENURE_TRACK' CHECK (tenure_status IN ('TENURE_TRACK', 'TENURED', 'NON_TENURE_TRACK')), specialty VARCHAR(100), office_number VARCHAR(15), office_hours TEXT, hire_date DATE NOT NULL, contract_end DATE); -- ───────────────────────────────────────────────────────────────-- SUBTYPE TABLE: Staff-- ───────────────────────────────────────────────────────────────CREATE TABLE staff ( person_id INTEGER PRIMARY KEY REFERENCES person(person_id) ON DELETE CASCADE, -- Staff-specific attributes only staff_id VARCHAR(20) NOT NULL UNIQUE, position VARCHAR(50) NOT NULL, department VARCHAR(50) NOT NULL, hire_date DATE NOT NULL, salary_grade VARCHAR(10), supervisor_id INTEGER REFERENCES staff(person_id), work_schedule VARCHAR(20) DEFAULT 'FULL_TIME' CHECK (work_schedule IN ('FULL_TIME', 'PART_TIME', 'CONTRACTOR'))); -- ───────────────────────────────────────────────────────────────-- INDEXES for performance-- ───────────────────────────────────────────────────────────────CREATE INDEX idx_person_type ON person(person_type);CREATE INDEX idx_person_email ON person(email);CREATE INDEX idx_student_gpa ON student(gpa);CREATE INDEX idx_student_major ON student(major);CREATE INDEX idx_faculty_rank ON faculty(rank);CREATE INDEX idx_staff_department ON staff(department);Notice that subtype tables use person_id as BOTH their primary key AND foreign key to person. This ensures a 1:1 relationship between supertype and subtype rows, and allows efficient joining since both tables are indexed on the same key.
The primary complexity in TPT is constructing queries that span the hierarchy. Let's master the essential join patterns:
Pattern 1: Subtype-Specific Query (Simple)
Querying a single subtype is straightforward—just JOIN supertype and subtype:
123456789101112131415161718192021222324252627282930313233343536
-- Query all students with complete informationSELECT p.person_id, p.name, p.email, p.date_of_birth, s.student_number, s.gpa, s.major, s.credits_earnedFROM person pINNER JOIN student s ON p.person_id = s.person_idWHERE s.gpa > 3.5ORDER BY s.gpa DESC; -- Query faculty members with their detailsSELECT p.name, p.email, f.faculty_id, f.rank, f.tenure_status, f.specialtyFROM person pINNER JOIN faculty f ON p.person_id = f.person_idWHERE f.tenure_status = 'TENURED'; -- Query staff in specific departmentSELECT p.name, p.phone, st.position, st.hire_dateFROM person pINNER JOIN staff st ON p.person_id = st.person_idWHERE st.department = 'Information Technology';Pattern 2: Hierarchy-Wide Query (Complex)
Querying across all subtypes requires either LEFT JOINs or UNIONs:
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061
-- APPROACH 1: LEFT JOIN all subtypes-- Returns all people with subtype data where applicableSELECT p.person_id, p.name, p.email, p.person_type, -- Determine actual type based on which subtype table has data CASE WHEN s.person_id IS NOT NULL THEN 'STUDENT' WHEN f.person_id IS NOT NULL THEN 'FACULTY' WHEN st.person_id IS NOT NULL THEN 'STAFF' ELSE 'UNKNOWN' END AS actual_type, -- Include subtype-specific fields (will be NULL for other types) s.gpa, s.major, f.rank, f.tenure_status, st.position, st.departmentFROM person pLEFT JOIN student s ON p.person_id = s.person_idLEFT JOIN faculty f ON p.person_id = f.person_idLEFT JOIN staff st ON p.person_id = st.person_id; -- APPROACH 2: UNION ALL (often more efficient)-- Returns unified view with type-specific dataSELECT p.person_id, p.name, p.email, 'STUDENT' AS person_type, s.gpa::TEXT AS subtype_detail_1, s.major AS subtype_detail_2FROM person pINNER JOIN student s ON p.person_id = s.person_id UNION ALL SELECT p.person_id, p.name, p.email, 'FACULTY' AS person_type, f.rank AS subtype_detail_1, f.tenure_status AS subtype_detail_2FROM person pINNER JOIN faculty f ON p.person_id = f.person_id UNION ALL SELECT p.person_id, p.name, p.email, 'STAFF' AS person_type, st.position AS subtype_detail_1, st.department AS subtype_detail_2FROM person pINNER JOIN staff st ON p.person_id = st.person_id;Pattern 3: Views for Abstraction
Create views to simplify common query patterns:
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667
-- Complete student view (joins automatically)CREATE VIEW v_students ASSELECT p.person_id, p.name, p.email, p.date_of_birth, p.address, p.phone, s.student_number, s.enrollment_date, s.gpa, s.major, s.minor, s.credits_earned, s.expected_grad, s.academic_statusFROM person pINNER JOIN student s ON p.person_id = s.person_id; -- Complete faculty viewCREATE VIEW v_faculty ASSELECT p.person_id, p.name, p.email, p.date_of_birth, p.address, p.phone, f.faculty_id, f.rank, f.tenure_status, f.specialty, f.office_number, f.office_hours, f.hire_date, f.contract_endFROM person pINNER JOIN faculty f ON p.person_id = f.person_id; -- Unified all-persons viewCREATE VIEW v_all_persons ASSELECT person_id, name, email, date_of_birth, 'STUDENT' AS type, student_number AS type_id, major AS type_detailFROM v_studentsUNION ALLSELECT person_id, name, email, date_of_birth, 'FACULTY' AS type, faculty_id AS type_id, rank AS type_detailFROM v_facultyUNION ALLSELECT p.person_id, p.name, p.email, p.date_of_birth, 'STAFF' AS type, st.staff_id AS type_id, st.position AS type_detailFROM person pINNER JOIN staff st ON p.person_id = st.person_id; -- Now queries are simple:SELECT * FROM v_students WHERE gpa > 3.5;SELECT * FROM v_all_persons WHERE email LIKE '%@university.edu';For hierarchy-wide queries, UNION ALL often outperforms LEFT JOIN with many subtypes. Each UNION branch uses efficient INNER JOINs and targeted indexes. LEFT JOIN scans all subtypes for every supertype row. Profile both approaches with realistic data volumes.
TPT's greatest strength is its ability to enforce data integrity at the database level. Let's explore the powerful constraint patterns available:
Enforcing Disjoint Constraints:
When subtypes are disjoint (a person can be only ONE type), we need to prevent rows appearing in multiple subtype tables:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869
-- APPROACH 1: Trigger-based enforcementCREATE OR REPLACE FUNCTION enforce_disjoint_subtypes()RETURNS TRIGGER AS $$DECLARE subtype_count INTEGER;BEGIN -- Count how many subtype tables this person_id appears in SELECT (CASE WHEN EXISTS (SELECT 1 FROM student WHERE person_id = NEW.person_id) THEN 1 ELSE 0 END) + (CASE WHEN EXISTS (SELECT 1 FROM faculty WHERE person_id = NEW.person_id) THEN 1 ELSE 0 END) + (CASE WHEN EXISTS (SELECT 1 FROM staff WHERE person_id = NEW.person_id) THEN 1 ELSE 0 END) INTO subtype_count; -- If this insert would create a second subtype, reject it IF subtype_count >= 1 THEN RAISE EXCEPTION 'Disjoint constraint violation: Person % already exists in another subtype', NEW.person_id; END IF; RETURN NEW;END;$$ LANGUAGE plpgsql; -- Apply trigger to each subtype tableCREATE TRIGGER trg_student_disjointBEFORE INSERT ON studentFOR EACH ROW EXECUTE FUNCTION enforce_disjoint_subtypes(); CREATE TRIGGER trg_faculty_disjointBEFORE INSERT ON facultyFOR EACH ROW EXECUTE FUNCTION enforce_disjoint_subtypes(); CREATE TRIGGER trg_staff_disjointBEFORE INSERT ON staffFOR EACH ROW EXECUTE FUNCTION enforce_disjoint_subtypes(); -- APPROACH 2: Discriminator with CHECK constraint-- Add discriminator to supertype if not present:ALTER TABLE person ADD COLUMN IF NOT EXISTS person_type VARCHAR(20); -- Create function to check discriminator matches subtype tableCREATE OR REPLACE FUNCTION check_person_type( p_person_id INTEGER, expected_type VARCHAR) RETURNS BOOLEAN AS $$BEGIN RETURN EXISTS ( SELECT 1 FROM person WHERE person_id = p_person_id AND person_type = expected_type );END;$$ LANGUAGE plpgsql IMMUTABLE; -- Add check to each subtypeALTER TABLE student ADD CONSTRAINT chk_student_type CHECK (check_person_type(person_id, 'STUDENT')); ALTER TABLE faculty ADD CONSTRAINT chk_faculty_type CHECK (check_person_type(person_id, 'FACULTY')); ALTER TABLE staff ADD CONSTRAINT chk_staff_type CHECK (check_person_type(person_id, 'STAFF'));Enforcing Completeness (Total Participation):
When every supertype entity MUST belong to a subtype:
12345678910111213141516171819202122232425262728293031323334353637
-- Deferred constraint trigger for total participation-- Checks at transaction end that every person has a subtype CREATE OR REPLACE FUNCTION check_person_has_subtype()RETURNS TRIGGER AS $$BEGIN -- This runs at end of transaction (DEFERRED) IF NOT EXISTS ( SELECT 1 FROM student WHERE person_id = NEW.person_id UNION SELECT 1 FROM faculty WHERE person_id = NEW.person_id UNION SELECT 1 FROM staff WHERE person_id = NEW.person_id ) THEN RAISE EXCEPTION 'Total participation violation: Person % must belong to a subtype', NEW.person_id; END IF; RETURN NEW;END;$$ LANGUAGE plpgsql; -- CONSTRAINT trigger (can be deferred)CREATE CONSTRAINT TRIGGER trg_person_completenessAFTER INSERT ON personDEFERRABLE INITIALLY DEFERREDFOR EACH ROW EXECUTE FUNCTION check_person_has_subtype(); -- Usage: Both inserts in single transactionBEGIN; INSERT INTO person (name, email, date_of_birth, person_type) VALUES ('John Smith', 'john@university.edu', '1995-03-15', 'STUDENT'); -- Must insert subtype before transaction commits INSERT INTO student (person_id, student_number, enrollment_date) VALUES (currval('person_person_id_seq'), 'S12345', CURRENT_DATE);COMMIT; -- Constraint checked hereSubtype-Specific Relationship Constraints:
TPT excels at enforcing relationships that only apply to specific subtypes:
1234567891011121314151617181920212223242526272829303132333435363738
-- Only STUDENTS can enroll in coursesCREATE TABLE enrollment ( enrollment_id SERIAL PRIMARY KEY, student_id INTEGER NOT NULL REFERENCES student(person_id) -- FK to STUDENT table! ON DELETE CASCADE, course_id INTEGER NOT NULL REFERENCES course(course_id), semester VARCHAR(20) NOT NULL, grade VARCHAR(2), enrollment_date DATE DEFAULT CURRENT_DATE, UNIQUE (student_id, course_id, semester)); -- Only FACULTY can teach coursesCREATE TABLE course_assignment ( assignment_id SERIAL PRIMARY KEY, faculty_id INTEGER NOT NULL REFERENCES faculty(person_id) -- FK to FACULTY table! ON DELETE CASCADE, course_id INTEGER NOT NULL REFERENCES course(course_id), semester VARCHAR(20) NOT NULL, role VARCHAR(20) DEFAULT 'INSTRUCTOR', UNIQUE (faculty_id, course_id, semester)); -- Only STAFF can manage departmentsCREATE TABLE department_management ( department_id INTEGER PRIMARY KEY REFERENCES department(department_id), manager_id INTEGER NOT NULL REFERENCES staff(person_id) -- FK to STAFF table! ON DELETE RESTRICT, assigned_date DATE DEFAULT CURRENT_DATE); -- These constraints are IMPOSSIBLE to achieve with STI!-- STI would require triggers; TPT uses native FK constraintsThe ability to create foreign keys to specific subtype tables is TPT's most powerful advantage. It provides database-enforced integrity that STI cannot achieve. When your model has many subtype-specific relationships, TPT is almost always the right choice.
One of TPT's unique strengths is its natural support for overlapping hierarchies—where an entity can belong to multiple subtypes simultaneously.
Real-World Overlapping Examples:
In TPT, overlapping is handled simply by having rows in multiple subtype tables:
123456789101112131415161718192021222324252627282930313233343536373839404142
-- Example: Graduate Teaching Assistants-- Person can be BOTH student AND employee (staff) -- First, insert the personINSERT INTO person (name, email, date_of_birth, phone)VALUES ('Maria Garcia', 'maria@university.edu', '1998-05-20', '555-0123')RETURNING person_id; -- Returns: 42 -- Insert as student (PhD candidate)INSERT INTO student (person_id, student_number, enrollment_date, gpa, major)VALUES (42, 'G2024001', '2022-08-15', 3.85, 'Computer Science'); -- ALSO insert as staff (teaching assistant)INSERT INTO staff (person_id, staff_id, position, department, hire_date, work_schedule)VALUES (42, 'TA2024001', 'Teaching Assistant', 'Computer Science', '2023-01-15', 'PART_TIME'); -- Maria now appears in BOTH subtype tables - perfectly valid! -- Query to find all GTAs (people in both tables)SELECT p.name, p.email, s.student_number, s.major, st.position, st.departmentFROM person pINNER JOIN student s ON p.person_id = s.person_idINNER JOIN staff st ON p.person_id = st.person_id; -- Query to get complete profile including all rolesSELECT p.*, CASE WHEN s.person_id IS NOT NULL THEN TRUE ELSE FALSE END AS is_student, CASE WHEN f.person_id IS NOT NULL THEN TRUE ELSE FALSE END AS is_faculty, CASE WHEN st.person_id IS NOT NULL THEN TRUE ELSE FALSE END AS is_staffFROM person pLEFT JOIN student s ON p.person_id = s.person_idLEFT JOIN faculty f ON p.person_id = f.person_idLEFT JOIN staff st ON p.person_id = st.person_id;Managing Type Transitions:
TPT also handles type transitions gracefully—a student graduating and becoming faculty:
123456789101112131415161718192021222324252627282930313233
-- Scenario: Student graduates and becomes faculty member -- Option 1: Keep history (overlapping approach)-- Student graduates but row remains for historical recordUPDATE student SET academic_status = 'GRADUATED', expected_grad = CURRENT_DATEWHERE person_id = 42; -- Add faculty roleINSERT INTO faculty (person_id, faculty_id, rank, tenure_status, specialty, hire_date)VALUES (42, 'F2024042', 'ASSISTANT_PROFESSOR', 'TENURE_TRACK', 'Machine Learning', CURRENT_DATE); -- Option 2: Replace (disjoint approach)-- Use transaction for atomic transitionBEGIN; -- Archive or delete student record DELETE FROM student WHERE person_id = 42; -- Update person type UPDATE person SET person_type = 'FACULTY' WHERE person_id = 42; -- Create faculty record INSERT INTO faculty (person_id, faculty_id, rank, tenure_status, specialty, hire_date) VALUES (42, 'F2024042', 'ASSISTANT_PROFESSOR', 'TENURE_TRACK', 'Machine Learning', CURRENT_DATE);COMMIT; -- The supertype row (person) remains unchanged in either approach-- This preserves person_id stability for any referencesThe choice between overlapping and disjoint hierarchies should be driven by business requirements, not technical convenience. If your domain genuinely supports multi-role entities (a person can be student AND employee), model it as overlapping. If roles are mutually exclusive, enforce disjointness even though TPT doesn't require it.
TPT elegantly handles multi-level inheritance—where subtypes themselves have further specializations. Consider an extended university model:
Person
├── Student
│ ├── UndergraduateStudent
│ └── GraduateStudent
│ ├── MastersStudent
│ └── PhDStudent
├── Faculty
└── Staff
Each level adds its own table:
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495
-- Level 0: Root supertypeCREATE TABLE person ( person_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, date_of_birth DATE NOT NULL); -- Level 1: First specializationCREATE TABLE student ( person_id INTEGER PRIMARY KEY REFERENCES person(person_id), student_number VARCHAR(20) NOT NULL UNIQUE, enrollment_date DATE NOT NULL, gpa DECIMAL(3,2)); -- Level 2: Second specialization (subtypes of Student)CREATE TABLE undergraduate_student ( person_id INTEGER PRIMARY KEY REFERENCES student(person_id), class_standing VARCHAR(20) NOT NULL CHECK (class_standing IN ('FRESHMAN', 'SOPHOMORE', 'JUNIOR', 'SENIOR')), advisor_id INTEGER REFERENCES faculty(person_id), housing_status VARCHAR(20)); CREATE TABLE graduate_student ( person_id INTEGER PRIMARY KEY REFERENCES student(person_id), program_type VARCHAR(20) NOT NULL CHECK (program_type IN ('MASTERS', 'PHD')), thesis_title TEXT, advisor_id INTEGER REFERENCES faculty(person_id), qualifying_exam_passed BOOLEAN DEFAULT FALSE); -- Level 3: Third specialization (subtypes of GraduateStudent)CREATE TABLE masters_student ( person_id INTEGER PRIMARY KEY REFERENCES graduate_student(person_id), thesis_required BOOLEAN DEFAULT TRUE, internship_completed BOOLEAN DEFAULT FALSE, expected_completion DATE); CREATE TABLE phd_student ( person_id INTEGER PRIMARY KEY REFERENCES graduate_student(person_id), dissertation_title TEXT, candidacy_date DATE, defense_scheduled DATE, publications INTEGER DEFAULT 0, teaching_requirement_met BOOLEAN DEFAULT FALSE); -- ─────────────────────────────────────────────────────────────-- Querying deep hierarchies requires multiple joins-- ───────────────────────────────────────────────────────────── -- Get complete PhD student profile (all 4 levels)SELECT p.name, p.email, s.student_number, s.gpa, gs.program_type, gs.thesis_title, gs.advisor_id, phd.dissertation_title, phd.candidacy_date, phd.publicationsFROM person pINNER JOIN student s ON p.person_id = s.person_idINNER JOIN graduate_student gs ON s.person_id = gs.person_idINNER JOIN phd_student phd ON gs.person_id = phd.person_idWHERE phd.publications >= 3; -- Create view for simplified accessCREATE VIEW v_phd_students ASSELECT p.person_id, p.name, p.email, p.date_of_birth, s.student_number, s.enrollment_date, s.gpa, gs.thesis_title AS dissertation_proposal, gs.advisor_id, gs.qualifying_exam_passed, phd.dissertation_title, phd.candidacy_date, phd.defense_scheduled, phd.publications, phd.teaching_requirement_metFROM person pJOIN student s ON p.person_id = s.person_idJOIN graduate_student gs ON s.person_id = gs.person_idJOIN phd_student phd ON gs.person_id = phd.person_id;Each hierarchy level adds a required JOIN. A 4-level hierarchy needs 3 JOINs for complete data. While indexes on PKs make this efficient, deep hierarchies can impact query performance. Consider flattening very deep hierarchies or using views to abstract complexity.
TPT performance depends heavily on proper indexing and query optimization. Let's examine the key strategies:
Primary Key/Foreign Key Index Strategy:
TPT's PK/FK pattern naturally creates efficient join paths, but ensure indexes are optimal:
1234567891011121314151617181920212223242526272829303132333435363738394041424344
-- Primary keys are automatically indexed-- But verify index types and add covering indexes -- Analyze query patterns and add appropriate indexesCREATE INDEX idx_student_gpa_major ON student(gpa DESC, major);CREATE INDEX idx_student_enrollment ON student(enrollment_date); CREATE INDEX idx_faculty_rank_tenure ON faculty(rank, tenure_status);CREATE INDEX idx_faculty_specialty ON faculty(specialty) WHERE specialty IS NOT NULL; CREATE INDEX idx_staff_dept_position ON staff(department, position); -- Covering index for common student query-- Includes all columns needed so no table lookup requiredCREATE INDEX idx_student_covering ON student(person_id, student_number, gpa, major) INCLUDE (enrollment_date, credits_earned); -- For hierarchy-wide queries, consider materialized viewCREATE MATERIALIZED VIEW mv_all_persons ASSELECT p.person_id, p.name, p.email, p.date_of_birth, COALESCE( CASE WHEN s.person_id IS NOT NULL THEN 'STUDENT' END, CASE WHEN f.person_id IS NOT NULL THEN 'FACULTY' END, CASE WHEN st.person_id IS NOT NULL THEN 'STAFF' END ) AS person_type, s.gpa, s.major, f.rank, st.departmentFROM person pLEFT JOIN student s ON p.person_id = s.person_idLEFT JOIN faculty f ON p.person_id = f.person_idLEFT JOIN staff st ON p.person_id = st.person_id; CREATE UNIQUE INDEX ON mv_all_persons(person_id);CREATE INDEX ON mv_all_persons(person_type); -- Refresh periodically or on triggersREFRESH MATERIALIZED VIEW CONCURRENTLY mv_all_persons;Query Plan Analysis:
1234567891011121314151617181920212223242526272829303132333435363738394041
-- Analyze join performanceEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)SELECT p.name, p.email, s.gpa, s.majorFROM person pINNER JOIN student s ON p.person_id = s.person_idWHERE s.gpa > 3.5; -- Expected output (good):-- Nested Loop (cost=0.58..8.62 rows=1 width=100)-- -> Index Scan using idx_student_gpa_major on student s-- (cost=0.29..4.31 rows=1 width=54)-- Index Cond: (gpa > 3.5)-- -> Index Scan using person_pkey on person p -- (cost=0.29..4.31 rows=1 width=50)-- Index Cond: (person_id = s.person_id) -- If you see Seq Scan on large tables, investigate missing indexes -- Compare UNION vs LEFT JOIN for hierarchy-wide queryEXPLAIN ANALYZESELECT p.person_id, p.name, 'STUDENT' as typeFROM person p JOIN student s ON p.person_id = s.person_idUNION ALLSELECT p.person_id, p.name, 'FACULTY' as typeFROM person p JOIN faculty f ON p.person_id = f.person_idUNION ALLSELECT p.person_id, p.name, 'STAFF' as type FROM person p JOIN staff st ON p.person_id = st.person_id; -- vsEXPLAIN ANALYZESELECT p.person_id, p.name, CASE WHEN s.person_id IS NOT NULL THEN 'STUDENT' WHEN f.person_id IS NOT NULL THEN 'FACULTY' WHEN st.person_id IS NOT NULL THEN 'STAFF' END as typeFROM person pLEFT JOIN student s ON p.person_id = s.person_idLEFT JOIN faculty f ON p.person_id = f.person_idLEFT JOIN staff st ON p.person_id = st.person_id;TPT is the right choice under specific conditions. Let's crystallize the decision criteria:
Real-World TPT Success Stories:
We've comprehensively explored the Table-Per-Type pattern—its implementation, query strategies, integrity enforcement, and appropriate use cases.
What's Next:
The next page explores the Hybrid Approach—combining STI and TPT strategically for complex real-world hierarchies that don't fit neatly into either pure pattern.
You now have comprehensive knowledge of the Table-Per-Type pattern including implementation, join strategies, integrity enforcement, overlapping hierarchies, and performance optimization. You can confidently implement TPT when data integrity and flexibility are paramount.