What Is Cardinality in Data Modeling? The Theory and Practice of Database Cardinality

What is cardinality in data modeling? And how do you implement cardinality in databases? This discussion uses simple, easy-to-follow examples to describe both the theory and modeling of cardinality in ER diagrams.

Cardinality is a mathematical term. It translates into the number of elements in a set. In databases, cardinality refers to the relationships between the data in two database tables. Cardinality defines how many instances of one entity are related to instances of another entity.

Let's take a look at the doctor consultation relationship found in a medical practice database. For example, a doctor can have one or many consults on a given day. Therefore, the relationship between the Doctor entity and the Consult entity is one doctor to many consults (a one-to-many relationship).

On the other hand, each consult is associated with exactly one diagnosis. So, the relationship between a Consult and a Diagnosis is a one-to-one relationship.

In other words, cardinality describes a fundamental relationship between two entities or objects. There are three relationship types or cardinalities: one-to-one, one-to-many, and many-to-many. Entity-Relationship (ER) diagrams are used to describe the cardinality in databases.

Optionality Within Cardinality

A concept related to cardinality is optionality. Optionality represents whether an entity on one side must be joined to an entity on the other side.

For instance, each consult must be associated with a doctor. The Doctor side of the Consult-Doctor relationship is mandatory. On the other hand, a doctor may have zero consults (for example, because he hasn’t started working yet). The Consult side of the Consult-Doctor relationship is optional.

As described in detail below, the mandatory nature of cardinality is represented by a vertical line or segment (|) on the mandatory side(s) of the horizontal line connecting the two entities. Optionality is denoted by an empty circle on the optional side(s) of the line connecting the two entities.

The Three Relationship Types or Cardinalities

1. The One-To-One Relationship

The one-to-one (1:1) relationship defines the fact that one row in a database table relates to exactly one row in a second table. In an ER diagram, 1:1 means that one occurrence of an entity is related to one event in a second entity.

Examples of the 1:1 relationship include student to student contact details, country or state to capital city, and person to social security or identity number.

The 1:1 relationship is notated in an ER diagram with a single line connecting the two entities. In our scenario, the line connects the Student entity to the Student Contact Details entity.

As an aside, the many-to-one and many-to-many relationships are denoted using single lines and a crow’s foot symbol.

The two perpendicular lines (|) indicate a mandatory relationship between the two entities. In other words, the student must have contact details, and the contact details must have a related student.

Optionality is notated by an empty circle. In this scenario, the Student entity does not have to have a corresponding Student Contact Details record.

2. The One-To-Many Relationship

The one-to-many (1:N) relationship is the most common database relationship. It is used to indicate the relationship between the majority of tables found in a relational database.

In summary, the one-to-many relationship means that one row in a database table relates to many rows in a second table. It is also known as a Primary Key-Foreign Key relationship because it uses primary keys and foreign keys to enforce this relationship.

There are innumerable instances of a 1:N relationship, including a student to subjects, courses or degrees to a student, and a sales invoice to invoice transactions.

In an ER diagram, the cardinality of one-to-many is notated with a line joining the two entities. The connectors reflect the different characteristics of this relationship. The single vertical line (on the left side of this relationship line) indicates that this connector only has one row affected by this relationship.

The crow’s foot with an open circle indicates that this connector has many rows influenced by this relationship. The open circle indicates optionality. In other words, there does not have to be a student enrollment record linked to a course.

3. The Many-To-Many Relationship

The many-to-many (M:N) relationship means that many rows in one table are related to many rows in a second table. In other words, many instances in one entity correlate with many instances in a second entity. For example, a student can sign up for many classes, and a class can have many students signed up.

It is slightly more difficult to model a cardinality of many-to-many. A direct many-to-many relationship between these two example entities is not possible.  o, a cross-reference table is required to convert this relationship into two one-to-many relationships.

As with the one-to-many relationship described above, the relationship between two entities is indicated by a line between them. The connectors on each end describe the nature of this relationship.

The single vertical line (|) on the Students entity side indicates that the connector only has one row affected by this relationship. And the crow’s foot on the other side of the line shows that this relationship influences multiple rows.

The middle table (ClassStudent) consists of two primary/foreign keys, one of which is the primary key for the Students table and the other the primary key for the Classes table. Therefore, there has to be a Student ID and a Class ID for each row in the Class-Student table. Because these elements of the ClassStudent table are also primary keys of the entity on each side of it, each element has to exist in the Students and Classes tables, respectively.

Final Thoughts

The role that cardinality plays must not be underestimated when defining the relationships between business objects or database entities represented as database tables and columns. In summary, the three cardinalities (the one-to-one, one-to-many, and many-to-many relationships) are fundamental to describing the relationships between different objects or entities.

Additionally, optionality is essential to the data modeling process, from start to finish. Both sides of the relationship between two entities can either be optional or mandatory. Consequently, it is essential to model the optionality of the relationship within the cardinality construct.

Lastly, to come full circle, data modeling tools simplify the critical database abstraction and design process. They ensure an adequately modeled and designed database, a crucial element of the modern data pipeline and data architecture development process.