Physical database design
After you complete the logical design of your database, you now move to the physical design. The purpose of building a physical design of your database is to optimize performance, while ensuring data integrity by avoiding unnecessary data redundancies.
During physical design, you transform the entities into tables, the instances into rows, and the attributes into columns. You and your colleagues must decide on many factors that affect the physical design, such as:
- How to translate entities into physical tables
- What attributes to use for columns of the physical tables
- Which columns of the tables to define as keys
- What indexes to define on the tables
- What views to define on the tables
- How to denormalize the tables
- How to resolve many-to-many relationships
Physical design is the time when you abbreviate the names that you chose during logical design. For example, you can abbreviate the column name that identifies employees, EMPLOYEE_NUMBER, to EMPNO. The column name size has a 30- byte maximum, and the table name size has a 128-byte maximum. For more information about the conventions and rules for database object names, see Naming conventions in SQL and Identifiers in SQL.
The task of building the physical design is a job that never ends. You need to continually monitor the performance and data integrity characteristics of a database as time passes. Many factors necessitate periodic refinements to the physical design.
Db2 lets you change many of the key attributes of your design with ALTER SQL statements. For example, assume that you design a partitioned table so that it will store 36 months of data. Later you discover that you need to extend that design to hold 84 months of data. You can add or rotate partitions for the current 36 months to accommodate the new design.