Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 90 ยท Schema Design and Constraints

Table Inheritance and Common Design Patterns

Learn when PostgreSQL INHERITS helps and when it does not, plus proven patterns: soft delete, audit columns, polymorphic links, EAV pitfalls and tagging.

Some schema problems come up again and again: how do you delete a row but keep its history, how can a comment belong to either a post or a photo, how do you store tags, and what do you do when every product has different attributes? Each has a well-known solution with trade-offs. PostgreSQL also has a feature, table inheritance, that looks like a fit for several of these problems but is usually the wrong tool.

This lesson walks through table inheritance first, so you can recognize it and know its limits, then through five patterns you will meet in real projects. It builds on Designing Tables the PostgreSQL Way and Constraints in Depth.

What runs live here. The editors use SQLite (sql.js). Soft delete with a partial unique index, exclusive-arc polymorphic links, join-table tags and an EAV demonstration all run live. INHERITS, array columns and PostgreSQL-only functions are read-only blocks.

Five patterns, one lesson

Soft delete Mark rows deleted instead of removing them.
Audit columns Who and when, on every row.
Polymorphic link One child, several possible parents.
Tags Many labels per item.
1 / 2

Table inheritance with INHERITS

PostgreSQL lets one table inherit the columns of another:

SQL
CREATE TABLE vehicles (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  plate text NOT NULL,
  made  int  NOT NULL
);

CREATE TABLE trucks (
  load_tons numeric(5,1) NOT NULL
) INHERITS (vehicles);

INSERT INTO trucks (plate, made, load_tons) VALUES ('TRK-100', 2021, 12.5);

SELECT plate FROM vehicles;        -- includes rows stored in trucks
SELECT plate FROM ONLY vehicles;   -- excludes child tables

trucks has all the columns of vehicles plus its own. Querying the parent also returns the child's rows, and ONLY limits the query to the parent itself. It looks like object-oriented inheritance, but the limits are severe:

  • Primary keys, unique constraints, indexes and foreign keys are not inherited. The id primary key on vehicles only guarantees uniqueness inside vehicles. A truck can have the same id as a car.
  • Foreign keys to the parent do not see child rows. A table that references vehicles(id) cannot reference a row stored in trucks.
  • NOT NULL and CHECK constraints are inherited, but you must manage everything else per table.
  • Changing columns on the parent changes all children, which is both convenient and risky.

Because of these gaps, inheritance is a poor model for "a truck is a kind of vehicle". It was the original way to do partitioning, but declarative partitioning replaced it. See Declarative Partitioning. Use INHERITS only if you maintain a legacy schema that already relies on it.

The usual way to model "kinds of a thing" is a shared parent table with a one-to-one extension table. The pattern is plain relational design and works in every database:

SQL
CREATE TABLE vehicles (id bigint PRIMARY KEY, plate text NOT NULL, kind text NOT NULL);
CREATE TABLE truck_details (
  vehicle_id bigint PRIMARY KEY REFERENCES vehicles (id),
  load_tons  numeric(5,1) NOT NULL
);

Here the foreign key and primary key are real, so every constraint holds.

Pattern 1: Soft delete

A soft delete marks a row as deleted instead of removing it. You keep history, can undo mistakes, and avoid breaking foreign keys. The cost is that every query must remember to hide deleted rows, and unique rules must ignore them.

SQL
ALTER TABLE users ADD COLUMN deleted_at timestamptz;

A null deleted_at means "live". The trick is the unique rule. If email must be unique, a deleted user should not block a new signup with the same address. A partial unique index limits uniqueness to live rows:

SQL Soft delete with a partial unique index

Press Run (or Ctrl + Enter) to see the result here.

There are three rows in total (two for [email protected]) but only two live ones, and the unique index allowed the second Ada because the first has a deleted_at value. Notice the view live_users: giving application code a view that hides deleted rows prevents the classic bug of forgetting the filter. PostgreSQL syntax is the same, with timestamptz for the column and CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL. Partial indexes are covered in Partial and Expression Indexes.

Soft delete is not free. Tables grow forever, privacy rules (such as erasing personal data on request) may require a real delete, and foreign keys still point at deleted rows. Consider moving old deleted rows to an archive table after some time.

Pattern 2: Audit columns

Most tables benefit from a few columns that say when and by whom a row was created or changed:

SQL
CREATE TABLE invoices (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount      numeric(12,2) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  created_by  bigint REFERENCES users (id),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  updated_by  bigint REFERENCES users (id)
);

created_at is easy because a DEFAULT fills it in. updated_at is harder: PostgreSQL does not update it for you, so it needs either application code on every update or a trigger. A trigger is more reliable because scripts and manual fixes also update the column. You will build one in Triggers. For a full history of old values, not only the last change, you need an audit table, which is the subject of Project: Audit Log with Triggers.

