Databases Lesson 6: Database Design

Good database design prevents bugs, duplicate data, and performance problems. Normalization is the process of organizing data efficiently.

Normal Forms

-- Bad design: everything in one table
create table orders (
  order_id INT,
  customer_name VARCHAR,     -- duplicated for every order!
  customer_email VARCHAR,    -- duplicated!
  product_name VARCHAR,      -- what if price changes?
  product_price DECIMAL
);

-- Normalized (3NF): separate tables for each entity
users: id, name, email
products: id, name, price, description
orders: id, user_id, created_at
order_items: id, order_id, product_id, quantity, price_at_purchase

Relationships

-- One-to-Many: one user has many orders
orders.user_id → users.id

-- Many-to-Many: users can have many roles, roles can belong to many users
-- Needs a junction table:
user_roles: user_id, role_id
-- user_id → users.id
-- role_id → roles.id

-- One-to-One: one user has one profile (split for performance)
user_profiles: user_id (FK, UNIQUE), bio, avatar, website

Design rules: Never store derived data you can calculate. Never repeat data — normalize it. Use foreign keys for referential integrity. Add created_at/updated_at to everything.