All language subtitles for 003 Database Normalization_en

Instructor: All right, let's talk about a concept

called normalization.

Now, this is a really important topic.

It's gonna help you grasp exactly why

and how data models actually work,

but it can be a little bit tricky

to wrap your head around at first.

So the textbook definition here

is that normalization is the process

of organizing the tables and columns

in a relational database

to reduce redundancy and preserve data integrity.

So what the heck does that even mean, right?

Well, it's commonly used to do three different things.

First, to eliminate redundant data in a model,

to decrease your table sizes, and more importantly,

improve processing speed and efficiency.

It's also used to minimize errors and anomalies

when you do things like insert, update, or delete,

or otherwise modify data inside of your model.

And third, it helps simplify queries

and structure your database for meaningful analysis.

And we'll see examples of this

as we start writing DAX queries and measures

later on in the course.

So at this point, it still feels a little bit vague, right?

A little ambiguous.

So the way that I like to wrap my head around

what normalization really means

is to think of it this way,

in a normalized database,

each table should serve one distinct

and specific purpose and one purpose only.

So you may have a table that only contains information

about individual products.

You might have another table

that only contains daily transaction records,

another that's a calendar table

with information about dates,

and maybe some others that contain attributes

about your customers, or your stores, and so on.

And these should sound pretty familiar

because these are the exact tables

that we'll be working with in the project

that we work through throughout this course.

So let me take a stab at kind of bringing this to life,

and visualizing the concept of normalization a bit.

Imagine a table that looks like this.

You've got transactional records here, right?

You've got dates of transactions,

product IDs that were sold, quantity of goods sold,

but you also have product specific attributes

stored here as well,

like the product brand, the product name,

the SKU, and weight,

and because you have multiple transactions per day,

and multiple transactions per product,

you end up with a bunch of redundant duplicate fields

or rows here.

In other words, every time a specific product ID is sold,

we're storing the same set of information

about the brand, the name, the SKU, and the weight

in row after row after row.

And you might be looking at this and saying,

"You know, what's the big deal?

Like, I've got all the information that I need

for my analysis.

I've got it all in one place.

So that's great, right?

But suppose that instead of a handful of records,

like we see here, you've got a hundred different products

that sell an average of 10,000 times a day.

Now, we're talking about upwards of 1 million duplicate rows

for each date in the table.

So the point is that minor inefficiencies

like this can very quickly become major, major problems

as you scale.

And the way that we would solve a problem like this

is to normalize the data, right?

We'd take all those product specific attributes,

brand names, SKU, and weight,

and we would store those

in its own separate table containing one row,

or one unique record for each product ID

that would essentially transform this one table

into two tables that each serve one purpose.

You'd have transactions with date, product ID,

and quantity in one table.

You'd have products in a second table,

and you could use a relationship between those

based on the product ID field

to access the exact same information

without any of the redundant data.

So hopefully that helps,

we're gonna walk through a ton of additional demos

and examples to help really hammer this point home,

but that's our introduction to the concept

of database normalization.