Database Reference
In-Depth Information
Starting with an OLTP Design
When you are designing a data warehouse, there is a strong chance that you will begin by reviewing an existing
OLTP database. Although a data warehouse can be built on simple log files or XML data, it is much more likely
that it will be built upon an OLTP database.
The standard OLTP database design has been defined for several decades. Many readers may be familiar
with these patterns, but we do not expect everyone to have the same level of technical background, so let's take a
brief look at these patterns before we move on.
The three basic table patterns in an OLTP database are one-to-one, one-to-many, and many-to-many
relationship patterns. Figure
4-1
demonstrates these common design patterns.
Figure 4-1.
Standard OLTP design patterns
In a one-to-one relationship pattern, a single table is divided along its columns into two tables. Although
not unheard of, this pattern is somewhat unusual. Quite often, the goal with this pattern is to separate private
information from public information or possibly to partition data onto a separate hard drive for performance
reasons. In Figure
4-1
, two employee information tables represent a one-to-one relationship. All of the data could
have been stored in one employee table, but because Social Security information is considered private, it has
been separated into an additional table. Note that the EmployeeId in either table is never repeated in this type of
relationship pattern.
The one-to-many relationship pattern also seen in Figure
4-1
is by far the most common pattern in
OLTP databases. Our example shows a one-to-many relationship between customers and sales. These tables
demonstrate that one customer can have many sales, but an individual sale is associated with only one customer.
The many-to-many relationship pattern of Figure
4-1
demonstrates the process of how one student can
attend many classes and one class can hold many students. Note that a third table records the relationship data
by storing a copy of the two related tables' key values.
As we show later, these design patterns are also represented in a data warehouse, but for now let's look at an
example of a typical OLTP database. It enables you to contrast these OLTP designs with that of a data warehouse
design.
