Physical data model

On this page 7

A physical data model is the implementation-level representation of data that specifies exactly how it is stored in a particular database management system: tables, columns, data types, keys, indexes, partitions, storage parameters and constraints. In data modeling, it is the third and most concrete layer, derived from the logical data model and ultimately from the conceptual data model.

Where the logical model asks what the data means and how it relates, the physical model asks how the data will perform on real hardware and a real engine such as PostgreSQL, Oracle Database, SQL Server or MySQL. It is the blueprint from which DDL scripts are generated and against which database administrators tune production systems in the field of databases.

What a physical data model contains

A physical data model contains every object the target engine needs to create a working schema. Each logical entity becomes one or more physical tables, each attribute becomes a typed column, and each relationship becomes a foreign key constraint or a deliberate denormalized structure.

Beyond structure, the model records engine-specific decisions that have no meaning at the logical level. These include tablespace assignment, fill factor, compression, collation, sequence or identity definitions and the exact index type, such as B-tree, hash, GIN or columnstore. The following table lists the main element groups.

ElementWhat it specifiesExample
Tables and columnsNames, order and nullabilitycustomer_order, order_date NOT NULL
Data typesStorage format and precisionNUMERIC(12,2), TIMESTAMPTZ, VARCHAR(80)
Keys and constraintsUniqueness and referential integrityPrimary key, foreign key, CHECK
IndexesAccess paths for queriesComposite B-tree on (customer_id, order_date)
PartitioningPhysical splitting of large tablesMonthly range partitions on order_date
Storage settingsSpace, compression and placementTablespace, fill factor 90, page compression
Core elements of a physical data model

From logical design to physical implementation

The transformation from logical to physical design is a sequence of translation and optimization steps. Logical names are converted to the naming conventions of the platform, generic attribute domains are mapped to concrete engine types, and natural identifiers are often replaced or complemented by a surrogate key.

Many-to-many relationships, which a logical model may show directly, are resolved into associative tables with two foreign keys. Subtype hierarchies are implemented as a single table, one table per subtype or a shared table plus extension tables, and each option has different consequences for null values, joins and integrity checks.

The designer then adds structures driven purely by workload: indexes for frequent predicates, materialized views for expensive aggregations and partitions for large, time-based data. At this stage, a normalized logical design may be selectively denormalized when measured query costs justify the extra redundancy.

The three modeling layers differ in audience, detail and dependency on technology, as summarized below.

AspectConceptual modelLogical modelPhysical model
Main audienceBusiness stakeholdersData architects and analystsDevelopers and DBAs
ContentEntities and relationshipsAttributes, keys, normalizationTables, types, indexes, storage
Platform dependenceNoneNone or minimalTied to one engine
Data typesNot specifiedGeneric domainsExact engine types
Performance detailNoneNoneIndexes, partitions, tuning
Typical outputHigh-level ER diagramDetailed ER diagramDDL scripts and schema
Conceptual, logical and physical data models compared

Key physical design decisions

Physical design is a set of trade-offs between read speed, write cost, storage and maintainability. No choice is free: every index accelerates certain reads but slows inserts and updates, and every redundant column saves a join but creates a consistency obligation.

Data type selection is often underestimated. Choosing BIGINT where INTEGER suffices, or fixed-length CHAR where variable-length text is appropriate, inflates row size, reduces the number of rows per page and therefore increases I/O for every scan. Monetary values belong in exact decimal types, never in binary floating point.

The decision table below shows how common requirements translate into physical choices.

RequirementPhysical choiceMain trade-off
Fast lookup by a selective columnB-tree indexSlower writes, extra storage
Very large table with time-based queriesRange partitioningMore complex maintenance
Analytical scans over few columnsColumnstore or column-oriented storageCostly single-row updates
Frequent join of two tablesControlled denormalizationRisk of inconsistent copies
Stable, compact join keysInteger surrogate keyExtra unique constraint on natural key
Reduced storage footprintRow or page compressionHigher CPU usage
Typical requirements and physical design responses

In analytical systems, the physical model frequently follows a star schema or snowflake schema, with large fact tables partitioned by date and smaller dimension tables indexed on their surrogate keys. Transactional systems, by contrast, usually stay close to third normal form and rely on narrow, well-indexed tables.

Engine-specific aspects of the physical model

A physical data model is valid for one platform, because engines differ in type systems, index structures and storage options. A model built for SQL Server cannot be deployed unchanged on PostgreSQL; the designer must re-map types and features to the new engine.

FeaturePostgreSQLSQL ServerOracle Database
Auto-generated keyIdentity column or sequenceIDENTITY property or sequenceIdentity column or sequence
Clustered storageNo persistent clustering by defaultClustered indexIndex-organized table
Specialized indexGIN, GiST, BRINColumnstore, filtered indexBitmap index
PartitioningDeclarative range, list, hashPartition function and schemeRange, list, hash, interval
Examples of engine-specific physical features

These differences matter for query plans and maintenance. In SQL Server, the choice of the clustered index determines the physical order of rows, while PostgreSQL stores rows in an unordered heap and depends on secondary indexes and periodic vacuuming.

Common mistakes and limitations

The most frequent failure is skipping the logical model and designing tables directly, which mixes business meaning with storage concerns and makes later changes expensive. Other mistakes appear when physical decisions are made without real workload data.

  • Over-indexing: dozens of indexes on a write-heavy table slow every insert and consume storage.
  • Missing foreign key indexes: unindexed foreign keys cause slow joins and lock contention on deletes.
  • Premature denormalization: redundancy added without measurement creates update anomalies.
  • Model drift: the documented physical model no longer matches the production schema after manual hotfixes.
  • Wrong data types: storing dates as text or amounts as floating point breaks sorting, validation and arithmetic.

Model drift is best prevented by treating the physical model as version-controlled code, applying changes through migration scripts and comparing the deployed schema with the model regularly. A physical model also ages as data volumes grow, so periodic review of indexes and partitions is part of its lifecycle.

A non-technical view

Imagine planning a library. First you decide that there will be books, readers and loans. Then you decide what information each needs, such as a title or a membership number. Finally, you decide on the actual shelves, labels, card drawers and where each item goes in the building.

The physical data model is that last, practical plan. It tells a specific database exactly how to store and find the information: which tables exist, what kind of value each field holds and which shortcuts, called indexes, speed up searching. Good choices make the system fast and reliable; poor choices make it slow, however good the earlier plans were.

A concrete example

An online retailer moves its order system to PostgreSQL. The logical model has entities for customer, order, order line and product, with a many-to-many relationship between orders and products. In the physical data model, the team creates an order_line associative table, assigns BIGINT identity surrogate keys, stores prices as NUMERIC(12,2) and stores timestamps as TIMESTAMPTZ.

The orders table is expected to reach about 400 million rows, so the team defines monthly range partitions on order_date and a composite index on (customer_id, order_date) for the order history page. The product name is deliberately copied into order_line, because invoices must show the name valid at the time of purchase.

After deployment, the order history query drops from several seconds to under 50 milliseconds, and old partitions can be detached and archived in minutes instead of running large delete operations.