Design E-Commerce Tables and Indexes for Recent-Spend Queries

Schema Design and Optimization

You are designing the core database schema for an e-commerce platform. The system must track users, products, orders, and order line items. A critical requirement is that when a user places an order, the price of each item must be snapshotted at that moment, as product prices change over time.

1. Schema Design

Define a normalized relational schema with the following tables:

• Users: user_id (PK), email, created_at.
• Products: product_id (PK), current_price, stock_quantity.
• Orders: order_id (PK), user_id (FK), status (e.g., 'pending', 'completed'), created_at.
• Order_Items: item_id (PK), order_id (FK), product_id (FK), captured_price.

Explain how you ensure data consistency for captured_price and what integrity constraints you would apply to the Order_Items table.

2. Query and Indexing

Write a SQL query to calculate the total spend of each user for all completed orders placed in the last 30 days.

-- Example Target Query
SELECT user_id, SUM(captured_price) as total_spent
FROM Orders O
JOIN Order_Items OI ON O.order_id = OI.order_id
WHERE O.status = 'completed' AND O.created_at >= NOW() - INTERVAL '30 days'
GROUP BY user_id;

Propose a set of indexes to optimize this query. Provide the specific CREATE INDEX statements and explain why your chosen columns and order (column cardinality/prefixing) benefit the execution plan.

3. Evaluation

Briefly explain what you would look for in a EXPLAIN ANALYZE output to verify that your indexes are effectively reducing scan costs.