Pattern 3: Polymorphic associations

Imagine a comments table where a comment can belong to a post or a photo. A popular approach, common in some web frameworks, uses two columns:

SQL
commentable_type text,      -- 'post' or 'photo'
commentable_id   bigint     -- the id in whichever table

The problem is that commentable_id cannot be a foreign key, because it points to different tables. The database cannot stop a comment pointing to a post that does not exist, and deleting a post leaves orphans behind.

A safer design gives each possible parent its own nullable foreign key column, and a CHECK requires exactly one of them. This is called an exclusive arc:

SQL Exclusive arc: one parent, enforced

Press Run (or Ctrl + Enter) to see the result here.

After deleting the post, only the photo comment remains. Every comment has a real foreign key, and the cascade works. In PostgreSQL you can write the check as CHECK (num_nonnulls(post_id, photo_id) = 1), which scales better when there are many parents. The cost is one column per parent type, which is fine for a handful of types. For many types, a shared parent table that both posts and photos reference (a supertype) is the alternative.

Pattern 4: Entity-attribute-value, and why to avoid it

EAV stores every attribute as a row: (entity_id, attribute, value). It looks flexible, since adding an attribute is just adding a row. In practice it gives up what makes a relational database useful:

SQL EAV: flexible, but painful

Press Run (or Ctrl + Enter) to see the result here.

You get three product rows, but the third has the price sixty (every value is text, so there is no number check), and no color at all. You cannot make price required, cannot index by attribute type cleanly, and every query needs a pivot like the one above. In PostgreSQL, prefer these alternatives:

  • Real columns for attributes every row has.
  • A jsonb column for the varying extras, with a CHECK for must-have keys (see JSON and JSONB).
  • A table per product type if the types are few and distinct.

EAV is justified only when the set of attributes truly is user-defined and unbounded, and even then JSONB usually does the job better.

Pattern 5: Tagging

Tags are a many-to-many relationship. The portable solution is a join table:

SQL Tags with a join table

Press Run (or Ctrl + Enter) to see the result here.

The result is Joins and Views. The composite primary key prevents tagging the same article twice with the same tag, foreign keys prevent unknown tags, and renaming a tag changes one row.

PostgreSQL offers a shortcut: an array column on the article itself.

SQL
CREATE TABLE articles (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  tags  text[] NOT NULL DEFAULT '{}'
);
CREATE INDEX articles_tags_idx ON articles USING gin (tags);

SELECT title FROM articles WHERE tags @> ARRAY['sql', 'beginner'];

The @> operator means "contains all of these", and the GIN index makes it fast. Arrays are simpler and need no joins, but they cannot enforce that a tag exists, renaming a tag means rewriting many rows, and you cannot attach data to a tag such as a description. Use arrays for free-form, throwaway labels, and a join table when tags are a managed vocabulary. See Arrays and GIN and GiST Indexes.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A table child INHERITS (parent), and parent has a primary key. What does the primary key guarantee?

    Primary keys, unique constraints, indexes and foreign keys are not shared across an inheritance hierarchy. This is a main reason to avoid INHERITS for modeling.
  2. 2. Why use a partial unique index on email WHERE deleted_at IS NULL?

    The index only covers live rows, so deleted rows are ignored by the uniqueness rule while live rows stay unique.
  3. 3. What is wrong with a commentable_type plus commentable_id polymorphic design?

    One column cannot reference several tables. An exclusive arc with one nullable foreign key per parent and a CHECK keeps referential integrity.
  4. 4. Which is a main drawback of entity-attribute-value tables?

    EAV stores everything as generic rows, so you cannot enforce types or required attributes and must pivot to rebuild a normal row.
  5. 5. When is a text[] column a reasonable choice for tags?

    Arrays are simple and can be indexed with GIN, but they cannot enforce that tags exist or carry extra data. A join table suits managed tags.

Summary

  • INHERITS shares columns but not keys, indexes or foreign keys, so prefer partitioning or a parent table plus extension tables.
  • Soft delete uses a deleted_at column, a partial unique index and a view that hides deleted rows.
  • Audit columns (created_at, updated_at, and who) come cheaply; keep updated_at correct with a trigger.
  • For polymorphic links, use one nullable foreign key per parent and a CHECK that exactly one is set.
  • Avoid EAV; use real columns plus jsonb for flexible extras.
  • Tags go in a join table when they are a managed vocabulary, or in a GIN-indexed array for free-form labels.
  • The live editors ran the portable patterns on SQLite; INHERITS and array syntax are PostgreSQL features shown read-only.

Next: Schema Changes and Migrations.