Class 5 Post-Class: Test Your Database Knowledge
30-Question Quiz + E-Commerce DB Design Exercise

The schema must support: user registration and login, product browsing and search, cart management, order placement with inventory checks, payment processing with idempotency, and order history queries.

Figure 1: Six core data entities — Users, Products, Orders, Order Items, Cart Items, Payments

Figure 2: Complete database schema with all tables, columns, data types, and constraints

Key Design Decisions:

• UUIDs as Primary Keys: Globally unique, do not leak business volume, and work across shards without coordination. Trade-off: larger (16 bytes vs 4 bytes for INT) and random insertion into B-Tree indexes causes page splits. Mitigation: use UUIDv7 (time-sorted).
• price_at_time in order_items: Store the price at the time of purchase, not a join to the current price. If a product price changes from $99 to $129 after a user ordered it, their order must still show $99. Never compute order totals by joining to the live product price.
• idempotency_key in payments: A UNIQUE constraint prevents duplicate payments at the database level even if the application retries. This prevents double charges even if the application layer misses it.
• Separate addresses table: Users can have multiple addresses (home, office). A separate table with an is_default flag allows flexible address management and reuse across orders.

The index strategy is driven by access patterns. Most frequent queries: user login (email lookup), user's order history (user_id + created_at), product search (full-text), category browsing (category_id), payment idempotency checks (idempotency_key).

Figure 3: Index plan matching each core query pattern to the optimal index type

Figure 4: Four databases working together — PostgreSQL (truth), Redis (speed), Elasticsearch (search), Kafka (events)

• PostgreSQL (Source of Truth): Stores users, orders, payments, and addresses. ACID transactions ensure that order placement (inventory decrement + order creation + payment) is atomic. Read replicas handle read-heavy queries.
• Redis (Cache + Sessions + Cart): Caches product catalog (24h TTL), stores user sessions (30min TTL), and manages shopping carts. Cart data is written to PostgreSQL only at checkout.
• Elasticsearch (Product Search): Full-text search, faceted filtering, autocomplete. Product data synced from PostgreSQL via Kafka CDC (Change Data Capture).
• Kafka (Event Streaming): Order events trigger inventory updates, email notifications, analytics pipeline, search index updates, and recommendation engine updates. Decouples producers from consumers for resilient event processing.

Figure 5: What to cache and for how long — each data type has a TTL based on how frequently it changes

The caching strategy follows the cache-aside pattern: check Redis first; on miss, query PostgreSQL, store in Redis, return result. On writes, write to PostgreSQL and invalidate the Redis key.

Figure 6: The 7-step ACID transaction flow for placing an order — including row-level locking for inventory

The order placement flow must be atomic: either all steps succeed (inventory decremented + order created + payment processed) or all steps roll back. This is implemented as a single PostgreSQL transaction.

The key technique is SELECT ... FOR UPDATE on the inventory row. This places an exclusive lock, preventing any other transaction from reading or modifying it until the current transaction completes. This prevents the classic race condition where two users simultaneously buy the last item: without the lock, both would see inventory=1, both would decrement to 0, and the store would oversell.

Figure 7: Phase 1 uses single DB + replicas + caching. Phase 2 shards by user_id with products replicated to all shards.

Follow the database scaling ladder. Start with a single PostgreSQL instance (handles up to ~50K QPS). Add read replicas when reads exceed capacity. Add Redis caching to offload 90%+ of reads. Only shard when data volume or write throughput exceeds a single machine.

When sharding becomes necessary: shard by user_id. This co-locates a user's orders, order items, and payments on the same shard — keeping ACID transactions local to one shard (no distributed transactions). Products are replicated to all shards (every shard needs the full catalog). Cart data stays in Redis, which is naturally sharded by user.