After completing the logical design of our database, we now move to the physical design. The purpose of building a physical design of our 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 must decide on many factors that affect the physical design, some of which are listed as follows:
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
hysical 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 task of building the physical design is a job that truly never ends. You need to continually monitor the performance and data integrity characteristics of the database as time passes. Many factors necessitate periodic refinements to the physical design.
Denormalization is a key step in the task of building a physical relational database design. It is the intentional duplication of columns in multiple tables, and the consequence is increased data redundancy.
This is recommended to avoid performance problems occur as a result of normalization. This should be done based on the processing needs of applications accessing the data.
If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!