Database Schema Design

Objective

Design a normalized relational schema using ERD, apply 1NF/2NF/3NF, and choose appropriate data types.

Tools & Technologies

normalization
ERD
primary key
foreign key
indexes

Key Commands

CREATE TABLE users (id SERIAL PRIMARY KEY, ...);

ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

CREATE INDEX idx_users_email ON users(email);

EXPLAIN SELECT * FROM orders WHERE user_id=1;

Architecture Overview

erDiagram USER { int id PK varchar name varchar email timestamp created_at } ORDER { int id PK int user_id FK decimal total timestamp created_at } ORDER_ITEM { int id PK int order_id FK int product_id FK int quantity decimal price } PRODUCT { int id PK varchar name decimal price } USER ||--o{ ORDER : places ORDER ||--|{ ORDER_ITEM : contains PRODUCT ||--o{ ORDER_ITEM : included_in

Step-by-Step Process

Normal Forms

Apply normalization to eliminate redundancy.

-- 1NF: atomic values, no repeating groups
-- 2NF: no partial dependencies on composite PK
-- 3NF: no transitive dependencies

-- Bad (unnormalized):
-- orders: id, customer_name, customer_email, product1, product2

-- Good (3NF):
-- customers(id, name, email)
-- orders(id, customer_id, date)
-- order_items(id, order_id, product_id, qty)

DDL — Create Tables

Create tables with proper constraints.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10,2) NOT NULL CHECK(total >= 0),
  status VARCHAR(20) DEFAULT 'pending'
);

Challenges & Solutions

Over-normalization hurts read performance — denormalize hot paths if needed
Indexes speed reads but slow writes — don't index every column

Key Takeaways

Design for your query patterns first, normalize second
Foreign key ON DELETE CASCADE vs RESTRICT — choose based on business rules