Pages 2 (459 words)
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 as online 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 the processing of physical files and databases efficient, with less attention to the 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:
- Choosing the storage format (called data type) for each attribute from the logical data model. The format is chosen to minimize storage space and maximize data integrity.
- 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.
- Arranging similarly structured records in secondary memory (primarily hard disks) so that individual and groups of records (called file organizations) can be stored, retrieved, and updated rapidly. Consideration must be given also to protecting data and recovering data after errors are found.
- Selecting structures (called indexes and database architectures) for storing and connecting files to make retrieving related data more efficient.
- Preparing strategies for handling queries against the database that will optimize performance and take advantage of the file organizations and indexes that you have specified. Efficient database structures will be of benefit only if queries and the database management systems that handle those queries are tuned to intelligently use those structures.