In this post we are going to cover cardinality, and how to read an ER diagram without getting the relationships wrong.
Cardinality describes how many rows on one side of a relationship can match rows on the other side. There are three kinds: one-to-one, one-to-many and many-to-many. An ER (entity-relationship) diagram draws the tables as boxes and the relationships as lines, and the symbols at the ends of each line tell you the cardinality.
We will build a small TB Shop model with all three kinds, query each one, and then look at a second meaning of cardinality that catches people out in interviews.
Question 13 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
Cardinality is how many rows on one side of a relationship can match rows on the other: one-to-one, one-to-many or many-to-many. In an ER diagram (crow's foot notation), a bar means one, a crow's foot means many, and a circle means optional (zero). Many-to-many is built with a junction table.
Crow's foot notation in one minute
Most ER diagrams use crow's foot notation. Each end of a relationship line gets symbols:
- Bar - one.
- Crow's foot (three little lines) - many.
- Circle - optional, so zero is allowed.
Put together, a bar and a bar mean "exactly one", a circle and a bar mean "zero or one", a bar and a crow's foot mean "one or many", and a circle and a crow's foot mean "zero or many".
Read every line twice, once in each direction. For example: "a customer places zero or many orders" and "each order belongs to exactly one customer".
Let's create some sample data
We need five tables: customers, profiles (one-to-one with customers), orders (one-to-many from customers), products, and order_items, the junction table between orders and products.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS profiles;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), status VARCHAR(10));
CREATE TABLE profiles (customer_id INT PRIMARY KEY REFERENCES customers(customer_id), -- 1:1
loyalty_tier VARCHAR(10));
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT REFERENCES customers(customer_id)); -- 1:N
CREATE TABLE products (product_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE order_items ( -- M:N junction table
order_id INT REFERENCES orders(order_id),
product_id INT REFERENCES products(product_id),
qty INT,
PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES
(1, 'Sara Khan', 'sara@example.com', 'Active'),
(2, 'Omar Ali', 'omar@example.com', 'Active'),
(3, 'Lina Park', 'lina@example.com', 'Inactive');
INSERT INTO profiles VALUES (1, 'Gold'), (2, 'Silver');
INSERT INTO orders VALUES (1001, 1), (1002, 2), (1003, 1);
INSERT INTO products VALUES (101, 'Laptop Stand'), (102, 'USB-C Cable'), (103, 'Wireless Mouse');
INSERT INTO order_items VALUES (1001, 101, 1), (1001, 102, 2), (1002, 102, 1), (1003, 101, 1);Let's look at the customers.
SELECT * FROM customers ORDER BY 1;| customer_id | name | status | |
|---|---|---|---|
| 1 | Sara Khan | sara@example.com | Active |
| 2 | Omar Ali | omar@example.com | Active |
| 3 | Lina Park | lina@example.com | Inactive |
Three customers. Lina Park is the only one with status Inactive.
SELECT * FROM profiles ORDER BY 1;| customer_id | loyalty_tier |
|---|---|
| 1 | Gold |
| 2 | Silver |
This is the one-to-one relationship. The primary key of profiles is also the foreign key to customers, so each customer can have at most one profile. Lina has none, which is allowed: zero or one. Teams split tables like this to keep optional or sensitive columns apart.
SELECT * FROM orders ORDER BY 1;| order_id | customer_id |
|---|---|
| 1001 | 1 |
| 1002 | 2 |
| 1003 | 1 |
Each order stores exactly one customer_id. The foreign key lives on the "many" side.
SELECT * FROM order_items ORDER BY 1;| order_id | product_id | qty |
|---|---|---|
| 1001 | 101 | 1 |
| 1001 | 102 | 2 |
| 1002 | 102 | 1 |
| 1003 | 101 | 1 |
One row per order and product pair. This table is what makes many-to-many possible.
Step 1: One-to-many - orders per customer
One customer can place many orders, but each order belongs to exactly one customer. In the diagram the customer end has a bar (exactly one) and the order end has a circle and a crow's foot (zero or many). Let's count orders per customer, with a LEFT JOIN so customers with no orders still show up.
SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY orders DESC;| name | orders |
|---|---|
| Sara Khan | 2 |
| Omar Ali | 1 |
| Lina Park | 0 |
Sara Khan has 2 orders, Omar Ali has 1, and Lina Park has 0. Zero is fine, because the relationship is "zero or many".
Step 2: Many-to-many - the junction table
An order can contain many products, and a product can appear in many orders. Relational tables cannot store that directly, because you cannot put a list of products in one order cell. So order_items sits in the middle with one row per pair, and the many-to-many becomes two one-to-many relationships: an order has many order items, and a product has many order items. Let's count how many orders each product appears in.
SELECT p.name, COUNT(*) AS in_orders
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.name
ORDER BY p.name;| name | in_orders |
|---|---|
| Laptop Stand | 2 |
| USB-C Cable | 2 |
Laptop Stand and USB-C Cable each appear in 2 orders. Wireless Mouse is not in any order yet, so it does not show up here. Junction tables are also called bridge or associative tables, and they come back in the warehouse as bridge tables.
Reading the full diagram
Now we can read the whole TB Shop diagram from left to right:
- A customer places zero or many orders, and each order belongs to exactly one customer.
- An order has one or many order items, and each order item belongs to exactly one order.
- A product appears in zero or many order items, and each order item is for exactly one product.
Step 3: The other cardinality - distinct values
One trap: cardinality has a second meaning. It can also mean the number of distinct values in a column. Let's measure it for email and status.
SELECT COUNT(DISTINCT email) AS email_values,
COUNT(DISTINCT status) AS status_values
FROM customers;| email_values | status_values |
|---|---|
| 3 | 2 |
There are 3 distinct emails for 3 customers, so email has high cardinality: every value is different. status has only 2 distinct values, so it has low cardinality. This meaning matters for indexing, partitioning and junk dimensions. If an interviewer says "high-cardinality column", they mean this one, not relationships.
Follow-up questions interviewers ask
Where does the foreign key go in a one-to-many?
On the "many" side (orders.customer_id).
How do you implement many-to-many?
With a junction (bridge / associative) table holding one row per pair.
What is a high-cardinality column?
A column with many distinct values, such as email or order_id.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #13 - Cardinality and ER diagrams
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q13; SET search_path TO dwh_q13;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS profiles;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), status VARCHAR(10));
CREATE TABLE profiles (customer_id INT PRIMARY KEY REFERENCES customers(customer_id), -- 1:1
loyalty_tier VARCHAR(10));
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT REFERENCES customers(customer_id)); -- 1:N
CREATE TABLE products (product_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE order_items ( -- M:N junction table
order_id INT REFERENCES orders(order_id),
product_id INT REFERENCES products(product_id),
qty INT,
PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES
(1, 'Sara Khan', 'sara@example.com', 'Active'),
(2, 'Omar Ali', 'omar@example.com', 'Active'),
(3, 'Lina Park', 'lina@example.com', 'Inactive');
INSERT INTO profiles VALUES (1, 'Gold'), (2, 'Silver');
INSERT INTO orders VALUES (1001, 1), (1002, 2), (1003, 1);
INSERT INTO products VALUES (101, 'Laptop Stand'), (102, 'USB-C Cable'), (103, 'Wireless Mouse');
INSERT INTO order_items VALUES (1001, 101, 1), (1001, 102, 2), (1002, 102, 1), (1003, 101, 1);
-- Step 1: One-to-many: orders per customer (zero or many)
SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY orders DESC;
-- Step 2: Many-to-many: orders per product (through the junction table)
SELECT p.name, COUNT(*) AS in_orders
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.name
ORDER BY p.name;
-- Step 3: Column cardinality: how many distinct values?
SELECT COUNT(DISTINCT email) AS email_values,
COUNT(DISTINCT status) AS status_values
FROM customers;
Key points
- 1:N is the most common: the foreign key lives on the "many" side
- 1:1: the child's primary key is also its foreign key
- M:N: built with a junction table = two one-to-manys
- Crow's foot: bar = one, crow's foot = many, circle = optional
- Column cardinality = number of distinct values
Question 14 — What is dimensional modeling? What is dimensional modeling?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.