Data Modeling Foundations
Data modeling is the discipline of deciding what a database should represent before deciding how PostgreSQL should store it.
Every table, foreign key, and constraint in a production schema is a physical answer to a question that was first asked in business terms, such as "what is an order" or "can a customer exist without an account."
This page lays out the mental model that the rest of this section builds on: the three layers of modeling, how they hand off to each other, and where PostgreSQL-specific mechanics enter the picture.
Summary
- Core Idea: Data modeling moves a domain through three layers of increasing commitment: conceptual (what exists), logical (how it relates), and physical (how PostgreSQL stores it).
- Why It Matters: Schemas designed physical-first tend to encode accidental implementation details as if they were business rules, making later change expensive.
- Key Concepts: conceptual model, logical model, physical model, entity, attribute, schema evolution.
- When to Use: Any greenfield schema design, any review of an existing schema's intent, and any discussion about whether a table's shape reflects the domain or just a past migration's shortcuts.
- Limitations / Trade-offs: A strict three-layer process adds upfront design time that a small prototype may not need.
- Related Topics: entity-relationship modeling, normalization, schema evolution, domain-driven schema boundaries.
Foundations
A conceptual model names the things a business cares about, independent of any database technology.
It identifies entities like Customer, Order, and Product, and the relationships between them, using the vocabulary the business itself uses.
A logical model takes that conceptual sketch and adds structure: attributes get assigned to entities, relationships get cardinality (one-to-many, many-to-many), and identity gets defined.
The logical model is still technology-agnostic in principle, though in practice most teams think in relational terms once they reach this stage.
A physical model is where PostgreSQL enters: entities become CREATE TABLE statements, attributes become typed columns, and relationships become foreign keys, indexes, and constraints.
This is also where PostgreSQL-specific decisions live, such as choosing bigint GENERATED ALWAYS AS IDENTITY for surrogate keys or timestamptz over timestamp without time zone.
The three layers are not three separate documents most teams maintain forever; they are three separate questions that get asked, roughly in order, every time a schema changes.
Skipping straight to the physical layer is the most common failure mode, because it feels productive to write DDL immediately.
The cost shows up later, when a column added under deadline pressure turns out to encode an assumption nobody actually agreed to.
Mechanics & Interactions
The handoff between layers is where most modeling defects are actually introduced, not within any single layer.
A conceptual entity like "Order" might map to one table, or it might map to several tables if the logical model decides an order has a distinct lifecycle from its line items.
That decision, one table versus several, is a logical-layer choice, and it should be made by asking what changes independently, not by asking what is convenient to JOIN.
PostgreSQL's schema namespace (CREATE SCHEMA) gives the physical layer a tool for expressing conceptual boundaries without paying for separate databases.
-- Conceptual boundary expressed physically as a schema, not a new database
CREATE SCHEMA billing;
CREATE SCHEMA catalog;
CREATE TABLE billing.invoices (
invoice_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(12, 2) NOT NULL
);This lets a single physical database still communicate "these tables belong to different parts of the domain" through naming and grants, rather than through developer memory alone.
Constraints are the other place where layers interact directly with each other.
A CHECK constraint or a NOT NULL column is a physical mechanism enforcing a rule that was stated at the conceptual layer, such as "an order cannot be marked shipped without a shipping date."
When a constraint in the schema does not map back to a stated business rule, that is usually a sign the physical layer drifted away from the model it was supposed to represent.
Conversely, a business rule that exists only in application code and not in any constraint is a rule the database cannot protect, and a future migration or ad-hoc query can silently violate it.
Advanced Considerations & Applications
Modeling decisions rarely get made once and then left alone, because the domain itself changes as a product grows.
Schema evolution is the practice of moving a physical model forward without breaking the logical contract that other services and code depend on.
The pattern most teams reach for is expand-contract: add new columns or tables alongside old ones, migrate readers and writers gradually, then remove the old shape once nothing depends on it.
At larger scale, teams also have to decide where a domain boundary becomes a database boundary, not just a schema boundary.
Domain-driven schema boundaries apply the same conceptual-entity thinking to a harder question: should this bounded context get its own schema, or its own database entirely.
That decision trades consistency and JOIN convenience (single database) against blast-radius isolation and independent deployability (separate databases), and it deserves its own explicit record rather than an implicit accumulation of tables.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
| Conceptual-first modeling | Schema reflects real business rules, not migration history | Slower to start; requires domain conversations up front | New domains, or domains with unclear ownership |
| Physical-first (DDL immediately) | Fast to prototype | Schema encodes accidental decisions as if they were rules | True throwaway prototypes only |
| Single database, multiple schemas | One transaction, easy cross-entity joins | Coupled deploys and blast radius across bounded contexts | Related domains with frequent cross-entity queries |
| Database-per-bounded-context | Strong isolation, independent scaling and deploys | Cross-context queries require APIs or replication, not joins | Domains with different ownership, compliance, or scaling needs |
Naming conventions deserve the same deliberateness as table shape, because they are the interface every future engineer reads before they read any code.
A consistent rule for primary keys, foreign keys, and junction tables turns JOIN paths into something a reader can predict rather than something they have to look up every time.
Common Misconceptions
- "The ERD is documentation, the schema is the real design." The ERD (or its equivalent conceptual sketch) is the design; the schema is one possible physical rendering of it, and treating the diagram as an afterthought is how schemas drift from the domain they claim to represent.
- "Normalization and physical modeling are the same step." Normalization is a logical-layer discipline about eliminating redundancy and update anomalies; physical modeling is about how PostgreSQL stores the normalized result, and the two questions can have different answers.
- "A good schema never changes." Every schema for a living product changes, and the goal of modeling discipline is to make that change cheap and safe, not to prevent it from happening.
- "Surrogate keys are always the right choice." Surrogate keys are the sensible default for OLTP tables, but a business already has a globally unique natural key (an ISO currency code, for example) that natural key can be the primary key without harm.
- "Modeling is a one-time upfront phase." Modeling is a recurring activity that happens every time a new entity, relationship, or business rule appears, not a phase that ends when the first migration ships.
FAQs
What is the difference between a conceptual, logical, and physical data model?
- A conceptual model names entities and relationships in business language, with no database technology involved.
- A logical model adds attributes, cardinality, and identity, while still staying technology-neutral in principle.
- A physical model is the PostgreSQL rendering: tables, typed columns, foreign keys, indexes, and constraints.
Do I need to produce three separate documents for every schema change?
No, the three layers are three questions to work through, not three deliverables to maintain forever.
For a small, well-understood change, the conceptual and logical questions can be answered in a short conversation before the DDL is written.
Why does starting with DDL cause problems later?
Writing CREATE TABLE first tends to encode whatever was convenient at that moment as if it were a business rule.
Later readers cannot tell which columns represent deliberate decisions and which are accidents of a rushed migration.
How do PostgreSQL schemas relate to conceptual modeling?
A PostgreSQL CREATE SCHEMA namespace is a physical-layer tool for expressing a conceptual boundary, such as billing versus catalog, without the cost of separate databases.
It is a lightweight way to make bounded contexts visible in the physical model.
Is normalization part of data modeling or a separate topic?
Normalization is a logical-layer technique that this same modeling process relies on to avoid redundant, contradiction-prone attribute placement.
It is covered on its own page because it has enough depth to deserve one, but it is not a separate discipline from data modeling.
How do I know when a business rule should become a database constraint?
If violating the rule would produce data that is objectively wrong, not just unusual, it belongs in a constraint.
If the rule is a soft preference that legitimately changes by context, it usually belongs in application logic instead.
What is schema evolution and how does it relate to modeling?
Schema evolution is the practice of changing a physical model over time without breaking the logical contract other code depends on.
It is the ongoing, iterative side of the same modeling process described on this page.
When should a bounded context get its own database instead of its own schema?
When the context has different ownership, different compliance requirements, or scaling needs that conflict with its neighbors, a separate database gives stronger isolation than a schema can.
A schema is enough when the contexts are related and frequently queried together.
Are surrogate keys always better than natural keys?
Surrogate keys are the safer default because they are stable and narrow, but a genuinely global, immutable natural key can serve as a primary key without the anomalies surrogate keys exist to avoid.
The decision should follow from whether the natural key can ever change or collide, not from habit.
What does it mean for a schema to "drift" from its model?
Drift happens when physical changes (a column added under deadline pressure, a constraint quietly dropped) accumulate without anyone updating the conceptual or logical understanding of the domain.
The symptom is a schema nobody can fully explain anymore.
Why do naming conventions matter this much for modeling?
Consistent naming turns every future JOIN path into something predictable rather than something that requires looking up the schema first.
It is a cheap, durable way to keep the physical model legible as a team grows.
How does this page relate to the other pages in this section?
This page gives the vocabulary and process that entity-relationship modeling, schema evolution, and domain-driven boundaries each go deeper on.
Read this first if the relationship between those pages is not already clear.
Related
- Data Modeling Basics - hands-on examples of conceptual to physical flow
- Entity-Relationship Modeling - cardinality, optionality, and naming
- Schema Evolution Strategy - expand-contract migrations over time
- Domain-Driven Schema Boundaries - grouping entities by bounded context
- ADR: Single Database vs Per-Service DB - the database-boundary decision
- Normalization Basics - the logical-layer discipline behind attribute placement
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17).