Important Decisions in Physical Database Design
Important Decisions in Physical Database Design
In most situations, many physical database design decisions are implicit or eliminated when you choose the database management technologies to use with the information system you are designing. Since many organizations have standards for operating systems, database management systems, and data access languages, you must deal only with those choices not implicit in the given technologies. Thus, we will cover only those decisions you will make most frequently, as well as other selected decisions that may be critical for some types of applications, such an on-line data capture and retrieval.
The primary goal of physical database design is data processing efficiency. Today, with ever-decreasing costs for computer technology per unit of measure (both speed and space measures), it is typically very important for you to design the physical database to minimize the time required by users to interact with the information system. Thus, we concentrate on how to make processing of physical files and databases efficient, with less attention on efficient use of space.
Designing physical files and databases requires certain information that should have been collected and produced during prior system development phases. The information needed for physical file and database design includes these requirements:
• Normalized relations, including volume estimates
• Definitions of each attribute
• Descriptions of where and when data are used: entered, retrieved, deleted, and updated (including frequencies)
• Expectations or requirements for response time and data security, backup, recovery, retention, and integrity
• Descriptions of the technologies (database management systems) used for implementing the database
Physical database design requires several critical decisions that will affect the integrity and performance of the application system. These key decisions include the following:
1. Choosing the storage format (called data type) for each attribute from the logical data model. The format is chosen to minimize storage space and to maximize data integrity.
2. Grouping attributes from the logical data model into physical records. You will discover that although the columns of a relational table are a natural definition for the contents of a physical record, this is not always the most desirable grouping of attributes.
3. Arranging similarly structured records in...