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.
| Element | What it specifies | Example |
|---|---|---|
| Tables and columns | Names, order and nullability | customer_order, order_date NOT NULL |
| Data types | Storage format and precision | NUMERIC(12,2), TIMESTAMPTZ, VARCHAR(80) |
| Keys and constraints | Uniqueness and referential integrity | Primary key, foreign key, CHECK |
| Indexes | Access paths for queries | Composite B-tree on (customer_id, order_date) |
| Partitioning | Physical splitting of large tables | Monthly range partitions on order_date |
| Storage settings | Space, compression and placement | Tablespace, fill factor 90, page compression |
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.
| Aspect | Conceptual model | Logical model | Physical model |
|---|---|---|---|
| Main audience | Business stakeholders | Data architects and analysts | Developers and DBAs |
| Content | Entities and relationships | Attributes, keys, normalization | Tables, types, indexes, storage |
| Platform dependence | None | None or minimal | Tied to one engine |
| Data types | Not specified | Generic domains | Exact engine types |
| Performance detail | None | None | Indexes, partitions, tuning |
| Typical output | High-level ER diagram | Detailed ER diagram | DDL scripts and schema |
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.
| Requirement | Physical choice | Main trade-off |
|---|---|---|
| Fast lookup by a selective column | B-tree index | Slower writes, extra storage |
| Very large table with time-based queries | Range partitioning | More complex maintenance |
| Analytical scans over few columns | Columnstore or column-oriented storage | Costly single-row updates |
| Frequent join of two tables | Controlled denormalization | Risk of inconsistent copies |
| Stable, compact join keys | Integer surrogate key | Extra unique constraint on natural key |
| Reduced storage footprint | Row or page compression | Higher CPU usage |
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.
| Feature | PostgreSQL | SQL Server | Oracle Database |
|---|---|---|---|
| Auto-generated key | Identity column or sequence | IDENTITY property or sequence | Identity column or sequence |
| Clustered storage | No persistent clustering by default | Clustered index | Index-organized table |
| Specialized index | GIN, GiST, BRIN | Columnstore, filtered index | Bitmap index |
| Partitioning | Declarative range, list, hash | Partition function and scheme | Range, list, hash, interval |
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.