Physical Design - Database Design Process
Physical design is the process of determining the data storage organization and data access characteristics of the database in order to ensure its integrity, security, and performance. This is the last stage in the database design process. Physical design could become a very technical job that affects not only the accessibility of the data in the storage device(s) but also the performance of the system.
The physical design stage is composed of the following steps.
1. Define data storage organization
2. Define integrity and security measures
3. Determine performance measurements
1. Define Data Storage Organization:
Before you can define the data storage organization, you must determine the volume of data to be managed and the data usage patterns.
• Knowing the data volume will help you determine how much storage space to reserve for the database. To do this, the designer follows a process similar to the one used during the ER model verification process. For each table, identify all possible transactions, their frequency, and volume. For each transaction, you determine the amount of data to be added or deleted from the database. This information will help you determine the amount of data to be stored in the related table.
• Conversely, knowing how frequently the new data is inserted, updated, and retrieved will help the designer to determine the data usage patterns. Usage patterns are critical, in particular in distributed database design. For example, are there any weekly batch uploads or monthly aggregation reports to be generated? How frequently is new data added to the system?
Equipped with the two previous pieces of information, the designer must:
Determine the location and physical storage organization for each table, Identify what indexes and the type of indexes to be used for each table and Identify what views and the type of views to be used on each table.
2. Define Integrity and Security Measures
Once the physical organization of the tables, indexes, and views are defined, the database is ready to be used by the end users. But before a user can access the data in the database, he or she must be properly authenticated. In this step of physical design, two tasks must be addressed:
• Define user and security groups and roles. User management is more a function of database administration than database design. But, as a designer you must be aware of the different types of users and group of users in order to properly enforce database security. Most DBMS implementations support the use of database roles. A database role is a set of database privileges that could be assigned as a unit to a user or group.
• Assign security controls. The DBMS also allows administrators to assign specific access rights on database objects to a user or group of users. For example, you could assign the SELECT and UPDATE access rights to the user sscott on the CLASS table. An access right could also be revoked from a specific user or groups of users. This feature could come in handy during database backups or scheduled maintenance events.
3. Determine Performance Measures
Physical design becomes more complex when data are distributed at different locations because the performance is affected by the communication media’s throughput. Given such complexities, it is not surprising that designers favor database software that hides as many of the physical-level activities as possible. In spite of the fact that relational models tend to hide the complexities of the computer’s physical characteristics, the performance of relational databases is affected by physical storage characteristics. For example, performance can be affected by the characteristics of the storage media, such as seek time, sector and block (page) size, buffer pool size, and the number of disk platters and read/write heads. In addition, factors such as the creation of an index can have a considerable effect on the relational database’s performance, that is, data access speed and efficiency.
You May Also Like:
Database Development Life Cycle
Conceptual Design in Database Design Process