한국어 | English

A Python tool that generates realistic test databases for an online computer & peripherals store. Designed for SQL learners who want to practice with well-structured, production-like data.

Why this project? One of the biggest challenges for beginner/intermediate developers learning databases is the lack of access to well-designed, realistic databases. This generator creates a complete e-commerce database with 10 years of business data, realistic patterns, and proper schema design.

# Install dependencies
pip install -r requirements.txt

# Small (230K rows, ~29MB, ~9 sec)
python generate.py --size small
# Medium (2M rows, ~250MB, ~2 min) — default
python generate.py --size medium
# Large (10M rows, ~1.2GB, ~10 min)
python generate.py --size large

# Small
python generate.py --size small --locale en
# Medium
python generate.py --size medium --locale en
# Large
python generate.py --size large --locale en

Output: output/tutorial.db (SQLite)

The tutorial documentation is built with MkDocs Material and can be previewed locally.

# Korean tutorial (http://localhost:8001)
serve.bat
# English tutorial
serve.bat en

Or run directly:

cd docs
mkdocs serve -f mkdocs-en.yml -a localhost:8001

Changes to files are auto-reloaded in the browser.

Lessons (21)

Exercises (201 problems)

Reference

• DB Dialect Comparison (SQLite / MySQL / PostgreSQL / SQL Server / Oracle)

• 21 tables with proper foreign keys, CHECK constraints, and indexes
• 18 views demonstrating real-world SQL patterns (window functions, CTEs, RFM analysis, etc.)
• 5 triggers for automatic timestamp updates and price history tracking
• Stored procedures for MySQL and PostgreSQL (in sql/ directory)
• Multi-locale support: Korean (ko) and English (en), extensible to other languages
• Realistic data patterns:
  • 10-year business growth curve (2016-2025)
  • Seasonal order patterns (Black Friday peaks, summer lows)
  • Time-of-day order distribution (evening peaks, dawn lows)
  • Pareto customer distribution (top 20% = 60% of orders)
  • Dormant/churned customer modeling
  • Price-weighted product selection (cheaper items sell more)

python generate.py [OPTIONS]
--size {small,medium,large} Data scale (default: medium)
--locale {ko,en} Data language (default: ko)
--seed NUMBER Random seed for reproducibility (default: 42)
--target {sqlite,...} Target database (default: sqlite)
--all Generate for all database targets
--download-images Download real product photos via Pexels API
--pexels-key KEY Pexels API key (or set PEXELS_API_KEY env var)
--config PATH Config file path (default: config.yaml)

categories ──┐
 ├── products ──┬── product_images
suppliers ───┘ ├── product_prices
 ├── order_items ──── orders ──┬── payments
 ├── reviews ├── shipping
 ├── wishlists ├── returns
 ├── cart_items ── carts ├── complaints
 └── inventory_transactions └── coupon_usage ── coupons
customers ──┬── customer_addresses
 ├── orders
 ├── reviews
 ├── wishlists (M:N with products)
 ├── carts
 ├── complaints
 └── coupon_usage
staff ──┬── orders (CS assignments)
 └── complaints

Located in sql/ directory:

This database covers all major relationship patterns:

• 10 CHECK constraints: price >= 0, quantity > 0, rating BETWEEN 1 AND 5, grade IN (...), etc.
• 7 UNIQUE constraints: email, SKU, order_number, coupon code, slug, (customer_id, product_id)
• 38 indexes: Covering all foreign keys and common query patterns

• 10-year growth curve: Startup → Growth → COVID boom → Stabilization
• Seasonal patterns: Black Friday +25%, Summer -15%, Back-to-school +15%
• Time-of-day: Evening peak (8-10 PM), dawn minimum (2-5 AM)
• Day-of-week: Mon/Sat/Sun slightly higher than Wed/Thu

• Pareto distribution: Top 5% customers = heavy buyers; bottom 50% = occasional
• Dormancy model: Older signups have higher dormancy (5+ years = 45%)
• Demographics: Male 65% (computer store), age peak at 30s (33%)
• Non-purchasers: ~25% sign up but never order (realistic funnel)

• No orders before customer signup date
• No orders for discontinued products
• No duplicate products within same order
• Delivery date always after ship date
• No reviews on cancelled/returned orders
• Product price matches latest price history record

The generator supports multiple languages via data/locale/{code}.json:

Adding a new language: Create data/locale/{code}.json following the existing structure.

ecommerce-test-db/
├── generate.py # Main entry point
├── config.yaml # Growth curves, ratios, settings
├── requirements.txt # Python dependencies (Faker, PyYAML)
├── data/
│ ├── categories.json # 53 product categories (hierarchical)
│ ├── products.json # 75 product templates (real brands)
│ ├── suppliers.json # 50 suppliers
│ └── locale/
│ ├── en.json # English text data
│ └── ko.json # Korean text data
├── src/
│ ├── generators/ # Data generators (one per domain)
│ │ ├── base.py # BaseGenerator (locale, RNG, helpers)
│ │ ├── products.py # Categories, suppliers, products, prices
│ │ ├── customers.py # Customers, addresses
│ │ ├── staff.py # Staff/employees
│ │ ├── orders.py # Orders, order items
│ │ ├── payments.py # Payment details (card/bank/e-wallet)
│ │ ├── shipping.py # Delivery tracking
│ │ ├── reviews.py # Product reviews
│ │ ├── inventory.py # Stock transactions
│ │ ├── carts.py # Shopping carts
│ │ ├── coupons.py # Coupons, usage history
│ │ ├── wishlists.py # Wishlist (M:N)
│ │ ├── returns.py # Returns/exchanges
│ │ ├── complaints.py # CS complaints
│ │ └── images.py # Product images + Pexels downloader
│ ├── exporters/
│ │ └── sqlite_exporter.py # SQLite DDL + views + triggers + bulk insert
│ └── utils/
│ ├── fake_phone.py
│ ├── growth.py # Yearly growth calculations
│ └── seasonality.py # Monthly seasonality
├── sql/
│ ├── procedures_mysql.sql # MySQL stored procedures & functions
│ └── procedures_postgresql.sql # PostgreSQL procedures & functions
└── output/ # Generated files
 └── tutorial.db # SQLite database

This database enables practicing virtually every SQL concept:

This tool generates fictitious data using:

• Fake names (via Faker library)
• Fake phone numbers (020-XXXX / 555-XXXX — non-existent area codes)
• Fake email domains (testmail.kr / testmail.com — non-existent)
• Real brand/product names for realism (Samsung, Intel, ASUS, etc.)

All generated data is for testing and educational purposes only.

This project was built by a developer who is not a SQL expert. There may be mistakes, oversights, or suboptimal patterns in the schema design, queries, or tutorial content. Feedback and contributions from experienced SQL practitioners are very welcome.

• If you spot unrealistic schema or data patterns, please let me know
• If you know a better way to write a query, please suggest it
• If you have ideas for additional exercises or topics, please share

Issues and Pull Requests are greatly appreciated.

Youngje Ahn — civilian7@gmail.com