ShopX schema

All tables live in the shopx schema, so write shopx.customers in your queries.

shopx.customers

One row per registered customer.

• customer_id (INT) — PK
• first_name (VARCHAR(50))
• last_name (VARCHAR(50))
• email (VARCHAR(120))
• country (VARCHAR(50))
• signup_date (DATE)
• is_premium (BOOLEAN) — true for paid membership

shopx.customer_addresses

Saved delivery addresses; a customer can have several.

• address_id (INT) — PK
• customer_id (INT) — FK → customers.customer_id
• address_line1 (VARCHAR(120))
• city (VARCHAR(60))
• state (VARCHAR(60))
• postal_code (VARCHAR(12))
• country (VARCHAR(50))
• is_default (BOOLEAN)

shopx.product_categories

Category tree. Top-level categories have no parent.

• category_id (INT) — PK
• category_name (VARCHAR(60))
• parent_category_id (INT) — FK → product_categories.category_id; nullable; NULL for a top-level category

shopx.products

The product catalogue.

• product_id (INT) — PK
• product_name (VARCHAR(120))
• category_id (INT) — FK → product_categories.category_id
• price (DECIMAL(10,2)) — current list price
• is_active (BOOLEAN)
• created_at (DATE)

shopx.orders

One row per order (the order header).

• order_id (INT) — PK
• customer_id (INT) — FK → customers.customer_id
• order_date (DATE)
• order_status (VARCHAR(20)) — 'placed', 'shipped', 'delivered', 'cancelled', 'returned'
• total_amount (DECIMAL(12,2)) — amount charged for the whole order

shopx.order_items

Line items: one row per product in an order.

• order_item_id (INT) — PK
• order_id (INT) — FK → orders.order_id
• product_id (INT) — FK → products.product_id
• quantity (INT)
• unit_price (DECIMAL(10,2)) — price at the time of the order
• discount_amount (DECIMAL(10,2)) — nullable; discount on the whole line; NULL when none

shopx.payments

Payment attempts. An order can have several attempts (e.g. a failed card, then a successful UPI payment).

• payment_id (INT) — PK
• order_id (INT) — FK → orders.order_id
• payment_method (VARCHAR(20)) — 'card', 'upi', 'wallet', 'cod'
• payment_status (VARCHAR(20)) — 'success', 'failed', 'refunded'
• amount (DECIMAL(12,2))
• paid_at (TIMESTAMP)

shopx.shipments

One shipment per shipped order.

• shipment_id (INT) — PK
• order_id (INT) — FK → orders.order_id
• carrier (VARCHAR(40))
• tracking_number (VARCHAR(40))
• shipped_at (TIMESTAMP)
• delivered_at (TIMESTAMP) — nullable; NULL while still in transit

shopx.product_reviews

Customer reviews of products.

• review_id (INT) — PK
• product_id (INT) — FK → products.product_id
• customer_id (INT) — FK → customers.customer_id
• rating (INT) — 1 to 5
• review_title (VARCHAR(120))
• review_text (TEXT) — nullable
• review_date (DATE)