In one of our blog post we covered on a high level what is a database and we gave you examples of databases that were normalized and are frequently used in operational databases (every day use databases).
In a separate article we will go into details of normalization process but in this post we will focus on three reasons for normalization in a database.
Before we go into reasons for normalization let's make it very clear who is interested in normalization. This is important because there are different types of databases and two main kind of databases are:
- Operational Databases (Online Transaction Processing - OLTP) - These databases are used every day by employees or customers and generally they are updated on row by row basis for example: An Employee may retrieve one customer's details (one row) and update one customer details (one row). These databases are generally used by large number of users that may potentially want to update the same records. OLTP databases are the most widely used databases but bear in mind that the reason behind it, is it's purpose (operational activities) and therefore they will be often many purpose specific databases. OLTP Databases are often critical from operations perspective (selling an air ticket) and are available for 99.9%-100% of the time.
- Data Warehouses (Supports Online Analytical Processing - OLAP) - This kind of database is actually the opposite of OLTP in many aspects. Data Warehouses integrate data from multiple sources (usually many OLTP databases but also excel files etc) into one centralized location. During integration process (commonly known as ETL) the data is cleansed (remove duplicates, typos etc) and business rules are applied (reject/accept, calculations etc). Data Warehouses are usually used by decision makers and analysts and they access set of rows (rarely single row) and they typically ask questions like how much did the company (or department) sale last month and the same time a year before. Most companies should have only one data warehouse and the database is critical for supporting tactical and strategic decisions (sometimes operational)
NOTE: When we talk about Data warehouses we mean Kimball Data Warehousing methodology which is the most common one. There are different ones where our arguments may not be applicable.
IMPORTANT: Normalization supports OLTP characteristics and objectives and you should always bear this in mind because Normalization almost never is used in Data Warehouse databases. You may see normalized Data warehouses but that is only because someone was not aware of negative consequences of doing that.
Below are three main reasons for normalization (from my point of view).
Reminder: These reasons are not applicable in Data Warehouses because they are handled differently and usage is different.
To get full access to this page
Become a member or Sign in
Membership is just $3.99/month