SQL Expert Lab — Full Database Architecture Design (Expert)

🖥️

SQL Terminal

📚

Reference

🗂️

Schema

⭐⭐⭐⭐⭐ EXPERT OBJECTIVES

○Design core schema: products, users, orders, inventory

○Justify all normalization decisions (3NF vs denorm)

○Design sharding strategy with shard key rationale

○Design read replica topology for global latency

○Define cache invalidation strategy (TTL vs event-based)

○Design full-text search integration (PG GIN vs external)

○Build capacity model: storage, IOPS, connection pool

○Produce Architecture Decision Record with trade-offs

0 / 8 complete

EXPERT SQL TERMINAL — FULL DATABASE ARCHITECTURE DESIGN (EXPERT)

PostgreSQL 16 — Expert Lab Environment loaded ⭐ Full Database Architecture Design (Expert) — Expert Tier ⚠ No guidance provided. Mastery is assessed on correctness AND trade-off analysis. ⚠ Use -- comments or REPORT to document every design decision. ──────────────────────────────────────────

pg=#

KEY COMMANDS

CREATE TABLE products (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), sku TEXT...
CREATE INDEX idx_products_search ON products USING GIN(search_vector);
CREATE TABLE orders (id UUID PRIMARY KEY, user_id UUID NOT NULL, shard_key INT...
CREATE INDEX idx_orders_shard_user ON orders(shard_key, user_id, created_at DE...
SELECT pg_size_pretty(pg_total_relation_size('orders'));
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE search_vector @@ to_ts...
CREATE MATERIALIZED VIEW product_inventory_summary AS SELECT category_id, COUN...

Expert tier: syntax starting points only. Your reasoning counts as much as the SQL.

SCHEMA BROWSER

Type \d or SHOW TABLES to list tables, SHOW SEARCH_PATH for active schema.

EXPERT BRIEFING

ARCHITECTURE — NOVA COMMERCE — EXPERT ⭐⭐⭐⭐⭐

⭐⭐⭐⭐⭐ Expert tier. No walkthrough. No syntax examples beyond the reference window. You are assessed on correctness, justification of trade-offs, and depth of reasoning.

SCENARIO

Nova Commerce is building a next-generation e-commerce platform for 10M daily active users, 500K concurrent sessions, 1B+ product catalogue entries, and sub-100ms global response times. You are the lead database architect. Starting from blank, design the complete database architecture: schema, normalization decisions justified, sharding strategy, read replica topology, caching integration points, full-text search approach, and a capacity model. Every decision must be documented with trade-off analysis.

OBJECTIVES (8)

Design core schema: products, users, orders, inventory
Justify all normalization decisions (3NF vs denorm)
Design sharding strategy with shard key rationale
Design read replica topology for global latency
Define cache invalidation strategy (TTL vs event-based)
Design full-text search integration (PG GIN vs external)
Build capacity model: storage, IOPS, connection pool
Produce Architecture Decision Record with trade-offs

EXPERT RULES

Every DDL decision must be justified with a -- comment or REPORT statement
Trade-off analysis is required for all design choices
All 8 objectives must be completed before the report is accepted
Reference window provides syntax starting points only

Display Mode