Relational Data Modeling & Normalization (1NF to BCNF)

Q: Should IDs be integers or UUIDs?

Integers (bigint identities) are smaller and fast to index. UUIDs suit IDs created by clients or several services, merged datasets, and public identifiers that should not reveal counts. Time-ordered UUIDv7 avoids the index fragmentation of random UUIDv4.

Relational Data Modeling and Normalization

Application code gets rewritten; data stays. The tables you design in the first month of a product are often still there years later, holding every customer, order, and invoice, and every feature built since has been shaped by them. A good relational model makes correct data easy to store and wrong data hard or impossible to store. A poor one leaks inconsistencies — two spellings of the same customer, an order pointing at a deleted product, a total that disagrees with its line items — that no amount of application code fully cleans up.

Data modeling is deciding which things the system records, how they relate, and which rules must always hold. Normalization is the discipline of storing each fact exactly once, so that it cannot contradict itself. Together they produce schemas that are easier to query, change, and trust.

TL;DR

Model the domain first: the entities, their relationships, and the rules that must always be true.
Give every table a primary key; use surrogate keys for identity and unique constraints for natural keys such as email or SKU.
Normalize to third normal form (or BCNF) by default, so each fact is stored once.
Enforce rules in the database with NOT NULL, UNIQUE, CHECK, and foreign keys, not only in application code.
Denormalize deliberately, for measured read performance, with a mechanism that keeps copies consistent.
Treat the schema as something that evolves, through migrations designed for zero downtime.

Quick Example

A spreadsheet-style table repeats customer and product details on every order line. Normalized, each fact lives in one place:

Renaming a customer now changes one row instead of every order they ever placed. Note that unit_price on order_items is not duplication: the price paid on a past order is a different fact from today's list price, and it must not change when the catalog does.

Core Concepts

Entities, Attributes, and Relationships

An entity is a thing the business cares about and can identify — customer, order, product, invoice. Attributes describe it. Relationships connect entities with a cardinality:

Junction tables often carry attributes of their own — quantity and price on order_items, a role and joined date on a membership table — which is a sign they are real entities.

Keys

Natural keys change more often than expected — people change email addresses, companies rename SKUs — so most tables use a surrogate primary key and keep natural keys under UNIQUE constraints. Sequential bigint identities are compact and fast; UUIDs suit IDs generated outside the database or exposed publicly, and time-ordered UUIDv7 values keep index inserts local.

Functional Dependencies and Anomalies

A functional dependency A → B means that knowing A determines B: a product's SKU determines its name. Normalization problems are dependencies stored in the wrong table. In orders_flat, customer_email → customer_name, so the name is repeated on every line, causing three classic anomalies:

Update anomaly: renaming a customer means updating many rows; missing one leaves contradictory data.
Insertion anomaly: a new product cannot be recorded until someone orders it.
Deletion anomaly: deleting the last order for a product also deletes the only record of that product.

Normal Forms

Each normal form removes a kind of misplaced dependency. A popular summary of the first three: every non-key column should depend on "the key, the whole key, and nothing but the key."

In practice, aiming for 3NF and checking for BCNF problems covers nearly every transactional schema. Higher forms matter mostly when a table tries to record several independent many-to-many relationships at once.

Constraints as Business Rules

Rules enforced only in application code are enforced only on the code paths that remember them. Scripts, admin tools, background jobs, data fixes, and future services all bypass them. Constraints hold for every writer, and they are checked inside transactions, which avoids races between "check" and "insert".

Choose foreign key delete behavior per relationship: RESTRICT (the default intent) protects referenced data, CASCADE suits true parts of a whole such as order lines, and SET NULL suits optional references.

Modeling Common Shapes

Hierarchies

Categories, comment threads, and org charts are trees. The simplest model is an adjacency list — a parent_id column referencing the same table — queried with recursive common table expressions. Materialized paths (/electronics/audio/headphones), closure tables, and PostgreSQL's ltree extension make subtree queries faster at the cost of more complex writes.

Polymorphic References

A comments table with commentable_type and commentable_id columns cannot have a foreign key, so comments can point at rows that do not exist. Prefer one nullable foreign key per target with a check that exactly one is set, or a separate comments table per parent type.

Money, Time, and Flexible Attributes

Store money as numeric or as integer minor units (cents) with a currency code — never as floating point.
Store instants as timestamptz and calendar dates as date; see Dates, Time Zones, and Daylight Saving.
Use a JSON column for genuinely variable attributes, such as integration-specific settings, but keep anything you filter, join, or constrain on in real columns. See PostgreSQL JSONB.

History

When the business needs to know what was true at a point in time — prices, addresses, plan changes — model it explicitly with valid_from and valid_to columns or append-only history tables, instead of overwriting rows and hoping an audit log is enough.

When to Denormalize

Normalization optimizes for correct writes; some read paths need data pre-joined or pre-aggregated. Denormalize when a measured query cannot be made fast enough with indexes and query optimization, and choose how the copy stays consistent:

Every denormalized copy needs an owner and a way to rebuild it from the normalized source.

Evolving the Model

Schemas change as the product does. Change them through versioned migrations, and for live systems use expand and contract: add the new structure, write to both, backfill, switch reads, then remove the old structure. Add constraints to large tables without long locks — in PostgreSQL, create a foreign key or check with NOT VALID and run VALIDATE CONSTRAINT afterwards.

Best Practices

Name Things Consistently

Pick conventions and keep them: snake_case, a consistent choice of plural or singular table names, _id for foreign keys, _at for timestamps, _on for dates. Consistent names make queries guessable and reviews faster.

Index Foreign Keys

PostgreSQL does not automatically index the referencing side of a foreign key. Without an index, joins and cascading deletes on large tables scan the whole child table.

Use Precise Types

timestamptz for instants, date for dates, numeric for money, boolean for flags, uuid for UUIDs. Correct types are constraints too: they reject impossible values for free.

Keep the Model Documented

Generate entity-relationship diagrams from the live schema, comment tables and columns whose meaning is not obvious, and record why unusual design choices were made.

Common Mistakes

Lists Packed Into a Column

Entity-Attribute-Value for Everything

Floating-Point Money

Skipping Foreign Keys "for Performance"

FAQ

How far should I normalize?

To third normal form or BCNF for transactional data, by default. That removes the redundancy that causes update anomalies. Denormalize specific read paths afterwards when measurements show a need, keeping the normalized tables as the source of truth.

Natural or surrogate primary keys?

Usually surrogate keys for identity, with natural keys protected by unique constraints. Natural keys change, can be long, and sometimes turn out not to be unique. Small, stable reference data — ISO country or currency codes — is a reasonable exception.

Should IDs be integers or UUIDs?

Integers (bigint identities) are smaller and fast to index. UUIDs suit IDs created by clients or several services, merged datasets, and public identifiers that should not reveal counts. Time-ordered UUIDv7 avoids the index fragmentation of random UUIDv4.

Is denormalization bad?

No — uncontrolled denormalization is. A denormalized copy is fine when it solves a measured problem, has a clear mechanism for staying consistent, and can be rebuilt from the normalized source.

Do NoSQL databases need data modeling?

Yes, differently. Document and key-value stores are modeled around access patterns — which reads must be fast — and often embed or duplicate data deliberately. The questions about consistency and duplication do not go away. See SQL vs NoSQL and MongoDB Schema Design.

Should business rules live in the database or in the application?

Invariants that must never be broken — uniqueness, existence of referenced rows, valid states, non-overlapping ranges — belong in database constraints, because every writer passes through them. Workflow and presentation rules belong in the application. Duplicating an invariant in the application for friendly error messages is fine.