All about Cardinality

As we are aware of the fact and dimension table, it’s important to understand the relationship between the tables. It is where the cardinality comes into the picture.

Cardinality is the directionality of a relationship, such as one-to-many, one-to-one, and so on. It describes the directionality between fact and dimension tables.

Four Types of Cardinality:

Many to one (N*:1):

A many-to-one relationship is the most common, default type of relationship. It means the column in a given table can have more than one instance of a value, and the other related table.

It is often known as the lookup table, has only one instance of a value.

For example, a project can have more than one student working on it. A team of five students in a college in assigned a project that they need to complete in let us say one month.

One to many (1:N*):

In a one-to-many relationship, the column in one table has only one instance of a particular value, and the other related table can have more than one instance of a value.

For example, one individual student working on 2 projects at a time would be considered as a One-to-Many relationship.

One to one (1:1):

In a one-to-one relationship, the column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value.

For example, in the below example, the product table has a Product Id that is distinct. Similarly, the productdetails table which is the subset of the Products table has Product Id.

Many to many (N*:N*):

Many to Many describes a relationship where many values are in common between two tables.

you can establish a many-to-many relationship between tables, which removes requirements for unique values in tables. However, it is not recommended to use.

The reason behind this is lacking unique values in the table brings ambiguity and the users will not know which column of values is referring to what.

For example, refer the order and sales table.

The above figure shows a many-to-many relationship between the Sales and Order tables on the OrderDate column.

Because multiple sales can have multiple orders associated with them. Ambiguity is introduced because both tables can have the same order date.

This concept of cardinality is important to understand as you will be creating relationships between the tables.

Hope you found the post informative. Your valuable feedback, question, or comments about this post are always welcome by leaving me message on  contact form is truly appreciated.