How are companies like Amazon, Google, Facebook getting their data? A simple answer is through user interface(UI). Users put informations in an application (eg. creating new gmail account) and data gets stored in the back-end database. Users are capable of changing data whenever they want. The database holding this application data can be updated, inserted and deleted anytime. This type of database is called Application Database. There are other several type of databases like:
- Relational Database -Data relationship are tabular in nature. Eg. Oracle, Teradata, SQL SERVER etc
- Object-Oriented Database – Apart from data, O-OD can handle things like photographs and videos.
Here’s a simple question. What is the main goal of Application Database? Fast access to data. The tables in this type of database are normalized and is great for OLTP ( On-line Transcriptional Processing). Denormalized tables on the other hand are great for OLAP (On-line Analytical Processing). Let’s quickly differentiate normalization/denormalization and OLAP and OLTP.
OLAP vs. OLTP
OLTP | OLAP |
Running day to day business activities. | Great for problem Solving, Reporting, budgeting, forecasting etc. |
Highly normalized. | Typically de-normalized |
Backups are very important. Data loss is like losing a house & wife and paying for child support. Day to day will be a hell. | Data can be simpy reloaded. Kind of like breaking up after two months of relationship. You will find your way. |
Normalization vs. Denormalization
Normalized Database | Denormalized Database |
Optimized for inputting faster. Typically 3NF form used. | Optimized for reading faster. Eg – Star Schema, Materialized View and Cubes |
SELECT, INSERT and UPDATE statements are faster. | Depends. |
Require Joins. Indexing not possible. | No joins. Since data is aggreaged SELECT statements are fast and indexing can be used. |