What is the difference between a schema diagram and an ER diagram?

Usage overlaps. An ER diagram can model conceptual entities, while a physical schema diagram shows implemented tables and constraints. Check which level the diagram represents.

Does a foreign key always point to a primary key?

No. Databases such as PostgreSQL and SQLite also allow references to suitable unique keys, subject to their rules.

Why does a join duplicate rows?

One row can match several rows on the other side. Check the intended grain of the result and the uniqueness of the join keys before adding DISTINCT.

Schemas

The join ran. Why did the total triple?

A diagram is useful when it tells you how many rows a join can produce. Follow one order through the tables.

Published October 20264 min read

Try the join example

An old hand-colored engraved map of North and Central America

Photo: Map of North and Central America, Nicolas Sanson, Adriaan de Winter and François Halma, Public domain, via Wikimedia Commons.

Imagine a shop with one order worth $30 and three order lines. Join the order to its lines and sum the order total. The query runs. The answer is $90.

No row is corrupt. The join repeated the parent total once for each matching line. A database schema diagram helps you spot that relationship before it reaches a report.

A small example with a wrong total

SQL: runnable in SQLite or PostgreSQL

WITH orders(id, total_cents) AS (VALUES (1, 3000)),
order_items(order_id, line_no) AS (VALUES (1, 1), (1, 2), (1, 3))
SELECT SUM(o.total_cents) AS wrong_total_cents
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id;

The result is 9000 cents. The intended order total is 3000. To sum orders, read at the order level. If you need a condition about their lines, an EXISTS test may express the question without repeating each order.

SQL: count each qualifying order once

WITH orders(id, total_cents) AS (VALUES (1, 3000)),
order_items(order_id, line_no) AS (VALUES (1, 1), (1, 2), (1, 3))
SELECT SUM(o.total_cents) AS total_cents
FROM orders AS o
WHERE EXISTS (
    SELECT 1 FROM order_items AS i WHERE i.order_id = o.id
);

This returns 3000. Do not reach for SUM(DISTINCT total_cents) as a general fix: two different orders can legitimately have the same total.

How to read the database schema diagram

Schema diagram of four tables, customers, orders, order_items and products, joined by relationship lines with crow's foot symbols — One customer places many orders. An order contains many products through order_items, which links the two.

Mark	What to look for
Table box	The entity or record type, then the columns it contains.
PK	The primary key. It may consist of more than one column.
FK	A foreign key referencing a key in another table, or sometimes the same table.
Crow's foot	The many side of a relationship in this notation. Read it together with the minimum-cardinality symbol.
Circle or bar	Usually optional or required participation in crow's-foot notation. Check the tool's legend; notation varies.

Follow one order

Start at orders, because that is the record your question counts. A customer can have multiple orders. Each order can have multiple lines. Each line points to a product. Moving toward the many side can increase the row count; moving back toward a unique parent usually does not.

A junction table connects two sets of records. In this illustration, order_items connects orders and products. Some real schemas allow the same product on several lines and use a line number or a separate line ID. Read the actual key before assuming that an order/product pair is unique.

A drawn line is not the whole rule

A foreign key can reference an eligible unique key, not only a primary key. A nullable foreign key can allow no parent value. A unique constraint on the foreign key can also change the relationship from many-to-one to one-to-one. Inspect the constraints as well as the picture.

Diagrams generated from declared foreign keys may omit relationships the application enforces itself. Some tools let you add inferred or manual links. A missing line is a reason to investigate, not proof that two tables are unrelated.

Check the relationship in SQLite

SQLite: inspect existing definitions

PRAGMA foreign_key_list(orders);
PRAGMA table_info(orders);
PRAGMA foreign_keys;

The last command reports whether foreign key enforcement is enabled on this connection. Set PRAGMA foreign_keys = ON; before starting a transaction when you need enforcement. Do not assume every application enables it. To inspect existing violations, use PRAGMA foreign_key_check;.

Schema diagram questions

What is the difference between a schema diagram and an ER diagram?: Usage overlaps. An ER diagram can model conceptual entities, while a physical schema diagram shows implemented tables and constraints. Check which level the diagram represents.
Does a foreign key always point to a primary key?: No. Databases such as PostgreSQL and SQLite also allow references to suitable unique keys, subject to their rules.
Why does a join duplicate rows?: One row can match several rows on the other side. Check the intended grain of the result and the uniqueness of the join keys before adding DISTINCT.