What Is Cardinality in a Database? (With Types and Examples)

Updated March 10, 2023

When developing new software programs and databases, there are several modeling concepts you can apply to provide structure and make information easy for users to find. Cardinality is one of these concepts, and it helps users describe how two different objects or entities relate to each other. Understanding what cardinality in a database is can help you organize a business's data and increase its efficiency at distributing information.

In this article, we explain cardinality in a database, why it's important and what the three types of cardinality are.

What is cardinality in a database?

Cardinality is a mathematical term that refers to the number of elements in a given set. Database administrators may use cardinality to count tables and values. In a database, cardinality usually represents the relationship between the data in two different tables by highlighting how many times a specific entity occurs in comparison to another. For example, the database of an auto repair shop may show that a mechanic works with multiple customers every day. This means that the relationship between the mechanic entity and the customer entity is one mechanic to many customers.

In this example, note that each customer has exactly one vehicle that they bring to the auto repair shop during their visit. This means the relationship between the customer entity and the car entity is a one-to-one relationship. Using cardinality can help database administrators automatically establish these relationships in a software program or database. This can make it easy for users to see the correlation between mechanics, customers and cars when searching for specific data or files.

Why's cardinality important in databases?

Cardinality is important in databases because it creates links from one table or entity to another in a structured manner. This has a significant impact on the query execution plan, which is a sequence of steps users can take to search for and access data within a database system. Having a well-structured query execution plan can make it easier for users to locate the data they need quickly. Database administrators can apply cardinality to databases for various reasons, but businesses typically use the cardinality model to analyze information about their customers or inventory numbers.

For example, an online retailer may have a database table that lists each one of its unique customers. It may also have another database table that lists all the purchases customers have made from its store. Since it's likely that each customer purchased multiple items from the store, the database administrator may represent this pattern by using a one-to-many cardinality relationship that links each customer in the first table to all the purchases they made in the second table.

Types of cardinality in databases

There are three types of cardinality that may apply to a database. These three types are one-to-one relationships, one-to-many relationships and many-to-many relationships. Here are definitions and examples for each type of cardinality:

One-to-one relationship

A one-to-one (1:1) relationship describes a situation where one occurrence of an entity relates to exactly one occurrence of another entity. You might see this type of cardinality in a database if you're working with one row in a specific table that relates to one row in a different table. Most database administrators agree that this is the least common type of relationship that occurs in databases.

Example: A school may use cardinality in its student database to show a one-to-one relationship between each student and their student ID number. The school only assigns one ID number per student. As a result, using this modeling concept can help faculty members look up a student's ID number quickly if they need to create a replacement card or use it to access a specific file.

One-to-many relationship

A one-to-many (1:N) relationship describes a situation where one occurrence in an entity relates to many occurrences in another entity. You might see this type of cardinality in a database if you're working with one row in a specific table that relates to multiple rows in a different table. The one-to-many relationship is the most common type of relationship, as you can use it to store data in any relational database.

Example: An online food delivery service may create a data table to store all its customer ID numbers. It may also create another table for unique order ID numbers. Each order ID number must correlate with the specific customer ID number of the person who placed the order. Many order ID numbers may relate back to a single customer ID number. A database developer can use cardinality to establish this as a one-to-many relationship to make it easy for other team members to connect each customer with all of the orders they've placed.

Many-to-many relationship

A many-to-many (M:N) relationship describes a situation where multiple occurrences in one entity relate to multiple occurrences in another entity. You might see this type of cardinality in a database if you're working with several rows in a specific table that relate to several rows in another table. Many-to-many relationships can be confusing to study, so some database administrators can opt to divide a many-to-many relationship into several one-to-many relationships.

Example: A book retailer may use the many-to-many relationship model to manage its online database. It might include a list of book titles in one table and a list of author names in another table. Many of the authors may have multiple books and some of the authors may have even co-written books together. This means that a single author may have a relationship with more than one book and a single book may have a relationship with more than one author. By using the many-to-many relationship model, the retailer can quickly assess which authors and books belong together.

Get interview-ready with tips from Indeed

Prepare for interviews with practice questions and tips

Frequently asked questions about cardinality in databases

Here are some answers to frequently asked questions about cardinality in databases:

How's cardinality different from modality?

While cardinality and modality are both modeling concepts that professionals use in database design to analyze entities and their relationships with each other, there are some key differences between these two methods. Cardinality measures the maximum number of associations between two different table rows or columns. Alternatively, modality represents whether a relationship between two or more entities exists at all. In other words, modality focuses on the minimum number of associations, whether a relationship is mandatory and if the relationship is null.

Related: What Is Data Modeling?

What's an entity-relationship (ER) diagram?

An ER diagram visually represents the cardinality that a specific database contains. An ER diagram often appears as a flowchart that shows how different entities in a database relate to one another. A database analyst may use an ER diagram to design, upgrade or troubleshoot a relational database system. ER diagrams are beneficial because they can help database analysts and system users quickly identify where the data's location and how it links together.

What's the difference between high and low cardinality?

High cardinality describes a data set that has a large number of unique values or entities. This represents a significant level of diversity and very little repetition. For example, a data set that lists the name of each unique customer would have high cardinality because the names are likely to vary.

Low cardinality refers to a data set that has a large quantity of the same values or entities. In a low cardinality data set, many of the same entities repeat themselves and there's less variety. For example, a data set that lists the category of each product for a small retail store may have low cardinality because there are only a few categories that are likely to repeat.