Data Modeling Foundations
Data modeling is the discipline of deciding what a database should represent before deciding how PostgreSQL should store it.
//
Search across all documentation pages
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 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.
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.
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.
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.
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.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17).
Reviewed by Chris St. John·Last updated Jul 15, 2026