ER Diagrams and Inheritance Modeling

In one line

ER diagrams show entities, attributes and relationship cardinalities in crow's-foot notation, and they're the fastest way to agree on a model. When entities share a base (payment → card, UPI, wallet), choose single-table, class-table (joined) or concrete-table inheritance based on how often you query across subtypes.

0/4 · 0%

Think of it like this

Vehicles in a parking system. Cars, bikes and trucks share a plate number and owner, but trucks also have an axle count. You can keep one big form with blank fields, one shared form plus a small extra form per type, or a completely separate form per type.

Key ideas

01
Crow's-foot notation: || exactly one, |o zero or one, }| one or more, }o zero or more. Read each end separately: "a customer places zero or more orders; an order is placed by exactly one customer".
02
Single-table inheritance: one payment_method table with a type column and nullable subtype columns. Fast, simple queries across types; many NULLs; CHECK constraints must be per type (CHECK (type <> 'card' OR card_last4 IS NOT NULL)).
03
Class-table (joined) inheritance: a base table with shared columns and one table per subtype sharing the primary key. Clean constraints and no NULL sprawl; every subtype read needs a join.
04
Concrete-table inheritance: a full table per subtype, no base table. Fast per-type queries; cross-type queries need UNION ALL, and uniqueness across types is hard to enforce.
05
A fourth option is a base table plus a JSONB details column validated by the application or a CHECK on jsonb_typeof. Useful when subtypes change often, at the cost of weaker constraints.

Code & diagrams

payments-er.mermaiddiagram

Rendering diagram…

class-table-inheritance.sqlsql

CREATE TABLE payment_method (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customer(id),
  type        text   NOT NULL CHECK (type IN ('card','upi','wallet')),
  UNIQUE (id, type)                 -- lets subtypes pin the type
);
CREATE TABLE card (
  payment_method_id bigint PRIMARY KEY,
  type    text NOT NULL DEFAULT 'card' CHECK (type = 'card'),
  last4   char(4) NOT NULL,
  network text NOT NULL,
  FOREIGN KEY (payment_method_id, type) REFERENCES payment_method(id, type)
);
-- The composite FK guarantees a card row can only attach to a 'card' base row.

Interview problem

The problem

Model notifications of different channels

Notifications can be email (subject, body, to_address), SMS (phone, text) or push (device_token, title, payload). Most queries list a user's notifications across all channels by time; the sender service reads per channel. Pick an inheritance strategy.

When it breaks

Single-table inheritance grows to dozens of subtypes

What you see

A 120-column table that is mostly NULL, per-type rules nobody can enforce, and wide rows that hurt cache efficiency.

Fix & prevent

Move subtype columns to joined tables or a validated JSONB column; keep only shared, frequently filtered columns in the base.

Explain it without notes

Compare single-table and class-table inheritance for constraints and query cost.

Practice

Draw the crow's-foot ER diagram for customer, order, order_item and product.

Trade-offs

↔
Inheritance choice trades join cost against constraint strength and schema sprawl.

Done when you can

I can draw a correct ER diagram and justify an inheritance mapping.

Command Palette