Default Cardinality

[former_member4998]

Hi All..

I am explaining importance of cardinality setting between the join and changing the cardinality impact.

• Cardinality is a property of a join that describes how many rows in one table match rows in another table. and it's expressed as the minimum and maximum number of rows in a column at one end of a join that have matching rows in the column at the other end of the join. The minimum and the maximum number of row matches can be equal to 0, 1, or N.

• If joined two tables (Customer and City) without defining the cardinality and to know what is the default cardinality consider the join . And if we change the cardinality  will it impact the results.

Below are the Data Base tables:

     Customer Table Data                                                             City Table Data

Cardinality Un-Know

Joined two tables and Specified the Cardinality as Un-know and returned total number of rows are 12.

Cardinality one-to-one (1,1)

Joined two tables and Specified the Cardinality as one-to-one (1,1) and returned total number of rows are 12.

Cardinality one-to-many (1,N)

Joined two tables and Specified the Cardinality as one-to-many (1,N) and returned total number of rows are 12.

Cardinality many-to-one (N,1)

Joined two tables and Specified the Cardinality as many-to-one (N,1) and returned total number of rows are 12.

Cardinality many-to-many (N,N)

Joined two tables and Specified the Cardinality as many-to-many (N,N) and returned total number of rows are 12.

So cardinality with Un-Know or one-to-one (1,1) or one-to-many (1,N) or  many-to-one (N,1) or many-to-many (N,N)  will get the same results. And cardinality of a join does not have a role in the SQL generated when you run a query.

But we need to defining cardinality for all the joins in data foundation to detect the context. which are mainly detected by the CARDINALITIES. So as per best practices cardinalities are set according to the key status of the column in the two tables for all the joins in data foundation as follows

Hope this helpful.

Thanks,

Sreeni