Database and OLTP

In the current era there are many datastores like Database, Data Warehouse, Data Marts, Big Data, Data Lake, Data Vault, Business Vault, SQL, and NoSQL etc. Use of all these terms is different with their own horizon, their own skill sets whereas tools and languages can be the same for all of these.

Let's decode it….

OLTP is on one side of the line and Data Warehouse/ Data Marts and Big Data/ Data Lake etc. are on the other side. Normally, databases are called OLTP (Online Transactional Processing) whereas Data Warehouse/ Data Marts fall under the term OLAP (Online Analytical Processing).

As far as Big Data/ Data Lake is concerned, Data World has yet to tag these with either OLTP or OLAP but for sure these are not meant for OLTP. At the same time NoSQL is stepping in OLTP zone

‘OLTP or OLAP both are Databases as both are RDBMS (Relational Database Management Systems)’

In nutshell, Database or Data Warehouse or Data Marts or Big Data or Data Lake etc., all these are not more than just Data Storages rather EMPTY Data Storages.

Image: https://intellipaat.com/blog/what-is-database/

Database: A medium to store structure data is called Database.

OLTP: OLTP (Online Transaction Processing) is to store business transactions in real-time with insert, update and delete capability with ACID feature.

This is the oldest and most used medium in every era till this point-in-time. One way or the other, everyone is playing its part to generate data either via mobiles, tablets, applications, IT solutions, online meetings, WhatsApp calls, Facebook, Tweeter, Instagram, TikTok etc. In other words, whatever you are doing in normal life, it is generating data and this data is being stored in databases. In databases, we have a concept of tables, rows, and columns. Data sitting in tables can be joined with each other using relations and that is why these are called RDBMS (relational database management systems).

In the OLTP world, we make sure there is no duplication of data and that is called the Normalization concept. There are many normalization forms, but most data models are normalized up to 3NF (3rd Normal Form). In 3NF, data modelers keep breaking tables till the time all the non-key columns are dependent on key columns.

As mentioned above Databases are the oldest medium to store and retrieve data. As time passed, the data size in OLTP started to grow exponentially and read-write operations started getting slower & storage started to become a bottleneck. As storage and processing power of computers in that era before the 90(s) was a big challenge and expensive as well, organizations started to offload data in Tape drives but with this came the challenge of decision making on the historical data. Later, when storage became cheaper, organizations started keeping more and more historical data in their OLTP systems but as these systems were for both insertion and extraction there came the challenge of table locking etc. To resolve this issue, organizations started to make a duplicate copy of their OLTP systems, one for insert/ update/ delete and one for extraction for decision making.

This gave a free hand to businesses to store as much historical data as possible in the extraction version of OLTP systems. This way the business wish list started to grow from a few KPI(s) to 100(s) of KPI(s).

This came with a challenge to extract data within the given SLA. As the OLTP data model was designed in normalization (3NF most) form where tables were broken down into as many as possible. To extract data, the query had to search 10(s) or 100(s) of tables using joins which started to slow down the process from a few minutes to 10-20 hours to run one query. This Normalization model was not fulfilling the business requirement and started to become a bottleneck for Data extraction for DSS. To mitigate this issue, Bill Inmon came up with a solution called Data Warehouse which would replace the extraction version of OLTP, next topic, for reporting and analysis.

Cheers.