Storing Repeating Groups (Violates 1NF)

-- Wrong: Repeating groups (product1, product2, product3)

CREATE TABLE orders_bad (

order_id INT PRIMARY KEY,

customer_name VARCHAR(100),

product1 VARCHAR(100),

product2 VARCHAR(100),

product3 VARCHAR(100),

price1 DECIMAL(10,2),

price2 DECIMAL(10,2),

price3 DECIMAL(10,2)

);

-- Problems:

-- 1. Cannot store order with 4+ products

-- 2. Wasted space if order has only 1 product (NULL for product2, product3)

-- 3. Complex queries: WHERE product1 = 'Laptop' OR product2 = 'Laptop' OR product3 = 'Laptop'

-- Correct: 1NF with atomic values

CREATE TABLE orders (

order_id INT PRIMARY KEY,

customer_name VARCHAR(100)

);

CREATE TABLE order_items (

order_id INT REFERENCES orders(order_id),

product VARCHAR(100),

price DECIMAL(10,2),

PRIMARY KEY (order_id, product)

);

-- Benefits:

-- 1. Unlimited products per order

-- 2. No wasted space

-- 3. Simple queries: WHERE product = 'Laptop'

Repeating groups (product1, product2, product3) violate First Normal Form. Problems: (1) Fixed limit (max 3 products), (2) Wasted space (NULLs if fewer products), (3) Complex queries (check multiple columns). Solution: Create separate order_items table with one row per product. Benefits: Unlimited products, no wasted space, simple queries. Real-world: E-commerce sites use order_items table for flexible product storage.

Never use repeating groups (column1, column2, column3). Create separate table with one row per item. Enables unlimited items and simple queries.

Critical

Repeating groups violate 1NF. Cannot store unlimited items, wasted space, complex queries. Query "all orders with Laptop" requires checking 3 columns.

graph TB

subgraph "Wrong: Repeating Groups"

W1["orders_bad<br/>order_id, customer_name<br/>product1, price1<br/>product2, price2<br/>product3, price3"]

W2["Problems:<br/>Max 3 products<br/>Wasted space (NULLs)<br/>Complex queries"]

end