Database Design and Normalization

Site Admin · 11 Sep 2026 · 7 views

Database Design and Normalization

Design Before Data

How you structure tables determines how easy the database is to maintain and query. Good design starts with understanding the real-world facts you need to store and the questions you will ask. A little planning now saves painful migrations later.

Start with Requirements

List the entities in your problem, such as customers, products, orders, and courses. For each entity, list the facts you need, such as name, price, and city. Each entity becomes a table and each fact becomes a column. Then decide how entities relate: one-to-one, one-to-many, or many-to-many.

Consider an order. It is tempting to store the customer name directly in the orders table, but that duplicates data: change the name in one place and other orders still show the old value. Instead, store a customer_id and keep customer details in their own table.

Normalization

Normalization is a set of rules that remove redundancy and protect data consistency. The common goal for most projects is third normal form (3NF):

First normal form (1NF): each column holds a single value, and rows do not repeat.
Second normal form (2NF): every non-key column depends on the whole primary key, which matters for tables with composite keys.
Third normal form (3NF): non-key columns depend only on the primary key, not on other non-key columns.

As an example, storing city and country in one table where city uniquely determines country violates 3NF, because country depends on city rather than on the primary key.

Foreign Keys Enforce Design

Design alone is not enough; the database should refuse bad data. Foreign key constraints ensure that a referenced row exists and block deleting a row that is still referenced.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

A Healthy Habit

Draw the tables on paper first: boxes for tables, lines between related keys. Review whether any fact repeats. Normalization is worth the effort, but do not over-normalize; schedules and business reports sometimes favor a little controlled duplication for speed.