Transaction Processing vs. Decision Support

Many applications fall into two main categories of database applications:

The characteristics of these application types have a dramatic effect on the design considerations for a database.

Transaction Processing

Transaction processing database applications are optimal for managing changing data, and usually have a large number of users who will be simultaneously performing transactions that change real-time data. Although individual requests by users for data tend to reference few records, many of these requests are being made at the same time. Common examples of these types of databases are airline ticketing systems and banking transaction systems. The primary concerns in this type of application are concurrency and atomicity.

Concurrency controls in a database system ensure that two users cannot change the same data, or that one user cannot change a piece of data before another user is done with it. For example, if you are talking to an airline ticket agent to reserve the last available seat on a flight and the agent begins the process of reserving the seat in your name, another agent should not be able to tell another passenger that the seat is available.

Atomicity ensures that all of the steps involved in a transaction complete successfully as a group. If any step fails, no other steps should be completed. For example, a banking transaction may involve two steps: taking funds out of your checking account and placing them into your savings account. If the step that removes the funds from your checking account succeeds, you want to make sure that the funds are placed into your savings account or put back into your checking account.

Transaction processing system databases should be designed to promote:

Good data placement. I/O bottlenecks are a big concern for OLTP systems due to the number of users modifying data all over the database. Determine the likely access patterns of the data and place frequently accessed data together. Use filegroups and RAID (redundant array of independent disks) systems to assist in this.
Short transactions to minimize long-term locks and improve concurrency. Avoid user interaction during transactions. Whenever possible, execute a single stored procedure to process the entire transaction. The order in which you reference tables within your transactions can affect concurrency. Place references to frequently accessed tables at the end of the transaction to minimize the duration that locks are held.
Online backup. OLTP systems are often characterized by continuous operations (twenty-four hours a day, seven days a week) for which downtime is kept to an absolute minimum. Although Microsoft® SQL Server™ can back up a database while it is being used, schedule the backup process to occur during times of low activity to minimize effects on users.
High normalization of the database. Reduce redundant information as much as possible to increase the speed of updates and hence improve concurrency. Reducing data also improves the speed of backups because less data needs to be backed up.
Little or no historical or aggregated data. Data that is rarely referenced can be archived into separate databases or moved out of the heavily updated tables and into tables containing just historical data. This keeps tables as small as possible, improving backup times and query performance.
Careful use of indexes. Indexes must be updated each time a row is added or modified. To avoid overindexing heavily updated tables, keep indexes narrow. Use the Index Tuning Wizard to design your indexes.
High hardware configuration to handle the large numbers of concurrent users and quick response times required by an OLTP system.

Decision Support

Decision-support database applications are optimal for data queries that do not change data. For example, a company can periodically summarize its sales data by date, sales region, or product and store this information in a separate database to be used for analysis by senior management. To make business decisions, users need to be able to determine trends in sales quickly by querying the data based on various criteria. However, they do not need to change this data. The tables in a decision-support database are heavily indexed, and the raw data is often preprocessed and organized to support the various types of queries to be used. Because the users are not changing data, concurrency and atomicity issues are not a concern; the data is changed only by periodic, bulk updates made during off-hour, low-traffic times in the database.

Decision-support system databases should be designed to promote:

Heavy indexing. DSS systems have low update requirements but large volumes of data. Use many indexes to improve query performance.
Denormalization of the database. Introduce preaggregated or summarized data to satisfy common query requirements and improve query response times.
Use of a star or snowflake schema to organize the data within the database.