Database Design Principles

Normalization

First Normal Form (1NF)

Atomic values only.

Second Normal Form (2NF)

Remove partial dependencies.

Third Normal Form (3NF)

Remove transitive dependencies.

---

Primary Keys

CREATE TABLE Employee (

    id BIGINT PRIMARY KEY,

    name VARCHAR(100)
);

---

Foreign Keys

customer_id BIGINT REFERENCES Customer(id)

Maintains referential integrity.

---

Indexing

Create indexes on

• Search columns
• Foreign keys
• Join columns
• Frequently filtered columns

Benefits

• Faster reads

Trade-off

• Slower inserts and updates

---

Avoid Over Indexing

Too many indexes

• Increase storage
• Slow writes
• Increase maintenance

---

Denormalization

Duplicate data only when necessary to improve read performance.

Example

Orders Table

Customer Name copied

Useful in reporting systems.

---

Database Sharding

Split data across multiple databases.

flowchart TD

Application --> Shard1

Application --> Shard2

Application --> Shard3

Benefits

• Scalability
• Reduced contention

---

Replication

flowchart TD

Master --> Replica1

Master --> Replica2

Master --> Replica3

Read operations use replicas while writes go to the master.

---

Key Takeaways

• Normalize (1NF → 2NF → 3NF) to eliminate redundancy and anomalies, then selectively denormalize only where read performance demands it (e.g. reporting).
• Index search/join/filter columns for faster reads, but avoid over-indexing — every index slows writes and adds storage/maintenance cost.
• Sharding scales writes by splitting data across databases; replication scales reads by routing them to replicas while writes stay on the master.