Database Schema Designer

Database Schema Designer

Design production-ready database schemas with best practices built-in.

Quick Start

Just describe your data model:

design a schema for an e-commerce platform with users, products, orders

You'll get a complete SQL schema like:

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL
);

-- 인덱스는 CREATE TABLE 밖에 분리 (MySQL/PostgreSQL 모두 동작)
CREATE INDEX idx_orders_user ON orders(user_id);

위 예시는 MySQL 기준입니다(AUTO_INCREMENT). PostgreSQL은 PK를 BIGINT GENERATED ALWAYS AS IDENTITY(또는 BIGSERIAL)로 쓰세요. 인덱스는 CREATE INDEX로 분리하면 양쪽에서 동작합니다(인라인 INDEX ...는 MySQL/MariaDB 전용 → Postgres 문법 에러). 이 스킬은 DB-First라 대상 DB에 맞춰 설계가 달라집니다.

What to include in your request:

• Entities (users, products, orders)
• Key relationships (users have orders, orders have items)
• Scale hints (high-traffic, millions of records)
• Database preference (SQL/NoSQL) - defaults to SQL if not specified

Triggers

Key Terms

Quick Reference

Process Overview

Your Data Requirements
    |
    v
+-----------------------------------------------------+
| Phase 1: ANALYSIS                                   |
| * Identify entities and relationships               |
| * Determine access patterns (read vs write heavy)   |
| * Choose SQL or NoSQL based on requirements         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 2: DESIGN                                     |
| * Normalize to 3NF (SQL) or embed/reference (NoSQL) |
| * Define primary keys and foreign keys              |
| * Choose appropriate data types                     |
| * Add constraints (UNIQUE, CHECK, NOT NULL)         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 3: OPTIMIZE                                   |
| * Plan indexing strategy                            |
| * Consider denormalization for read-heavy queries   |
| * Add timestamps (created_at, updated_at)           |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 4: MIGRATE                                    |
| * Generate migration scripts (up + down)            |
| * Ensure backward compatibility                     |
| * Plan zero-downtime deployment                     |
+-----------------------------------------------------+
    |
    v
Production-Ready Schema

Commands

Workflow: Start with design schema → iterate with normalize → optimize with add indexes → evolve with migration

Core Principles

Anti-Patterns

Verification Checklist

After designing a schema:

• Every table has a primary key
• All relationships have foreign key constraints
• ON DELETE strategy defined for each FK
• Indexes exist on all foreign keys
• Indexes exist on frequently queried columns
• Appropriate data types (DECIMAL for money, etc.)
• NOT NULL on required fields
• UNIQUE constraints where needed
• CHECK constraints for validation
• created_at and updated_at timestamps
• Migration scripts are reversible
• Tested on staging with production data

Deep Dives (References)

Related Resources

• DB별 구현: 네이티브 작업자 + 프로젝트 DB 버전·schema·migration 도구·테스트
• Supabase Best Practices: skills/supabase-postgres-best-practices/SKILL.md