Database Normalization Explained: Normal Forms & Trade-offs

So yeah, let's start by basically a definition of what normalization is.

So it's basically a process of structuring your data in your tables of your database or restructuring it, changing a schema, basically.

It's usually done in a relational databases.

like most closely related to relational ones and it's done so we will decrease the amount of redundancy repeatable data to make our database easier to understand and to work with in some ways not in all ways but yeah to remove some anomalies when we modify our data so normalization helps with this Overall, it's just a process of changing and restructuring your tables.

And most usually it's related to splitting our tables to several tables.

We have, for example, one table with all the data.

And in the process of normalization, we move our data around and we create like two, three, four, five tables.

So the same data is spread around, so we structure it in such a way And there are kind of like grades of this restructuring And those grades or levels of this normalization are called normal forms I will go and describe what are all those normal forms a bit later But what's important to understand is that all this normalization really helps with decreasing anomalies of data modification What does it mean is that if you have a table with, I don't know, a data, some specific data which we have, and it's not normalized at all, or it's structured in an arbitrary way, so we just have it, the problem we will have when we will try to modify the data, maybe update it in some sort of way, that we will most likely encounter various anomalies, so-called.

Because table isn't structured that well, and it has a lot of repeated values, it has relationships which are not built in a proper way.

So we can encounter those anomalies, and there are three main types of anomalies.

I will go through them So one of them is called insert anomalies So it appears when we try to add some data Means that it's not really possible for us to, let's say, add information about some entity without adding information to some other entity which you're not expected to do.

It's done because the structure for a table isn't built correctly.

So you can imagine, for example, you can't register a teacher in a university without adding some course to this teacher.

Because it's all one table, let's imagine.

Yeah, you can't add a teacher because all teachers have to have a course attached to them.

But if he doesn't have a course, well, you can't add it.

So this is like insert anomaly, it's called.

and then updateAnomaly.

So updateAnomaly is closely related to redundancy of the data.

We have the same information, which is stored in the various rows of our table.

So it means that if this data is changed, we have to update it in all those rows instead of just one place You can imagine, for example, if you have a table of teachers in our university and all of those teachers have a specific department in this table where they work This actually means that if, for example, the department changes a name we will have to update it not in one place But in all the rows for all the teachers, right? Because we have a repeated department name for all the teachers there are in this table.

This is what update anomaly means.

So we can't just update it in one place.

We have to update it in all the places this name occurs.

And also there is an anomaly which is called delete anomaly.