Relational E-Commerce Database Schema & ER Diagram
An Entity-Relationship Diagram (ERD) defines tables, columns, indexes, and cardinality constraints to guarantee data integrity, eliminate redundancy, and optimize SQL query execution.
Live Architecture Studio
Edit components, modify labels, add databases, or redraw connections directly on this canvas:
Loading Database ERD Blueprint...
Mounting vector diagram elements, nodes, and capacity metrics
Loading Database ERD Blueprint...
Mounting vector diagram elements, nodes, and capacity metrics
1. Problem & Challenge
An e-commerce store needs a scalable, normalized schema to store customers, products, shopping carts, orders, and payment transactions without data anomalies, orphan records, or locking contention during flash sales.
2. Core Building Blocks & Responsibilities
👉 Desliza la tabla para ver roles y responsabilidades| Component | Role | Plain-English Explanation |
|---|---|---|
| users Table | Customer Identity & Auth | Primary key id with unique index on email, hashed credentials, and role permissions. |
| products & inventory Tables | Catalog & Stock Quantity | Maintains current product prices, descriptions, and atomic stock counts with row-level locking support. |
| orders Header Table | Order Transaction Lifecycle | Foreign key to users.id tracking state machine transitions: PENDING -> PAID -> SHIPPED -> COMPLETED. |
| order_items Join Table | M:N Relational Resolution & Price Snapshot | Composite primary key (order_id, product_id) capturing frozen historical unit_price at time of purchase. |
| payments Table | Payment Audit & Idempotency | Stores payment processor transaction IDs with unique idempotency_key to prevent double charges. |
| Indexes & Foreign Keys | Performance & Referential Integrity | ON DELETE RESTRICT constraints and B-Tree composite indexes on (user_id, created_at). |
3. Step-by-Step Request Flow
Customer Registration
New customer profile inserted into users table with encrypted credentials and verified email.
Cart Assembly & Stock Check
Client queries products table; inventory stock checked via SELECT stock FROM inventory WHERE product_id = ?.
Atomic Order Placement
Single database transaction inserts order record into orders, and bulk inserts line items into order_items.
Price Snapshot Preservation
The exact current price from products is copied into order_items.unit_price, shielding history from future catalogue price edits.
Payment Idempotency & Stock Decrement
Payment record inserted with unique provider transaction ID. Inventory decremented with optimistic version lock.
4. Architectural Trade-offs
Strict 3NF Normalization vs Selective Denormalization
Chosen: 3NF with Frozen Historical Snapshots
Rationale: Full denormalization causes massive data redundancy and update anomalies. Freezing unit_price in order_items while keeping user/product details normalized achieves 100% integrity.
Hard Deletes vs Soft Deletes (deleted_at)
Chosen: Soft Deletes with Partial Indexes
Rationale: Hard deleting customers cascades and destroys historical financial invoices. Soft deletes with a timestamp column and partial indexes (WHERE deleted_at IS NULL) retain full compliance audits.
Interview Tip
Explain Snapshot Denormalization: Never join back to `products.price` when generating customer invoices or order receipts. The catalogue price changes over time. Always freeze the `unit_price` at the instant of order creation inside the `order_items` table.
Explore Related System Blueprints
TinyURL Shortener
A URL shortener converts a long link (like a 100-character article URL) into a compact 7-character key (like tinyurl.com/xyz123) and redirects visitors in under 15 milliseconds.
API Rate Limiter
A rate limiter acts as a digital bouncer at the door of your API, ensuring each client stays within their allowed request limits (e.g. 100 requests per minute) and blocking abusive traffic.
Video Streaming CDN
Streaming high-definition video to millions of smart TVs and mobile phones requires breaking large 10GB video files into tiny 5-second chunks, encoding each into 20 different resolutions, and caching them right inside local ISP networks.
Uber Dispatch Engine
A real-time geospatial dispatch system matches riders with the most optimal nearby drivers using 64-bit H3 hexagonal indexing and 2-second batch optimization, minimizing city-wide pickup ETA and driver idle time.
Stripe Payments Ledger
A resilient financial payments architecture guarantees strict consistency (CP system) using cryptographic idempotency reservation, double-entry balanced postings, and sharded balance locks.
Figma Multiplayer Engine
A real-time multiplayer document engine uses stateful sticky session routing and server-authoritative operational ordering to sync 2D scene graphs across worldwide collaborators without locking.