Physical Data Modeling

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.

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.

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.

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.

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.

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.