Normalization eliminates data redundancy and anomalies. Prevents update errors, saves storage, ensures consistency. Essential for production databases.

E-Commerce Order System Normalization

Normalizing Amazon-scale order data from 0NF to 3NF:

Unnormalized (0NF):

order_id | customer_name | customer_email | product1 | price1 | product2 | price2

3NF (Normalized):

CUSTOMERS (customer_id PK, name, email)

ORDERS (order_id PK, customer_id FK, order_date)

PRODUCTS (product_id PK, name, price)

ORDER_ITEMS (order_item_id PK, order_id FK, product_id FK, quantity)

Normalization eliminates redundancy. Update product price once (not in 1000 order rows). Change customer email once (not in 50 order rows). Saves storage, prevents inconsistency.

Update product price (affects all orders)

UPDATE products SET price = 899.99 WHERE product_id = 101;

-- Updates once, affects all future orders automatically

Change customer email (one place)

UPDATE customers SET email = 'newemail@example.com' WHERE customer_id = 1;

-- Updates once, reflected in all orders

All

Blog Platform Schema Design

Normalized blog platform with users, posts, comments, tags:

USERS (user_id PK, username, email, bio)

POSTS (post_id PK, user_id FK, title, content, published_at)

COMMENTS (comment_id PK, post_id FK, user_id FK, content, created_at)

TAGS (tag_id PK, tag_name UNIQUE)

POST_TAGS (post_id FK, tag_id FK, PRIMARY KEY (post_id, tag_id))

Many-to-many relationship (posts ↔ tags) handled via junction table POST_TAGS. Add new tag once, reuse across posts. No duplicate tag names.

Find all posts with tag "SQL"

SELECT p.title, p.content

FROM posts p

JOIN post_tags pt ON p.post_id = pt.post_id

JOIN tags t ON pt.tag_id = t.tag_id

WHERE t.tag_name = 'SQL';

Count posts per user

SELECT u.username, COUNT(p.post_id) as post_count

FROM users u

LEFT JOIN posts p ON u.user_id = p.user_id

GROUP BY u.username;

All

Inventory Management System

Warehouse inventory with proper normalization: