Executive summary
Understanding the basic concepts, the stages of physical database design, and the advanced aspects that affect the structure of databases is key for a successful database design.
This paper focuses on physical database attributes that are affected by the specifics of DB2 database servers in online transaction processing (OLTP) environments.
Introduction to physical database design
The main objective of physical database design is to map logical database design to the specific features and functions of the actual database server, in this case a DB2 database server.
Database design consists of the following three phases:
- Designing a logical database model. This phase includes gathering of business requirements, and entity relationship modeling.
- Converting the logical design into database objects. This phase includes table definitions, normalization, primary key (PK) and foreign key (FK) relationships, and basic indexing. It is often performed by an application developer.
- Adjusting the deployed physical database design. This phase includes improving performance, reducing I/O, and streamlining administration tasks. It
is often performed by a database administrator.
Following logical database design, physical database design covers those aspects of database design that impact the actual structure of the database on disk. These aspects are described in phase 2 and 3. Although you can perform logical design independently of the relational database chosen, many physical database attributes depend on the target database server. Physical database design includes the following aspects:
- Data type selection
- Table normalization
- Table denormalization
- Indexing
- Clustering
- Database partitioning
- Range partitioning
- Memory provisioning
- Database storage topology
- Database storage object allocation
For details about Database storage topology and Database storage object allocation, see “DB2 Best Practices: Database Storage” at
http://www.ibm.com/developerworks/data/bestpractices/databasestorage/.
Designing a physical database model is a process that requires a periodic review even after the initial design is rolled out into a production environment. New and emerging business methodology, processes, and change requirements affect an existing database design at architectural level. The best practices for database physical design described in this paper are relevant to both new deployments and existing deployments.
Today, we can achieve I/O reductions by properly partitioning data, distributing data, and improving the indexing of data. All of these innovations that improve
database capabilities expand the scope of physical database design and increase the number of design choices resulted in the increased complexity of optimizing database structures. Although the 1980s and 1990s were dominated by the introduction of new physical database design capabilities, the subsequent years have been dominated by efforts to simplify the process through automation and best practices.
The best practices presented in this document have been developed for today's database systems and address the features and functionality available in DB2
Version 9.7 software.
Download the full report for more on Physical Database Design for Online Transaction Processing (OLTP) environments.
Download the report to get started! #Db2