All language subtitles for 10. Understanding Relationship Cardinality

It's time to talk about relationship cardinality now cardinality by definition refers to the uniqueness

of values in a column.

So for our purposes all the relationships that we're going to be creating with the data tables that

we have in our model should follow a one to many cardinality one instance of every primary key from

our lookups but potentially many instances of each foreign key from our data tables.

So looking at the screenshot here in this case we have one instance of each product key in that product

lookup table and you'll see that blue box around the number one since each row contains attributes of

a single product names skew price etc. on the data table side of that relationship because there are

many instances of each product key which is the foreign key here.

You'll see that the relationship terminates in an asterisk in that yellow box because there are multiple

sales associated with each product.

Now the other types of carnality that we're trying to avoid here are many too many or one to one.

So I thought it would be helpful to put a little case study together for each of those two to show you

why we'll be using one to many relationships.

Instead So our first carnality case study is many to many.

Now consider these two tables.

You've got a product lookup table in green on the left which has IDs names and Skewes and you've got

a data table in blue on the right which has dates product IDs and transactions.

So if you were to try to create a relationship between these two tables you'd get an error message that

looks something like this Barbie is going to tell you you can't create a relationship between those

two columns because one of the columns must have unique values.

And as you can see above we've got two instances of product ID number four in the lookup table and three

instances in the data table.

So now let's take a minute and step back and really think about this for a second.

Even if we had some magical way to force this relationship to stick it really just doesn't make sense

logically.

So we know that product number four was sold 12 times on January 1st nine times on the second 11 times

on the third.

But since idea Number four is associated with multiple product names.

How do we know how many of those sales or transactions were for Washington cream soda versus Washington

diet cream soda which for the record sounds like the worst beverage on the planet.

That's not relevant here.

But you get my point.

There's no way to make sense of this data with the tables in this kind of form.

Now let's move on to our second cardinality case study 1 to 1.

Now compared to many to many.

This one's not nearly as bad.

In fact you can create one to one relationships.

They're just a little bit inefficient.

So consider these two tables.

You've got a product look up on the left just like we showed before but without those duplicate ID number

four rows and then almost like a smaller product look up in orange on the right product IDs and product

prices.

So if we were to connect these two based on product ID we're creating a one to one relationship since

each ID only appears once in each table.

So again unlike many to many There's nothing illegal about this relationship.

It's just kind of an inefficient way to do things.

So to eliminate the inefficiency what you could do here is simply merge those two tables together into

a single valid lookup.

Now I know you're thinking I've been telling you this whole time never to do merging it's inefficient

use relationships instead.

But trust me in this scenario it's legit because we're still respecting the laws of normalization.

All of our roles here are unique and this merged blended table still captures attributes related to

the primary key.

So it still serves one single purpose.

So there's your cardinality crash course.

Now let's jump back to power be-I and make sure that our relationships follow a one to many cardinality

so back in our relationships you what we're looking for here is that the look up table side of each

relationship shows one and the data table side shows an asterisk.

One instance of the primary key in the lookup table many instances of the foreign key in the data table

so customer look up looks good calendar look up on order date looks good calendar look up on stock date

looks good territory is one too many product one too many.

And then we can follow our way right up the snowflake one too many one too many.

So it all checks out.

We've got a good set of one too many carnality relationships.

So we're in good shape.