All language subtitles for 3. Principles of Database Normalization

All right.

Time to talk about something really really important called normalization.

Now this is a tricky one to wrap your head around at first so I encourage you to re watch this video

as many times as it takes until it starts to really stick.

So by definition normalization is the process of organizing the tables and columns in a relational database

to reduce redundancy and preserve data integrity.

So a lot of fancy words they're kind of tough to understand what that really means.

But basically it's used to do three different things.

Number one eliminate redundant data which helps to decrease table sizes and more importantly reduce

processing speed and improve efficiency.

Number two helps us minimize errors and anomalies when we make data modifications.

So if we're to insert or update or delete records in our database.

And number three it helps simplify queries and structure the database in a way that enables meaningful

useful analysis.

So still feels kind of over complicated.

If you asked me.

So my tip to remember what normalization is all about is to think of it this way.

In a properly normalized database every table should serve it distinct and specific purpose.

So you might have one table that only gives you information about products you have another that only

gives you information about dates like a calendar table.

You might have one that's only daily Transactional Records and another that's only about customers.

Now this should sound pretty familiar because these are the exact type of tables that we're using here

in this adventure works demo.

So let me take a stab at visualizing why normalization is such an important concept consider a table

like this.

You've got transaction quantities here in the third column broken down by product ID and by date as

well as all of this extra information about each product ID the brand the name the skew and the weight.

And as you can see just from this small sample that we have multiple transactions or multiple quantity

values per day and multiple quantity values per product ID.

So this table is not normalized.

It doesn't serve a single unique purpose.

It's actually serving at least two purposes one providing the transaction quantity by date and product

ID and to providing additional attributes about those products.

Those are two different purposes.

So what you end up with here are all of these duplicate rows.

In any case where the same product ID appears more than once.

So you see duplicate brand names product names duplicate Skewes and product weights.

And you might be wondering OK that's not that big a deal.

I'm still getting the information that I need in fact to have it all in one place in a single table

which is great.

So I don't see the downside here.

Well imagine if we were dealing with 100 different products and each of those products on average sold

10000 times a day.

Now all of a sudden you're talking about a million duplicate rows for every single date in the data

set.

So you can see that with larger more complex models minor inefficiencies like this can become major

major problems.

As you scale up in size.

So the way to avoid issues like this is to strip those product attribute columns out of this table and

create a relationship with a single product Look-Up.

And if that product look up contains a unique list of product IDs with those associated attributes then

we can access the exact same information here.

Well eliminating every one of those duplicate rows.

So again this concept may still feel a little bit ambiguous but trust me we're going to get our hands

dirty.

We're going to do a ton of demos walk through a bunch of samples and this is going to start to feel

much more natural as we continue through this section of the course.

So next up we're going to talk about data tables and look up tables as our first step towards building

a properly normalized model.