- Logical and Physical Models
- Database Application Types
- Using Efficient Datatypes
- Partitioning Large Tables
- Bigger, Better, Faster
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Database Application Types
Most database applications can fit into one of three categories:
Online transaction processing (OLTP)
Decision support system (DSS)
Data warehouse (DW)
The logical and physical data models built for each type will vary greatly from models built for other types:
OLTP systems are used for collecting data
DSS is used for generating reports
DW is used for building analysis services cubes and performing high-level analysis of the data
OLTP data models should be highly normalized. We won't discuss normalization details here because normalization is a big topic. Plenty of good articles and books are available that cover normalization rules—check them out if you need a refresher. The main concept of normalization is that you store each piece of data in a single spot in the database to make the data storage highly efficient. The optimized DSS data model is highly de-normalized. When generating reports, you need to relate the data that might be stored in different tables in a normalized model. The DSS model tries to minimize the number of joins you need to perform to produce reports. Both OLTP and DSS models are built using the relational modeling theory.
Data warehouse models are somewhat trickier to generate, at least for developers used to applying relational concepts. The analysis services cubes are built on the concepts of measures and dimensions; therefore, the DW models are built using the dimensional modeling theory. The DW models usually have one or a couple of fact tables and numerous dimension tables. Each dimension table is joined to the fact table(s) with a foreign key. Such design is often referred to as the star schema. Some dimensional models further "normalize" the dimension tables—this type of design is referred to as the snowflake schema. (Feel free to read my InformIT articles from a few months ago for further information on data warehousing.)
The indexing strategy is also different for each type of database application. The OLTP applications that constantly modify data perform best with few indexes. The DSS applications, on the other hand, are mostly used for data retrieval, and require more indexes than OLTP for optimal performance. Data warehouses benefit from few indexes on the dimension tables and many indexes on the fact tables.
The point of this discussion is that various application types require different approaches to modeling and tuning. Often, realizing which type of application you're optimizing is the key to making the software much more scalable.
A common mistake is using the same database for OLTP, DSS, and sometimes even DW. Such architecture causes conflicts of interest among database users—while John Doe is keying his transaction, he locks the table on which Jane Doe is trying to run a report. One of these users will have to wait until the other is done. If you run a data import to the table while someone else is querying the same table, you're guaranteed to see the blocking problems.
If you're working in such an environment, you need to come up with a plan to separate the OLTP, DSS, and DW functionality into their own databases.