What is OLTP and OLAP? Why is it important in Database Design?

There are two kinds of database depending on processing: transaction based and analytical based. So before starting database design, first we must analyze the nature of the application what we are designing for, is it Transactional based database or Analytical based database. 

OLTP 

OLTP stands for Online transaction processing which deals with the operation in a system with a lot of short transactions on-line. In this kind of application, end user is more interested in CRUD, i.e., creating, reading, updating, and deleting records. 

An example of an OLTP system is an ATM center. Assume that a couple has a joint account with a bank. One day both simultaneously reach different ATM centers at precisely the same time and want to withdraw the total amount present in their bank account.

However, the person that completes the authentication process first will be able to get money. In this case, OLTP system makes sure that the withdrawn amount will be never more than the amount present in the bank. The key to note here is that OLTP systems are optimized for transactional superiority instead of data analysis.

Other examples of OLTP system are:

1. Online banking
2. Online airline ticket booking
3. Sending a text message
4. Order entry
5. Add a book to shopping car

OLAP 

 OLAP stands for Online analysis processing which deals with historical data with low volume of transactions. In these kinds of applications your end user is more interested in analysis, reporting, forecasting, etc. These kinds of databases have a less number of inserts and updates but Queries are quite complex and data is stored in multi-dimensional schemes and is aggregated. Its processing speed depends upon the amount of data involved. The main intention here is to fetch and analyze data as fast as possible. 

Any Data warehouse system is an OLAP system. Uses of OLAP are as follows

A company might compare their mobile phone sales in September with sales in October, then compare those results with another location which may be stored in a separate database.

Amazon analyzes purchases by its customers to come up with a personalized homepage with products which likely interest their customer.

In a word, we can say, the primary objective of OLTP is data processing and not data analysis and the primary objective of OLAP is data analysis and not data processing.

Key difference between OLTP and OLAP

• OLTP is an online transactional system and manages database modification. OLAP is an online analysis and data retrieving process.
• OLTP is characterized by large numbers of short online transactions. OLAP is characterized by a large volume of data.
• OLTP is an online database modifying system. OLAP is an online database query management system.
• OLTP uses traditional DBMS. OLAP uses the data warehouse.
• OLTP does Insert, Update, and Delete information from the database. OLAP does Mostly select operations
• Tables in OLTP databases are normalized. Tables in the OLAP database are not normalized.
• OLTP and its transactions are the sources of data. Different OLTP databases become the source of data for OLAP.
• OLTP database must maintain data integrity constraint. OLAP database does not get frequently modified. Hence, data integrity is not an issue.
• OLTP's response time is in millisecond. Response time in seconds to minutes.
• The data in the OLTP database is always detailed and organized. The data in OLAP process might not be organized.
• OLTP helps to control and run fundamental business tasks. OLAP helps with planning, problem-solving, and decision support.
• OLTP allows read/write operations and Queries in this process are standardized and simple.. OLAP allows Only read and rarely write and Complex queries involving aggregations.
• OLTP need complete backup of the data combined with incremental backups. OLAP only need a backup from time to time. Backup is not important compared to OLTP
• DB design is application oriented. Example: Database design changes with industry like Retail, Airline, Banking, etc. DB design is subject oriented. Example: Database design changes with subjects like sales, marketing, purchasing, etc.
• OLTP is used by Data critical users like clerk, DBA & Data Base professionals. OLAP is used by Data knowledge users like workers, managers, and CEO.

Why is it important in Database Design?

 Many developers use OLTP design and normalized tables without thinking about how much data store in database or character of application , how many , what kind of report they need , especially in financial systems. We see many developers by default applying normalization rules without thinking about the nature of the application and then later getting into performance and customization issues. 

Since inserts, updates, and deletes are prime focus in OLTP, normalized table design must be considered for better performance. On the other hand, analysis, reporting, forecasting as prime focus in OLAP, flat demoralized database structure is suitable for that.