Database Relationships: Understanding Cardinality

Before diving into data modeling, it’s important to understand cardinality — one of the foundational ideas in relational databases. Cardinality helps you understand how data in one table relates to data in another. Knowing this is key to designing databases that are efficient, accurate, and easy to work with.

In databases, cardinality describes the number of unique values in a column or the type of relationship between tables. It comes in two main contexts:

1. Column Cardinality – how many unique values a column contains
2. Table Relationship Cardinality – how rows in one table relate to rows in another table

Column Cardinality

Column cardinality tells you how unique the values in a column are.

Example:

• The ParkID column has high cardinality because each value is unique.
• The StateName column has lower cardinality because “Alaska” appears multiple times.

You can visually inspect a table to get an idea of column cardinality, or you can use the formula shown below to calculate the specific cardinality ratio to precisely measure the number of unique values in a column relative to the total number of rows in a table.

-- get total record count
SELECT COUNT(1) AS total_record_count
FROM schema_name.table_name;

-- get unique record count
WITH CTE_CountUnique AS (
	SELECT column_name,
		   COUNT(1) AS record_count
	FROM schema_name.table_name
	GROUP BY column_name
	HAVING COUNT(1) = 1
)
SELECT COUNT(1) AS unique_record_count
FROM CTE_CountUnique;

-- get total record count
SELECT COUNT(1) AS total_record_count
FROM schema_name.table_name;

-- get unique record count
WITH CTE_CountUnique AS (
	SELECT column_name,
		   COUNT(1) AS record_count
	FROM schema_name.table_name
	GROUP BY column_name
	HAVING COUNT(1) = 1
)
SELECT COUNT(1) AS unique_record_count
FROM CTE_CountUnique;

Formula: Cardinality Ratio = Number of Unique Values / Total Rows

• High Cardinality: Many unique values (ratio close to 1)
• Medium Cardinality: Moderate number of unique values
  • Example: StateName column in a U.S. parks table
• Low Cardinality: Few unique values (ratio near 0)
  • Example: Region column with only a handful of values

Why it matters:

• High cardinality columns (like IDs) are great for primary keys.
• Low cardinality columns (like a “Region” column) might be better suited for grouping, indexing, or joining tables.

Table Relationship Cardinality

Cardinality also applies to relationships between tables, which is key for database design. There are three main types:

1. One-to-One (1:1)

• Each row in Table A relates to exactly one row in Table B.
• Rare in practice, but useful when splitting sensitive information.

Example:
A table of parks and a table of park headquarters:

Parks Table

Headquarters Table

• Each park has exactly one headquarters.

2. One-to-Many (1:N)

• One row in Table A can relate to many rows in Table B.
• This is the most common relationship type.

Example:
A table of parks and a table of visitor centers:

Parks Table

VisitorCenters Table

• Yellowstone has multiple visitor centers, but each center belongs to only one park.

3. Many-to-Many (M:N)

• Rows in Table A can relate to many rows in Table B, and vice versa.
• Requires a junction table to model relationships.

Example:
A table of parks and a table of wildlife species:

Parks Table

Wildlife Table

ParkWildlife Table (Junction Table)

• Yellowstone has both Bison and Bald Eagles.
• Bald Eagles exist in multiple parks.

Why Cardinality is Important

Understanding cardinality helps you:

• Choose primary and foreign keys correctly.
• Avoid redundancy or inconsistent data.
• Plan joins and queries efficiently.
• Lay the foundation for normalization — knowing relationships ensures you break tables apart (or combine them) correctly.

Key Takeaways

• Column cardinality: How many unique values a column contains.
• Relationship cardinality: How tables relate — 1:1, 1:N, or M:N.
• Cardinality informs your database design decisions, indexing strategy, and query efficiency.

Understanding cardinality first makes data modeling decisions much easier, because you’ll know how tables should connect and where duplicates might appear.