Supertype and Subtype Tables: Single-, Class- or Concrete-Table Inheritance

Your ER diagram has a payment that is either a card payment or a bank transfer, and relational tables have no inheritance. The hierarchy becomes one table with a type column, a table per entity linked by a shared key, or a table per subtype that repeats the shared columns. In DbSchema Architect you draw the hierarchy once, with Super Table and Discriminator, and the mapping is the Inheritance Model you pick at Generate Physical Model, so you can switch between SingleTable, ClassTable and ConcreteTable and regenerate to compare.

Generalization and specialization describe one hierarchy from two directions

Specialization starts from the supertype and splits it: a payment becomes a card payment or a bank transfer. Generalization starts from the subtypes and finds what they share: card payments and bank transfers both have an amount, so they become payments. Both end at the same structure: the supertype holds the shared attributes, and each subtype adds its own.

Two questions about the hierarchy decide which mapping can hold it. Is it disjoint, so a payment is a card payment or a bank transfer but never both? And is it total, so every payment has a subtype? The payment hierarchy is both.

Single table needs a discriminator and a CHECK per subtype

Single-table inheritance puts the whole hierarchy into one table with a column for every attribute of every subtype (Martin Fowler, Single Table Inheritance). A discriminator column says which subtype a row is:

CREATE TABLE payments (
  payment_id   integer PRIMARY KEY,
  payment_type varchar(10) NOT NULL CHECK (payment_type IN ('card', 'transfer')),
  amount       numeric(10,2) NOT NULL,
  card_last4   char(4),
  iban         varchar(34),
  CHECK (
    (payment_type = 'card'     AND card_last4 IS NOT NULL AND iban IS NULL) OR
    (payment_type = 'transfer' AND iban IS NOT NULL AND card_last4 IS NULL)
  )
);
INSERT INTO payments VALUES (1, 'card', 49.90, '4242', NULL);
INSERT INTO payments VALUES (2, 'transfer', 120.00, NULL, 'DE89370400440532013000');
INSERT INTO payments VALUES (3, 'card', 15.00, NULL, NULL);

The third row is a card payment without a card number, and the row CHECK refuses it:

ERROR:  new row for relation "payments" violates check constraint "payments_check"
DETAIL:  Failing row contains (3, card, 15.00, null, null).

Reading across subtypes needs no join:

SELECT payment_type, count(*), sum(amount) FROM payments GROUP BY payment_type ORDER BY 1;

The cost is the CHECK itself. card_last4 can't be NOT NULL, because transfers leave it empty, so the rule lives only in that expression, and it grows with every subtype column.

Class tables stay disjoint only with a composite foreign key

Class-table inheritance gives every entity its own table (Martin Fowler, Class Table Inheritance). Each subtype table reuses the supertype's key as its primary key and as a foreign key:

CREATE TABLE payments (
  payment_id integer PRIMARY KEY,
  amount     numeric(10,2) NOT NULL
);
CREATE TABLE card_payments (
  payment_id integer PRIMARY KEY REFERENCES payments,
  card_last4 char(4) NOT NULL
);
CREATE TABLE bank_transfers (
  payment_id integer PRIMARY KEY REFERENCES payments,
  iban       varchar(34) NOT NULL
);
INSERT INTO payments VALUES (1, 49.90), (2, 120.00);
INSERT INTO card_payments VALUES (1, '4242');
INSERT INTO bank_transfers VALUES (2, 'DE89370400440532013000');
INSERT INTO bank_transfers VALUES (1, 'FR7630006000011234567890189');

Every subtype column is NOT NULL now. The last insert makes payment 1 a bank transfer as well as a card payment, and PostgreSQL accepts it:

SELECT p.payment_id, p.amount, c.card_last4, b.iban
FROM payments p
LEFT JOIN card_payments c ON c.payment_id = p.payment_id
LEFT JOIN bank_transfers b ON b.payment_id = p.payment_id
ORDER BY 1;

The foreign keys only say that a subtype row has a payment. To keep the hierarchy disjoint, store the discriminator in payments and make it part of the foreign key:

CREATE TABLE payments (
  payment_id   integer PRIMARY KEY,
  payment_type varchar(10) NOT NULL CHECK (payment_type IN ('card', 'transfer')),
  amount       numeric(10,2) NOT NULL,
  UNIQUE (payment_id, payment_type)
);
CREATE TABLE bank_transfers (
  payment_id   integer PRIMARY KEY,
  payment_type varchar(10) NOT NULL DEFAULT 'transfer' CHECK (payment_type = 'transfer'),
  iban         varchar(34) NOT NULL,
  FOREIGN KEY (payment_id, payment_type) REFERENCES payments (payment_id, payment_type)
);

card_payments gets the same pair of columns with 'card'. Run again, the insert that turned payment 1 into a transfer fails:

