Database Design and Normalisation - SQL Tutorial

Good database design prevents data duplication, inconsistencies, and performance problems. A well-designed schema makes your application easier to build and maintain.

Choosing data types

Type	Use for	Example
`INT`	Whole numbers	IDs, counts, ages
`VARCHAR(n)`	Variable text, max n chars	Names, emails, titles
`TEXT`	Unlimited text	Articles, descriptions
`DECIMAL(p,s)`	Exact numbers	Prices, currency
`BOOLEAN`	True/false flags	Active, published
`TIMESTAMP`	Date + time	Created at, updated at
`UUID`	Unique identifiers	Distributed systems

Rule of thumb: use VARCHAR(255) for most text fields. Only use TEXT when the content might exceed a few paragraphs. Use INT for IDs unless you need UUIDs.

Primary keys

Every table should have a primary key — a unique identifier for each row.

SQL

-- Auto-incrementing integer (simplest)
CREATE TABLE students (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

-- UUID (better for distributed systems)
CREATE TABLE students (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  VARCHAR(100) NOT NULL
);

Foreign keys

Foreign keys enforce relationships between tables:

SQL

CREATE TABLE enrollments (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course     VARCHAR(100),
    score      INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

Without the foreign key, you could have an enrollment pointing to a student that doesn't exist. The constraint prevents that.

Normalisation

1NF — Each cell holds one value:

SQL

-- ❌ Bad: multiple values in one cell
CREATE TABLE students (courses TEXT);  -- "Math,Science,English"

-- ✅ Good: separate table for many-to-many
CREATE TABLE enrollments (
    student_id INT,
    course VARCHAR(100)
);

2NF — Non-key columns depend on the whole primary key.

3NF — Non-key columns don't depend on other non-key columns.

SQL

-- ❌ Bad: city depends on zip_code, not on student
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10),
    city VARCHAR(100)
);

-- ✅ Good: separate table
CREATE TABLE zip_codes (
    zip_code VARCHAR(10) PRIMARY KEY,
    city VARCHAR(100)
);

Indexes

Indexes speed up reads at the cost of slower writes and more storage:

SQL

-- Speed up lookups on a single column
CREATE INDEX idx_students_email ON students(email);

-- Composite index (for queries filtering on multiple columns)
CREATE INDEX idx_enrollments_student ON enrollments(student_id, course);

When to add an index:

Columns used in WHERE clauses frequently
Columns used in JOIN conditions
Columns used in ORDER BY

When NOT to add an index:

Small tables (< 1000 rows)
Columns that are rarely queried
Columns with low selectivity (e.g., gender with only 2 values)

Tips

Always define a primary key on every table.
Use foreign keys to enforce relationships — don't rely on application code.
Start simple (3NF), denormalise only when profiling shows a performance need.
Name tables and columns in snake_case and be descriptive (created_at, not ts).

🎉 You have completed the SQL course! You now know SELECT, JOINs, GROUP BY, INSERT/UPDATE/DELETE, and database design — the essential skills for working with any relational database.