Database Design, Normalization & Data Integrity

What You Will Master in This Lesson:

The mathematical foundation of anomaly-free relational architecture:

Data Redundancy: Why redundant copies degrade consistency
The 3 Anomalies: Update, Insert, and Delete anomalies
Normal Forms: Formal rules of 1NF, 2NF, and 3NF
Integrity Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FK
Last Line of Defense: Application checks vs DB-level constraints
Controlled Denormalization: Read throughput vs write complexity

1 Examining a Bad Database Design

Imagine we are building an e-commerce platform and decide to throw everything into a single convenient flat table:

Bad Schema: all_orders (Un-normalized Flat Table) Severe Redundancy

order_id	customer_name	customer_email	product_name	quantity
101	Salim	salim@example.com	Laptop	1
102	Salim	salim@example.com	Mouse	2
103	Rahim	rahim@example.com	Keyboard	1

At first glance, this table seems deceptively simple. A single query retrieves the customer name, email, product, and quantity in one pass without any JOINs.

However, look closely: Salim and his email salim@example.com are duplicated in every single order row. If Salim places 500 orders over the next two years, his basic contact information will be written to disk 500 times.

What is Data Duplication (Data Redundancy)?

When the exact same information is stored repeatedly across multiple locations beyond what is strictly necessary, it is known as Data Redundancy.

Salim | salim@example.com → Salim | salim@example.com → Salim | salim@example.com

Novice developers assume the only cost is disk storage. But disk space is inexpensive. The real, catastrophic danger of redundancy is Data Inconsistency.

2 How Inconsistency Arises: The Three Classical Anomalies

Suppose Salim changes his email address from salim@example.com to salim@newdomain.com.

Now imagine an application glitch, partial database timeout, or race condition causes 99 of Salim's 100 historical order rows to be updated, but 1 row is skipped. What is the resulting state of the database?

Conflicting States in the Database (Two Divergent Realities):

Row 101: Salim | salim@newdomain.com | Laptop

Row 102: Salim | salim@example.com | Mouse (UPDATE FAILED!)

Row 105: Salim | salim@newdomain.com | Monitor

When customer support inspects the account, they are faced with an impossible contradiction: which email is authentic? The system now houses conflicting facts for the same entity.

In relational database theory, these architectural flaws are called Anomalies. Poor schema design manifests in three classical operational breakdowns:

U Update Anomaly

Changing a single customer attribute requires mutating hundreds of duplicate rows. Missing a single row immediately causes silent, split-brain data corruption.

I Insert Anomaly

A new user signs up but has not placed an order yet. In our flat table, we cannot store the user without inserting dummy NULL values for order_id and product_name.

D Delete Anomaly