ERROR:  insert or update on table "bank_transfers" violates foreign key constraint "bank_transfers_payment_id_payment_type_fkey"
DETAIL:  Key (payment_id, payment_type)=(1, transfer) is not present in table "payments".

For an overlapping hierarchy, leave the discriminator out and keep the plain foreign keys. What no foreign key enforces is totality: a payment with no subtype row is still valid. The price of this mapping is a join for every subtype you read.

Concrete tables share no key across subtypes

Concrete-table inheritance makes one table per subtype and copies the supertype's columns into each (Martin Fowler, Concrete Table Inheritance). There is no payments table. A shared sequence is the usual way to keep ids apart:

CREATE SEQUENCE payment_id_seq;
CREATE TABLE card_payments (
  payment_id integer PRIMARY KEY DEFAULT nextval('payment_id_seq'),
  amount     numeric(10,2) NOT NULL,
  card_last4 char(4) NOT NULL
);
CREATE TABLE bank_transfers (
  payment_id integer PRIMARY KEY DEFAULT nextval('payment_id_seq'),
  amount     numeric(10,2) NOT NULL,
  iban       varchar(34) NOT NULL
);
INSERT INTO card_payments (amount, card_last4) VALUES (49.90, '4242');
INSERT INTO bank_transfers (amount, iban) VALUES (120.00, 'DE89370400440532013000');
INSERT INTO bank_transfers (payment_id, amount, iban) VALUES (1, 10.00, 'FR7630006000011234567890189');
SELECT 'card' AS payment_type, payment_id, amount FROM card_payments
UNION ALL
SELECT 'transfer', payment_id, amount FROM bank_transfers
ORDER BY payment_id, payment_type;

Payment 1 now exists twice. The sequence only protects rows that take the default, and each primary key covers its own table. A refund table can't reference "a payment" either, only one of the two tables.

PostgreSQL INHERITS is not a fourth answer

PostgreSQL has table inheritance of its own, and it looks like the hierarchy built in:

CREATE TABLE payments (payment_id integer PRIMARY KEY, amount numeric(10,2) NOT NULL);
CREATE TABLE card_payments (card_last4 char(4) NOT NULL) INHERITS (payments);
INSERT INTO payments VALUES (1, 49.90);
INSERT INTO card_payments VALUES (1, 15.00, '4242');
SELECT tableoid::regclass AS stored_in, payment_id, amount FROM payments ORDER BY 1;

The primary key of payments did not stop the child row with the same id, and a query on the parent returns both. The PostgreSQL manual names this as the feature's main limitation: unique constraints and foreign keys apply to single tables, not to their inheritance children (PostgreSQL 17, Inheritance caveats).

Which mapping fits which hierarchy

Class table is the safe default: every subtype column can be NOT NULL, disjointness has a constraint, and other tables can point at a payment. Only totality stays with the application. Choose single table when the subtypes add few columns and most reads span all of them. Keep concrete table for subtypes that never need to be listed or referenced together. In DbSchema Architect that choice stays one setting: changing your mind means generating the physical model again with another Inheritance Model, not rewriting the DDL, and you still check the generated tables for the CHECK or composite foreign key described above.

Model the hierarchy once and generate the mapping

The mapping is a decision about storage, and the hierarchy is a decision about the data. DbSchema keeps them apart. In a logical design, the hierarchy is part of the model, and the mapping is chosen when DbSchema generates the physical model. Logical design needs DbSchema Architect.

1. Start a logical model Architect

   Choose File → New Model, then Design without Database and Logical Design, and press Start Design. The model connects to no database until you generate the physical model.

2. Create the supertype and the subtypes Architect

   Create the entities payment, card payment and bank transfer with Table → Create Entity. In each subtype, set Super Table to payment, so it inherits payment id and amount.

3. Mark the discriminator Architect

   Add a payment type attribute to payment. In its attribute dialog, tick Discriminator: it marks the attribute that tells the subtypes apart.

4. Generate the tables Architect

   Choose Model → Generate Physical Model, set Target DBMS, such as PostgreSQL, and pick the Inheritance Model: SingleTable, ClassTable or ConcreteTable. Check the Model Conversion Rules and press Convert.

1

1. 1Pick the supertype here

The logical design stays as it is, so you can generate it again with ClassTable, or for MySQL, and compare the tables before anything reaches a database. Then check the generated tables for the constraints from the sections above, such as the composite foreign key that keeps class tables disjoint, add any your hierarchy needs, and create the schema in the database.

Download DbSchema: every new installation starts with a 15-day DbSchema Architect trial. Draw your hierarchy as a logical design and generate it with each Inheritance Model to see which tables fit your data. Logical design and Generate Physical Model are in DbSchema Architect.

Sources

1. PostgreSQL 17 documentation: Inheritance, 5.11.1 Caveats
2. Martin Fowler: Single Table Inheritance
3. Martin Fowler: Class Table Inheritance
4. Martin Fowler: Concrete Table Inheritance