Organizations have different data storage needs, and it is important to understand and differentiate transactional and analytical databases by their use. Each type of database has a different purpose and a different design.
Transactional databases emphasize correct and consistent data while keeping the speed of the operations in the database. These types of databases focus on time sensitivity. The database user will generally interact with a single row of the database, which can be displayed and updated very quickly and consistently.
EXAMPLE
Purchases on a website or shipments of inventory need to have data reflected in the database in real time for day-to-day operations. These transactions have to be recorded accurately and immediately. This type of database is called an operational database, an online transaction process database, or a production database.Transactional databases constantly have insert, update, delete and select statements running so the data is transitioning all of the time. All of these transactions have to execute quickly to provide responses to the end users as they need them. The results should be immediately displayed when the user runs one of the commands in the database. Since these databases change often, data integrity is crucial. As such, the data is normalized in third normal form. You will get into the detail of what that means in an upcoming tutorial.
Analytical databases, on the other hand, focus on historical data and business metrics. They allow for data manipulation and analysis to help produce information that organizations need for things like sales forecasts or market strategies. These analytical databases allow the end user to perform complex analysis of the data using complex applications.
Analytical databases generally consist of two main components: the data warehouse and the online analytical processing front end. The data warehouse is a specialized type of database that stores the underlying data in a format that’s optimized for decision support, rather than transactions. As such, typically data is loaded from transactional databases or other sources and mostly with select statements. It’s rare to see any update, insert or delete statements, as the data has already been verified and validated against when it has gone into the transactional database. Online analytical processing (OLAP) generally contains a set of tools that are used to process that data from the data warehouse.
Analytical databases are used to help with business intelligence, by generating information that is used for business decision-making. The volume of data in these analytical databases can be massive due to the historical data and the data sets that may be added from third parties. Since the data does not often change, space is not as much of a concern. You will see more data redundancy by denormalizing the database, since the focus is on performance.
Source: Authored by Vincent Tran
