ER diagram cardinality

Cardinality is the maximum number of rows on one side of a relationship that a single row on the other side can be linked to — one or many — and each end of a relationship line carries its own answer. Together with participation (can it be zero?) it produces the four line ends of crow’s foot notation — and in a real database it is not a choice on a canvas: ErdDocs reads it off the keys in the schema you paste.

What is the difference between cardinality and participation?

Two independent facts share each line end. Cardinality is the maximum — the inner mark, bar for one, crow’s foot for many. Participation is the minimum — the outer mark, bar for mandatory, circle for optional. The four combinations:

Exactly one — (1,1) — mandatory, single

Zero or one — (0,1) — optional, single

One or many — (1,N) — mandatory, repeating

Zero or many — (0,N) — optional, repeating

The three relationship shapes: one-to-one, one-to-many, many-to-many

One-to-many — the workhorse: nearly every foreign key produces one.

One-to-one — a foreign key that is also a primary key or unique.

Many-to-many — conceptual only: the database implements it as a junction table with two one-to-many lines.

How do you determine cardinality from a SQL schema?

To determine cardinality from a SQL schema, read the foreign key: in a database that exists, cardinality is not a design choice on a canvas — it is a consequence of keys and constraints. Five rules cover all of it:

DDL rules and the relationship cardinality each one produces in an ER diagram
The DDL says	The diagram shows	Example
A foreign key column exists	FK table is the many side; the referenced table is the one side	orders.customer_id → customers
The FK is NOT NULL	The one side is mandatory: minimum 1	an order cannot exist without its customer
The FK is nullable	The one side is optional: minimum 0	a task with assignee_id NULL is unassigned
The FK is also the PK, or unique	The many collapses: one-to-one	customer_profiles.customer_id PRIMARY KEY
The PK is two FKs together	A junction table: many-to-many through it	order_items (order_id, product_id)

The fourth rule is the one tools most often get wrong: an FK that is also the PK drawn as one-to-many overstates the schema. ErdDocs applies the collapse in both SQL and Prisma, so a one-to-one in your keys stays a one-to-one on the diagram.

Check it on a schema

The schema below produces all three shapes — select a line and ErdDocs names its cardinality in words on a plate. Paste your own dump to audit what your keys actually promise: parsing runs in your browser, and the schema never leaves your device.

Your schema

CREATE TABLE customers (
    id          bigserial PRIMARY KEY,
    email       varchar(255) NOT NULL UNIQUE,
    full_name   varchar(120)
);

CREATE TABLE customer_profiles (
    customer_id  bigint  PRIMARY KEY REFERENCES customers(id),
    marketing_ok boolean NOT NULL DEFAULT false,
    referred_by  varchar(120)
);

CREATE TABLE products (
    id     bigserial    PRIMARY KEY,
    sku    varchar(40)  NOT NULL UNIQUE,
    title  varchar(200) NOT NULL
);

CREATE TABLE orders (
    id           bigserial     PRIMARY KEY,
    customer_id  bigint        NOT NULL REFERENCES customers(id),
    status       varchar(20)   NOT NULL DEFAULT 'pending',
    total        numeric(10,2) NOT NULL
);

CREATE TABLE order_items (
    order_id    bigint        NOT NULL REFERENCES orders(id),
    product_id  bigint        NOT NULL REFERENCES products(id),
    qty         int           NOT NULL,
    unit_price  numeric(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE customers (
    id          bigserial PRIMARY KEY,
    email       varchar(255) NOT NULL UNIQUE,
    full_name   varchar(120)
);

CREATE TABLE customer_profiles (
    customer_id  bigint  PRIMARY KEY REFERENCES customers(id),
    marketing_ok boolean NOT NULL DEFAULT false,
    referred_by  varchar(120)
);

CREATE TABLE products (
    id     bigserial    PRIMARY KEY,
    sku    varchar(40)  NOT NULL UNIQUE,
    title  varchar(200) NOT NULL
);

CREATE TABLE orders (
    id           bigserial     PRIMARY KEY,
    customer_id  bigint        NOT NULL REFERENCES customers(id),
    status       varchar(20)   NOT NULL DEFAULT 'pending',
    total        numeric(10,2) NOT NULL
);

CREATE TABLE order_items (
    order_id    bigint        NOT NULL REFERENCES orders(id),
    product_id  bigint        NOT NULL REFERENCES products(id),
    qty         int           NOT NULL,
    unit_price  numeric(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Your diagram appears here.

FAQ

What is cardinality in an ER diagram, in one sentence?

What is the difference between cardinality and participation?

Cardinality is the maximum (one or many); participation is the minimum (zero or one — optional or mandatory). Crow’s foot stacks both on each line end: the inner mark is the maximum, the outer mark is the minimum. “Zero or many” is optional participation with many cardinality.

How do I determine cardinality from a SQL schema?

Read the foreign key. The table holding the FK column is the many side, the referenced table is the one side. NOT NULL on the FK makes the referenced side mandatory; a nullable FK makes it optional. If the FK is also the primary key or unique, the many collapses to one and the relationship is one-to-one. A table whose primary key is two FKs is a junction — a many-to-many.

What are min–max and Chen cardinality notations?

Min–max writes both numbers at each end: (0,1), (1,1), (0,N), (1,N) — the same facts crow’s foot draws as marks. Chen writes only the maximum as a letter near the relationship diamond: 1, N, M. Min–max notation appears mostly in academic material; crow’s foot is the one database tools draw, ErdDocs included — it reads the maximum straight off your foreign keys and names the relationship in words when you select its line.

ER diagram — what the whole notation is for, from entities to the generated diagram.
Crow’s foot notation — the reading rules and worked examples.
ER diagram symbols — the full symbol reference across notations.
ER diagram examples — eleven schemas to practice reading cardinality on.

What is the difference between cardinality and participation?

The three relationship shapes: one-to-one, one-to-many, many-to-many

How do you determine cardinality from a SQL schema?

Check it on a schema

FAQ

What is cardinality in an ER diagram, in one sentence?

What is the difference between cardinality and participation?

How do I determine cardinality from a SQL schema?

What are min–max and Chen cardinality notations?

Related