Online College Courses for Credit

Physical Design

Physical Design

Author: Sophia Tutorial

List the main steps of the physical design.

See More

what's covered
This tutorial explores the steps in the physical model design.

The physical model is the final model that extends the logical model. This data model describes how the database would be implemented using a specific database management system. This model is created by the database administrator and the database developers. The purpose here is to implement the database.

By the end of the physical data model process, we should have the specification for all of the tables and columns, define the relationships between the tables, determine if there are any physical considerations that may need to change the physical data model from the logical data model. It is important to note that the physical data model will be different depending on the RDBMS that has been selected and defined. Each database will have its own specific data types that are to be used.

The physical design focuses on data storage, security measures, and performance measures. Before we can define the data storage organization, we do have to know the volume of the data and usage patterns. The column data types and sizes are defined here so it is important to be aware of what the data should consist of. We will also determine what the indexes are for each table beyond the primary keys depending on the usage patterns or performance requirements will be. If there are any views, this would be useful to define now as well for the various types of roles that would interact with the database.

At this step, we would take the time to define the group roles and user roles that can access this database and what level of privileges they should have for each object. This goes beyond the physical data model itself, but it is a crucial step to take when planning for the physical database design. All of the constraints that we have defined as part of the logical data model such as unique or NOT NULL constraints would also be implemented as part of the physical data model. Once this model has been created, it should be ready to be implemented in the database that the physical model has been created for.

The following is a completed physical data model that has been defined for the eCommerce database. You may notice that a lot of the core elements are identical in this case as the logical data model with just the added data types and sizes. In other databases, there may be changes with added constraints depending on the business rules or other criteria being incorporated.

entity-relationship diagram

The physical data model will extend the logical data model to define the data types, sizes, constraints specific to a database management system.

Source: Authored by Vincent Tran