Rahim cancels his only order (#103). When you delete that row to clean up the order, Rahim's entire account, name, and email are permanently erased from the system.

3 The Remedy: Logical Decomposition

The definitive solution to anomalies is Logical Decomposition: separating data into distinct tables based on independent real-world entities.

We decompose the monolithic flat table into 4 normalized, cohesive relational tables:

1. customers Entity: Customer

Customer profile stored exactly once:

id (INT, PK)
name (VARCHAR)
email (VARCHAR, UNIQUE)
phone (VARCHAR)

2. orders Entity: Order Header

Order lifecycle metadata referenced by customer_id:

id (INT, PK)
customer_id (INT, FK → customers.id)
order_date (TIMESTAMP)
status (VARCHAR)

3. products Entity: Catalog Product

Master product pricing and inventory stock:

id (INT, PK)
name (VARCHAR)
price (DECIMAL)
stock (INT)

4. order_items Junction: Order × Product

Line items mapping which product was purchased in which order:

id (INT, PK)
order_id (INT, FK → orders.id)
product_id (INT, FK → products.id)
quantity (INT)
unit_price (DECIMAL)

What Just Changed?

Before (Un-normalized):

Order 101 → Salim (salim@...)
Order 102 → Salim (salim@...)
Order 103 → Salim (salim@...)

After (Normalized via FK):

Customer ID 1: Salim (salim@...)
Order 101 → customer_id = 1
Order 102 → customer_id = 1

When Salim changes his email, we issue exactly one UPDATE statement to row #1 in customers. Instantly, all historical and future orders reflect the change through relational foreign keys. Inconsistency is structurally impossible.

4 Understanding Normalization (1NF, 2NF & 3NF)

In formal engineering terms:

Normalization is the systematic process of organizing the columns and tables of a relational database to minimize data redundancy and maximize data dependency integrity.

Formulated by Dr. Edgar F. Codd, normalization establishes a progressive ladder of mathematical guarantees called Normal Forms:

1NF

First Normal Form: Atomic Values & Unique Records

Atomicity

1NF mandates that every cell must contain exactly one indivisible (atomic) value. A column cannot store comma-delimited lists, nested records, or arrays, and each row must be uniquely identifiable via a Primary Key.

1NF Violation (Non-Atomic):

id: 1
name: Salim
phones: 01711..., 01822... ← 2 numbers in 1 cell!
products: Laptop, Mouse

1NF Compliant (Atomic):

Rows are split or separate relation tables like customer_phones are introduced where each tuple holds exactly 1 atomic value.

2NF

Second Normal Form: Full Functional Dependency

No Partial Dependencies

To achieve 2NF, a relation must satisfy 1NF and contain no partial dependencies: every non-key column must depend on the whole primary key, not just a subset of a composite key.

What is Functional Dependency?

If knowing the value of column $X$ uniquely determines the value of column $Y$, we say "$Y$ is functionally dependent on $X$" ($X \to Y$).

Consider composite key: (order_id, product_id)
Columns: (order_id, product_id, customer_name, quantity)
quantity depends on both order_id AND product_id. But customer_name depends solely on order_id! This is a Partial Dependency.
2NF Fix: Extract customer details into an orders parent table.

3NF

Third Normal Form: No Transitive Dependencies

Non-Key Independence

To achieve 3NF, a relation must satisfy 2NF and contain no transitive dependencies: non-key columns must not depend on other non-key columns ($X \to Y \to Z$).

Bill Kent's Famous Relational Mnemonic:

"Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key, so help me Codd."

3NF Violation (employees table): emp_id (PK)
emp_name
dept_id
dept_name ← Determined by dept_id, NOT emp_id!

3NF Decomposition: employees: (emp_id, emp_name, dept_id)
departments: (dept_id, dept_name)
Department name is stored centrally in one place!

5 Interactive Lab: Anomalies & Constraint Enforcer

Hands-on Simulator

Use this interactive laboratory to simulate how flat un-normalized schemas succumb to operational anomalies, and how database-level constraints prevent corrupt writes:

Select Schema Architecture Mode: Run identical mutations across both architectures to compare behavioral guarantees

Click an action button above to observe structural differences between flat and normalized engines.

6 Data Integrity & Database Constraints

Data Integrity represents the state where data is accurate, consistent, valid, and trustworthy throughout its entire persistence lifecycle.

Relational engines preserve integrity across three primary dimensions:

1. Domain Integrity

Every column value strictly conforms to defined types, ranges, and formats (NOT NULL, CHECK, DEFAULT).

2. Entity Integrity

Every tuple is uniquely identifiable and no primary key can evaluate to null (PRIMARY KEY, UNIQUE).

3. Referential Integrity

Cross-table connections remain intact; no child foreign key can point to a nonexistent parent record (FOREIGN KEY).

Five Foundational Database Constraints:

PRIMARY KEY: Combines uniqueness and non-nullability to form the physical address for B+ Tree clustering and relationships.
NOT NULL: Prohibits missing or absent data in mandatory business fields.
UNIQUE: Guarantees that all values in a column or tuple set are distinct across all rows (e.g., email or tax ID).
FOREIGN KEY: Enforces relational validity and governs cascade behavior: ON DELETE CASCADE, ON DELETE SET NULL, or ON DELETE RESTRICT.
CHECK: Embeds domain business rules into the storage engine: CHECK (price >= 0), CHECK (status IN ('pending', 'shipped', 'delivered')).

7 Application Validation vs Database Constraints

A frequent misconception among junior engineers is: "We already validate all inputs in Node.js or Python using libraries like Zod or Pydantic. Why duplicate effort with database constraints?"

Relying exclusively on application validation is one of the most perilous architecture anti-patterns in software engineering. Application Validation and Database Constraints serve fundamentally different purposes under a Defense-in-Depth model.

How Relying Solely on the Application Leads to Corruption:

1. Race Conditions:

Two users submit the same email concurrently. Both app instances check the DB, see no record, and both write duplicates unless a UNIQUE constraint stops them.

2. Heterogeneous Microservices:

A database is accessed by web backends, background worker jobs, mobile APIs, and analytics ingest pipelines. A bug in one client corrupts shared storage.

3. Codebase Regressions:

A developer accidentally deletes an input validation guard during a refactor. Without DB constraints, invalid state silently enters production.

4. Direct Database Operations:

Data migrations, manual emergency patches, or administrative SQL scripts bypass application runtime checks entirely.

Summary: Application Validation provides friendly user feedback and UI ergonomics; Database Constraints are the impenetrable Last Line of Defense.

8 Denormalization: The Read vs Write Trade-off

Is higher normalization always better?

No. In production engineering, every optimization has trade-offs.

Over-normalizing into dozens of tables means simple read operations require 8-way to 10-way JOIN operations. At 50,000 queries per second, join evaluation costs, random page I/O, and CPU consumption become bottlenecks.

Normalization (Write-Heavy Workloads)

• Zero duplicate data and minimal storage overhead.
• Lightning-fast, deterministic writes and updates.
• Impossible to suffer update anomalies.
• Query complexity increases due to JOIN overhead.

Controlled Denormalization (Read-Heavy Workloads)

• Strategically duplicating attributes (e.g., storing customer_name on orders) or pre-aggregated summaries.
• Eliminates expensive JOINs for sub-millisecond reads.
• Requires distributed transactions or event listeners to keep copies synchronized.

9 The Engineering Mental Model: Data Architecture Flow

An experienced data systems architect follows an immutable, methodical pipeline:

REAL WORLD ENTITIES RELATIONSHIPS TABLES NORMALIZATION CONSTRAINTS DATA INTEGRITY

Whenever you design a schema or review a pull request, interrogate the schema with four fundamental questions:

1. Which core entity does this specific attribute genuinely belong to?

2. Is this fact being duplicated unnecessarily across other relations?

3. If this attribute updates, how many individual physical records must change?

4. Which storage-level constraint (CHECK, FK, UNIQUE, NOT NULL) prevents corrupted writes?

10 Knowledge Check: Interactive Quiz

3 Multiple Choice Questions

Question 1

A flat table repeats customer addresses on every order. When a customer changes address and some older rows fail to update, leaving contradictory data, which anomaly has occurred?

Question 2

What is the primary requirement for a relation to satisfy Second Normal Form (2NF)?

Question 3

Why are database-level constraints (CHECK, UNIQUE, FOREIGN KEY) mandatory even when application-level validation exists?

Key Architectural Takeaways

Data Redundancy: Storing duplicate facts wastes resources and directly leads to catastrophic operational anomalies.
The Three Anomalies: Update (partial mutation failures), Insert (inability to record independent facts), and Delete (loss of unintentionally associated master entities).
Logical Decomposition: Splitting monolithic tables into cohesive entities (customers, orders, products, order_items) bound by foreign keys.
1NF, 2NF, and 3NF: Enforcing atomic attributes (1NF), eliminating partial composite dependencies (2NF), and removing transitive relationships (3NF).
Database Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and FOREIGN KEY act as an unbreachable last line of defense.