Relational Database Design: Normalization, Keys and Practical Modeling

A database outlives the code that uses it. Frameworks change, frontends are rewritten, but the schema — and the data in it — stays for years. Good design prevents duplicated and inconsistent data, keeps queries simple and makes the database fast. This guide walks through the principles with a practical example: an online store.

Step 1: Identify entities and relationships

Start from the business language. For a store we hear: customers place orders; an order contains products; products belong to categories.

• Entities: Customer, Order, Product, Category, OrderItem
• Relationships:
  • Customer 1 — N Order
  • Order 1 — N OrderItem
  • Product 1 — N OrderItem
  • Category 1 — N Product (or N — N if a product can have many categories)

Drawing a simple ER diagram before writing any SQL catches most design problems early.

Step 2: Choose keys

Primary keys

Every table needs a primary key that uniquely identifies a row.

• Surrogate keys (INT IDENTITY, BIGINT, sequential GUID) — meaningless numbers, stable forever. The default choice.
• Natural keys (national ID, email, ISBN) — meaningful but can change or have duplicates in real life. Enforce them with a unique constraint, but don't use them as the primary key.

CREATE TABLE Customers (
    Id         INT IDENTITY CONSTRAINT PK_Customers PRIMARY KEY,
    Email      NVARCHAR(256) NOT NULL CONSTRAINT UQ_Customers_Email UNIQUE,
    FullName   NVARCHAR(150) NOT NULL,
    CreatedAt  DATETIME2     NOT NULL CONSTRAINT DF_Customers_CreatedAt DEFAULT SYSUTCDATETIME()
);

Foreign keys

Foreign keys guarantee that relationships point to rows that exist:

CREATE TABLE Orders (
    Id          INT IDENTITY PRIMARY KEY,
    CustomerId  INT NOT NULL CONSTRAINT FK_Orders_Customers REFERENCES Customers(Id),
    Status      TINYINT NOT NULL,
    CreatedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

Always index foreign key columns — joins and deletes rely on them.

Step 3: Normalize

Normalization organizes data to eliminate redundancy and update anomalies. Consider this "everything in one table" design:

Problems:

• Update anomaly — Sara changes her phone number; you must update every order, and missing one creates inconsistency.
• Insert anomaly — you can't store a new customer until they place an order.
• Delete anomaly — deleting her only order deletes the customer's information.
• Non-atomic values — "Mouse, Keyboard" can't be queried, counted or joined.

First Normal Form (1NF)

Each column holds atomic values; no repeating groups or comma-separated lists.

Split the products list into separate rows — an OrderItems table with one row per product.

Second Normal Form (2NF)

1NF, and every non-key column depends on the whole primary key (relevant for composite keys).

If OrderItems has the composite key (OrderId, ProductId) and also stores ProductName, that name depends only on ProductId, not on the whole key. Move it to the Products table.

Third Normal Form (3NF)

2NF, and non-key columns depend only on the key, not on other non-key columns.

If Orders stores CustomerId and CustomerPhone, the phone depends on the customer, not on the order. Keep it in Customers.

The normalized result:

Customers(Id, FullName, Phone, Email)
Products(Id, Name, Price, CategoryId)
Categories(Id, Name)
Orders(Id, CustomerId, Status, CreatedAt)
OrderItems(OrderId, ProductId, Quantity, UnitPrice)

A memorable summary of 3NF: every non-key attribute depends on the key, the whole key, and nothing but the key.

Important exception: historical data

Notice OrderItems.UnitPrice. Isn't that duplicating Products.Price? No — it records the price at the time of purchase. If the product's price changes tomorrow, past invoices must not change. Snapshotting values that represent a point in time (prices, addresses on an invoice, tax rates) is correct design, not denormalization.

Denormalization: breaking the rules on purpose

Sometimes reads are so frequent that joining or aggregating every time is too expensive. Controlled denormalization can help:

• Storing CommentCount on Posts instead of counting comments on every page view.
• A reporting table rebuilt nightly.
• Caching computed values (search text, totals).

Rules: denormalize after measuring a real problem, keep the source of truth normalized, and update the duplicate reliably (same transaction, trigger or background job).

Choosing data types

Store timestamps in UTC and convert to the user's time zone (and Persian calendar) in the application.

Constraints are documentation that enforces itself

ALTER TABLE OrderItems ADD CONSTRAINT CK_OrderItems_Quantity CHECK (Quantity > 0);
ALTER TABLE Products   ADD CONSTRAINT CK_Products_Price     CHECK (Price >= 0);

NOT NULL, UNIQUE, CHECK and foreign keys catch bugs that application validation misses — especially under concurrency or when several applications share a database.

Naming conventions

• Plural or singular table names — pick one and be consistent (Orders or Order).
• Id as the primary key, {Entity}Id for foreign keys (CustomerId).
• Name constraints and indexes explicitly: PK_Orders, FK_Orders_Customers, IX_Orders_CustomerId.
• Avoid reserved words (User, Order in some databases) or quote them consistently.

Soft delete and auditing

Business data is rarely truly deleted. Common patterns:

ALTER TABLE Customers ADD IsDeleted BIT NOT NULL DEFAULT 0, DeletedAt DATETIME2 NULL;

Combine soft delete with filtered unique indexes (WHERE IsDeleted = 0) so a deleted user's email can be reused. Add CreatedAt, UpdatedAt (and CreatedBy) columns to important tables — or use SQL Server temporal tables for full history.

Summary

Start from the business language, identify entities and relationships, use surrogate primary keys with unique constraints for natural keys, enforce relationships with indexed foreign keys, normalize to 3NF to eliminate redundancy, snapshot point-in-time values, denormalize only after measuring, pick precise data types and let constraints guard your data. A well-designed schema makes every query, report and feature built on top of it simpler.

FAQ

Is 3NF always the goal?

For transactional (OLTP) systems, 3NF is the right default. Reporting and analytics databases often use denormalized star schemas intentionally.

GUID or INT for primary keys?

INT/BIGINT identity is compact and fast. GUIDs help when IDs are generated in many places or must be unguessable — prefer sequential GUIDs, or keep a GUID as a public identifier alongside an internal integer key.

Should I store JSON in a column?

For truly flexible, rarely queried data (settings, payloads), JSON columns are fine. If you filter, join or aggregate on the data, model it relationally.