Cardinality

What Does Cardinality Mean?

The term “cardinality” in database design has to do with counting tables and values. With that said, cardinality has three main definitions. It can relate to counting the number of elements in a set, identifying the relationships between tables, or describing how database tables contain a number of values, and what those tables look like in general.

Cardinality between tables can be one-to-one, many-to-one or many-to-many.

Techopedia Explains Cardinality

We'll start with the easiest definition of cardinality – cardinality in mathematics. In mathematical terms, cardinality means simply counting the elements in the set.

If you count the number of unique items in the database column, that's a type of cardinality.

Data Cardinality

In database design, cardinality also can represent the relationships between tables. These relationships include one-to-one, one-to-many, or many-to-many.

Let's take a common “child/parent” example of database modeling and dependencies.

Suppose you have each individual customer of a small business as a unique record in a database table.

In another database table, you have records of each individual's customer's possessions. Let's use the example of an identity card for a one-to-one relationship.

In that setup, each customer has one identity card, and that card is linked to the customer through a one-to-one cardinality model between database tables. So every search can target the specific single card held in the secondary database table according to the subject, the individual customer.

Now, let's change that and suggest that instead of an identification card, the customer has a record of his or her purchases in a secondary database table. For each one customer, there will likely be a number of purchases. So in these auxiliary tables, the one customer’s link to the multiple purchases is a one-to-many cardinality relationship.

High and Low Cardinality

In some ways, this last definition of cardinality is the most common. Professionals will often talk about a database table in terms of it having high or low cardinality. Here they’re characterizing the contents of the database table in general.

High cardinality means that most of the values in that database table column are unique. There's not a lot of repetition. This happens when most of the transactions or identified elements are unique from each other in some way.

Low cardinality, on the other hand, means that many of these values in this database table column are repeated. You may have a few individual values that are common to most of the transactions or other elements that are being described. So you have a lot of repeated elements. That's low cardinality, because you're not making a lot of the counts as you go through the column content.

Assessing Relative Cardinality

Many IT professionals, and database administrators in particular, will use various kinds of notation or figuring to show cardinality relationships or high or low cardinality characterizations. This can be a key part of database design and maintenance over time, as professionals look at formatted and available data assets.

High cardinality generally means there is better unique information in each entry, where low cardinality may make a database table less valuable overall, or present opportunities for compression.

Essentially, measuring cardinality is a good part of figuring out how to manage a data asset.