Data Cardinality

This is a normal term for database modelers, but business users are not very familiar with it. Understand this and it will solve your 90% data problems. Don’t have good Data Model and you must deal with Chasm and Fan Traps.

Data Cardinality is about maintaining the level of details in a table. There are 2 types of Data Cardinality.

· High Cardinality: It is when there are no or less duplicates in a table. Tables with Pkey are mostly in Highest Cardinality mode.

· Low Cardinality: It is when there are high number of duplicates in a table. Tables with Fkey are mostly in Low Cardinality mode.

For example, Product table with details like Product ID, Product Name, and Product Manufactured Date etc. It can contain hundreds of products, but at the same time, a particular product cannot be repeated as it’s already in the table right. So, now we can say this table has High Cardinality. Referring to the same example if Product is there then one must sell it too. Sales of the products can be stored in a Sales table. Now, 1 type of product can be sold many times right. So, Sales tables can contain thousands of rows where hundreds of rows can be for 1 product means Product ID will be repeated in this Sales table so we can say this table is in Low Cardinality with respect to Products.

We have discussed Data Modeling in a separate topic where we normalize the data up to the level where there is no data duplication. The basic concept of normalization is dependent on Cardinality. Once, we break the denormalized tables into multiple tables to reduce duplicate records, the main table hosts High Cardinality data, in other words becomes the primary table and we know primary tables got Pkey (Primary Key).

‘All the primary key tables are High Cardinality tables and Fkey (Foreign Key) tables are Low Cardinality tables.’

Don’t be carried above 😊, as tables having Fkey can also have Pkey, and it can become the Primary table for another foreign key table. So, we can’t say all Pkey tables have absolute Highest Cardinality.

There are 3 degrees of Cardinality.

· One to One (1:1): Single row in table A relates to Single row in table B.

· One to Many (1:M): Single row in table A relates to Many rows in table B.

· Many to Many (M: N): Many rows in table A relate to Many rows in table B.

This concept is also very important when someone joins tables using no Pkey and Fkey direct relationships; using wrong columns to join can end up in Cartesian data (explained in separate topic).

Understanding Cardinality is also very important in terms of impact analysis. Whenever there is change in the table structure due to any business requirement, the first question one should ask is whether this change will impact the Cardinality of any table. If yes, then Data Lineage will help you see the end-2-end impact for which Metadata Management must be in place.