3
Overview of Physical Design

This chapter describes physical design in a data warehousing environment, and includes the following:

• Moving from Logical to Physical Design

• Physical Design

Moving from Logical to Physical Design

In a sense, logical design is what you draw with a pencil before building your warehouse and physical design is when you create the database with SQL statements.

During the physical design process, you convert the data gathered during the logical design phase into a description of the physical database, including tables and constraints. Physical design decisions, such as the type of index or partitioning have a large impact on query performance. See Chapter 6, "Indexes" for further information regarding indexes. See Chapter 5, "Parallelism and Partitioning" for further information regarding partitioning.

Logical models use fully normalized entities. The entities are linked together using relationships. Attributes are used to describe the entities. The UID distinguishes between one instance of an entity and another.

A graphical way of looking at the differences between logical and physical designs is in Figure 3-1:

Figure 3-1 Logical Design Compared with Physical Design

Physical Design

Physical design is where you translate the expected schemas into actual database structures. At this time, you have to map:

• Entities to Tables

• Relationships to Foreign Keys

• Attributes to Columns

• Primary Unique Identifiers to the Primary Key

• Unique Identifiers to Unique Keys

You will have to decide whether to use a one-to-one mapping as well.

Physical Design Structures

Translating your schemas into actual database structures requires creating the following:

• Tablespaces

• Partitions

• Indexes

• Constraints

Tablespaces

Tablespaces need to be separated by differences. For example, tables should be separated from their indexes and small tables should be separated from large tables. See Chapter 4, "Hardware and I/O", for further information regarding tablespaces.

Partitions

Partitioning large tables improves performance because each partitioned piece is more manageable. Typically, you partition based on transaction dates in a data warehouse. For example, each month. This month's worth of data can be assigned its own partition. See Chapter 5, "Parallelism and Partitioning", for further details.

Indexes

Data warehouses' indexes resemble OLTP indexes. An important point is that bitmap indexes are quite common. See Chapter 6, "Indexes", for further information.

Constraints

Constraints are somewhat different in data warehouses than in OLTP environments because data integrity is reasonably ensured due to the limited sources of data and because you can check the data integrity of large files for batch loads. Not null constraints are particularly common in data warehouses. See Chapter 7, "Constraints", for further details.