Best Practices: Physical Database Design

	Introduction
	Outline for Best Practices paper
	Download
	Get involved

Learn about all of the design features that relate to the physical structure of the database such as datatype selection, table normalization and denormalization, indexes, materialized views, data clustering, multidimensional data clustering, table (range) partitioning, and database (hash) partitioning.

Physical database design is the single most important factor that impacts database performance. Physical database design covers all of the design features that relate to the physical structure of the database such as datatype selection, table normalization and denormalization, indexes, materialized views, data clustering, multidimensional data clustering, table (range) partitioning, and database (hash) partitioning.

Good physical database design reduces hardware resource utilization (I/O, CPU, and network) and improves your administrative efficiency. This, in turn, can help you achieve the following potential benefits to your business:

• Increased performance of applications that use the database, resulting in better response times and higher end-user satisfaction
• Reduced IT administrative costs, giving you the ability to manage a wider scope of databases and respond quicker to changes in application requirements
• Reduced IT hardware costs
• Improved backup and recovery elapsed time

Back to top

• Physical database design
• Executive summary
• Introduction to physical database design
• Assumptions about the reader
• Goals of physical database design
• Datatype selection best practices
• Example of virtual views that represent a lookup table for each column
• Table normalization and denormalization best practices
• Normalization
• Denormalization
• IBM Layered Data Architecture
• Index design best practices
• Clustering indexes
• Data clustering and multidimensional clustering (MDC) best practices
• Block indexes for MDC tables
• Maintaining clustering automatically during INSERT operations
• Benefits of using MDC
• MDC storage scenario
• MDC run time overhead and benefit considerations
• Determining when to use MDC versus a clustering index
• Database partitioning (shared-nothing hash partitioning) best practices
• Balanced Warehouse and Balanced Configuration Units (BCU)
• Table (range) partitioning best practices
• UNION All View (UAV) partitioning best practices
• Migrating UAVs to table partitioning
• Database partitioning, table partitioning, and MDC in the same database design best practices
• Roll-in and roll-out of data with table partitioning and MDC best practices
• Rolling-in large data volumes using table partitioning best practices
• Materialized query table (MQT) best practices
• Post-design tools for improving designs for existing databases
• Explain facility best practices
• DB2 Design Advisor best practices
• Best Practices
• Conclusion
• Further Reading
• Contributors
• Notices
• Trademarks

Back to top

" Physical Database Design " (May 2008)
Learn about all of the design features that relate to the physical structure of the database such as datatype selection, table normalization and denormalization, indexes, materialized views, data clustering, multidimensional data clustering, table (range) partitioning, and database (hash) partitioning. (pdf; 1,539KB; 63 pages)

Back to top

Comment, edit, or add your own insights to the IBM Data Server Security best practices on the IBM Database Wiki.

Check out all the other Best Practices papers and see how you can improve your experience with DB2 for Linux, UNIX, and Windows.

Back to top