Normalize Orders & Order Items

SQL

Technical Specification

Design relational schema for orders and order_items tables to avoid data duplication and support multiple items per order.

Input/Output Samples

Input:order with 3 products

Output:1 row in orders, 3 in order_items

Optimal Logic Path

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  user_id     INT NOT NULL REFERENCES users(id),
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  total_amount NUMERIC(10,2)
);

CREATE TABLE order_items (
  id         SERIAL PRIMARY KEY,
  order_id   INT NOT NULL REFERENCES orders(id),
  product_id INT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL,
  price      NUMERIC(10,2) NOT NULL
);

Architectural Deep-Dive

Splitting orders and items avoids repeating order-level data for each line and models one-to-many correctly.