For the best web experience, please use IE11+, Chrome, Firefox, or Safari

Physical Data Modeling

Physical data modeling is the third of three sequential stages in data modeling. Database designers produce physical data models by elaborating on the models created in the conceptual and logical data modeling stages. The models created at this stage enable managed denormalization and take into account the target technology for deployment. They are thorough enough to represent the database design as implemented, or as intended to be implemented.

What is a physical data model?

A physical data model introduces the database-specific context missing in conceptual and logical data models. It represents the tables, columns, data types, views, constraints, indices and procedures within the database and/or the information communicated during computer processes.

Physical data models should be built in relation to a specific database management system (DBMS) as well as the specific requirements of the processes that operate based on the data. This often requires denormalization of logical design constructs to maintain referential integrity. An example of the contextual considerations at the physical data modeling stage is the nature of the data that can/will be processed and the rules regarding how such processes can be executed.

Another key consideration is ensuring the modeled column types are supported in the DBMS, and the naming conventions for entities and columns are observed, preventing problematic semantic overlaps. The consideration of technological context means physical data models reflect the needs of the technological environment as is, or as intended.

What is a physical data model?

What is the goal of a physical data model?

As a database-specific representation of a data model’s implementation, physical data models help visualize a database’s structure before it is built. Their focus and ultimate goal is the implementation of a database, and they help organizations achieve this by describing how the database will be created within the confines of a specific DBMS.

With it, database designers can create an abstraction of the database and generate schema. Entity types are represented as tables, and relationship-type lines represent the foreign keys between tables. This perspective is integral to ensuring the data objects and relationships represented are accurate and compatible with an organization’s systems.

When should I consider a physical data model?

The three types of data models can and should be viewed as linear stages. As the third stage of data modeling, physical data modeling builds on the models developed in the conceptual and logical stages.

Physical models mark a shift in models being primarily constructed to represent “what” – as in the data and information that will be modeled – to “how” – implementation. Naturally, this practical approach to implementation takes into account the specifics of the DBMS and technology, including denormalization requirements, proposed for the project.

The model describes a single project’s data needs, but it also can be integrated with physical data models from other projects to account for the interrelationships between projects, processes and technology. Due to its considerations of specific technology, a physical data model is more rigid, and even small changes can require modifications to the entire application. Therefore, it’s advisable to only progress to constructing a physical data model when the conceptual and logical data models have been built.

When should I consider a physical data model?

Why should I use a physical data model?

Physical data models are an integral step toward understanding the nature of an implementation. The more complete such an understanding is, the more likely you are to successfully implement a solution that addresses the organization’s needs.

A well-designed physical data model results in better data quality, easier implementations and maintenance, and more achievable scalability. However, a well-designed physical data model is contingent on the adequacy of models preceding it. In practice, many organizations recognize the need to construct a physical data model, but gloss over or skip conceptual and logical models. This inevitably leads to gaps in design considerations and issues with data lineage and traceability from data models to physical applications.

Benefits of physical data modeling

Some of the key benefits of physical data models are that they:

  • Help produce a visual representation of the database’s structure –By leveraging visual examples, database designers have a better understanding of requirements and are able to better communicate the database’s information to stakeholders.
  • Reduce the risk of botched or incomplete implementations – A physical data model helps an organization better prepare and avoid the often serious costs of addressing oversights.
  • Easily turn the data model into a database schema – By using the ample metadata and information captured while building the conceptual, logical and physical data models, organizations should be ready for a 1:1 translation of the data model into the database design.

Better visualize your data with erwin

With more than 30 years of experience, erwin is a trusted provider of data modeling tools and the industry leader. We continue to innovate to address every stage of the data modeling process, as well as bridge the gap between data modeling and wider data governance efforts.

erwin Data Modeler by Quest users enjoy a platform that fosters collaboration while addressing every stage of the data lifecycle. From SQL and NoSQL support to automated metadata harvesting, erwin Data Modeler customers enjoy greatly reduced implementation times.

Get started now

Physical data modeling with erwin Data Modeler is a proven method for creating accurate, agile and quality blueprints for database design. Improve your data capabilities and support for/integration with data governance and intelligence efforts. It’s time to put your data to work.