sqladvanced40 minutes

Design and Implement a Scalable E-Commerce Order Tracking Data Model

Build a comprehensive SQL data model supporting order tracking, including customers, products, orders, shipments, and status history, ensuring efficient querying for complex business insights.

Challenge prompt

You are tasked with creating a scalable, normalized data model suitable for an e-commerce platform's order tracking system. Your model must include tables for Customers, Products, Orders, Order Items, Shipments, and Order Status History. Each order can contain multiple products, and each shipment can include multiple orders or partial shipments. Additionally, you must track each status change of an order over time to support historical queries. Using standard SQL (DDL), create the full schema with appropriate primary keys, foreign keys, indexing considerations, and constraints to maintain data integrity. Think carefully about the relationships between tables and the historical tracking of status updates. Your model should optimize for queries such as retrieving all shipments for a customer, the status history of a particular order, and identifying delayed shipments. Consider edge cases like split shipments and orders with multiple status changes. The resulting schema should be normalized to at least 3NF and allow for efficient inserts and queries.

Guidance

• Focus on designing clear one-to-many and many-to-many relationships with junction tables where needed.
• Use status history tables to track changes with timestamps rather than overwriting existing order status.
• Add indexes on foreign keys and commonly queried columns to optimize performance.

Hints

• A junction table like OrderItems is necessary to model multiple products per order.
• To track multiple shipments per order, consider a Shipments table with a linking table between Orders and Shipments.
• Use a dedicated OrderStatusHistory table with an order_id, status, and timestamp column for status tracking.

Starter code

CREATE TABLE Customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(255),
  email VARCHAR(255) UNIQUE
);

-- Define Products, Orders, OrderItems, Shipments, and OrderStatusHistory tables as part of the task.

Expected output

A set of SQL CREATE TABLE statements defining Customers, Products, Orders, OrderItems, Shipments, and OrderStatusHistory with keys, constraints, and indexes.

Core concepts

SQL schema designData normalizationRelational modelingIndexing and constraints

Challenge a Friend

Send this duel to someone else and see if they can solve it.