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 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:
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.
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.