Contact Us

Call Us

+1 800-78-erwin
(800-783-7946)

Click here for a list of erwin’s global offices.

Technical Support
Submit a Ticket

+1 813-773-4170

Chat

Get live sales support.

Email

Send us comments or
ask general questions.

info@erwin.com

Physical Data Modeling

Bring your data model to maturity with database-specific context

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 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.

They 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.

It will describe 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.

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 (erwin DM) 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 DM customers enjoy greatly reduced implementation times.

Physical data modeling with erwin DM 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.

Start Trial Learn More