Generate an E-Commerce Database Schema from JSON

E-Commerce Schema from JSON

An e-commerce database involves multiple related entities: products, customers, orders, and line items. Converting JSON data from these entities into a cohesive SQL schema demonstrates how the converter handles complex, multi-table relationships.

Example JSON

{
  "order": {
    "id": 5001,
    "customer_id": 42,
    "status": "processing",
    "shipping_address": {
      "street": "456 Oak Ave",
      "city": "Portland",
      "state": "OR",
      "zip": "97201"
    },
    "items": [
      {
        "product_id": 301,
        "product_name": "Mechanical Keyboard",
        "quantity": 1,
        "unit_price": 149.99
      },
      {
        "product_id": 302,
        "product_name": "USB-C Cable",
        "quantity": 2,
        "unit_price": 12.99
      }
    ],
    "subtotal": 175.97,
    "tax": 15.84,
    "total": 191.81,
    "ordered_at": "2024-06-15T14:22:00Z"
  }
}

Generated SQL

-- Products table (referenced by line items)
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  CONSTRAINT chk_products_price CHECK (price >= 0)
);

-- Customers table (referenced by orders)
CREATE TABLE customers (
  id SERIAL PRIMARY KEY
  -- additional fields from customer JSON
);

-- Orders table
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  subtotal DECIMAL(10, 2) NOT NULL,
  tax DECIMAL(10, 2) NOT NULL DEFAULT 0,
  total DECIMAL(10, 2) NOT NULL,
  ordered_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT chk_orders_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
  CONSTRAINT chk_orders_total CHECK (total >= 0)
);

-- Shipping addresses (one per order)
CREATE TABLE shipping_addresses (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL UNIQUE,
  street VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL,
  state VARCHAR(50) NOT NULL,
  zip VARCHAR(20) NOT NULL,
  CONSTRAINT fk_shipping_addresses_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Order line items (many per order)
CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price DECIMAL(10, 2) NOT NULL,

  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT chk_order_items_quantity CHECK (quantity > 0),
  CONSTRAINT chk_order_items_price CHECK (unit_price >= 0)
);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_ordered_at ON orders (ordered_at);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

Schema Design Highlights

Money as DECIMAL — All monetary values use DECIMAL(10, 2) for exact precision. Never use FLOAT or DOUBLE for money — rounding errors accumulate.

Denormalized product_name — The order_items table stores the product name at time of purchase. Product names may change later, but the order history must preserve the original.

Shipping address per order — Each order has its own shipping address (not a reference to a reusable address table) because customers may change their address between orders.

CHECK constraints — Quantities must be positive, prices must be non-negative, and status must be from a defined set. These protect against invalid data.

Normalization Decisions

The converter makes intelligent choices about when to normalize:

• Separate tables for entities with independent lifecycles (products, customers, orders).
• Embedded for one-to-one sub-objects tightly coupled to the parent (shipping address to order).
• Junction-style for line items that form the many-to-many relationship between orders and products.

Extension Points

A real e-commerce schema would also include: inventory tracking, discount codes, payment records, refund history, and audit logs. Each of these follows the same JSON-to-SQL patterns demonstrated above.