--- name: db-schema-designer description: | 트리거: "DB 설계", "테이블 설계", "ERD 만들어줘", "데이터베이스 설계", "스키마 설계해줘", "테이블 구조 잡아줘" 수행: 요구사항 문장을 분석하여 ERD(텍스트/Mermaid 형식) + DDL SQL을 생성한다. 스네이크_케이스 명명 규칙, 인덱스 전략(PK/FK/조회 빈도 기반), 외래키 제약, NOT NULL/DEFAULT/CHECK 제약을 포함한다. 대상 DB: MySQL 8.0+ / PostgreSQL 14+. 요청 없으면 PostgreSQL 기준으로 생성. 출력: ERD(Mermaid) → 테이블 설명 → DDL SQL → 인덱스 전략 설명. --- # DB 스키마 설계기 ## 목적 자연어 요구사항을 분석하여 정규화된 ERD와 즉시 실행 가능한 DDL을 생성한다. 명명 규칙, 인덱스 전략, 외래키 관계, 적절한 데이터 타입 선택까지 실무 수준으로 설계한다. ## 실행 절차 1. **요구사항 분석**: 엔티티 식별, 속성 추출, 관계(1:1, 1:N, N:M) 파악 2. **정규화 검토**: 1NF~3NF 적용, 중복 제거, 함수적 종속성 분리 3. **명명 규칙 적용**: 테이블명 복수형 스네이크_케이스, 컬럼명 스네이크_케이스, PK는 `id` 또는 `{table}_id` 4. **데이터 타입 선택**: VARCHAR vs TEXT, INT vs BIGINT, DECIMAL vs NUMERIC 등 적절한 타입 결정 5. **제약 조건 정의**: NOT NULL, UNIQUE, DEFAULT, CHECK, FK 참조 무결성 6. **인덱스 전략 수립**: PK 자동, FK 컬럼, 조회 빈도 높은 컬럼, 복합 인덱스 대상 분석 7. **Mermaid ERD 생성**: 관계 표기법(||--o{, }|--|{) 포함 8. **DDL 생성**: CREATE TABLE + ALTER TABLE(FK) + CREATE INDEX 순서로 출력 ## 출력 형식 ### 1. Mermaid ERD ```mermaid erDiagram USERS ||--o{ ORDERS : "places" ... ``` ### 2. 테이블 설명 | 테이블 | 설명 | 주요 컬럼 | |--------|------|-----------| ### 3. DDL (PostgreSQL 또는 MySQL) ```sql -- 테이블 생성 (의존성 없는 테이블 먼저) CREATE TABLE users ( ... ); -- FK 포함 테이블 CREATE TABLE orders ( ... ); -- 인덱스 CREATE INDEX idx_orders_user_id ON orders(user_id); ``` ### 4. 인덱스 전략 설명 - 각 인덱스 생성 이유와 예상 효과 설명 ## 사용 예시 ### 입력 ``` 쇼핑몰 DB 설계해줘. - 사용자는 여러 주문을 할 수 있음 - 주문에는 여러 상품이 포함됨 (주문 상품) - 상품은 카테고리에 속함 - 사용자는 배송지 주소를 여러 개 등록 가능 - 주문 상태: 결제대기/결제완료/배송준비/배송중/배송완료/취소 ``` ### 출력 (Mermaid ERD) ```mermaid erDiagram users ||--o{ orders : "places" users ||--o{ addresses : "has" orders ||--o{ order_items : "contains" orders }o--|| addresses : "ships to" products ||--o{ order_items : "included in" categories ||--o{ products : "classifies" users { bigint id PK varchar(50) username UK varchar(100) email UK varchar(255) password_hash boolean is_active timestamp created_at timestamp updated_at } orders { bigint id PK bigint user_id FK bigint address_id FK varchar(20) status numeric(12_2) total_amount timestamp ordered_at timestamp updated_at } order_items { bigint id PK bigint order_id FK bigint product_id FK integer quantity numeric(10_2) unit_price numeric(10_2) subtotal } products { bigint id PK bigint category_id FK varchar(200) name text description numeric(10_2) price integer stock_quantity boolean is_active timestamp created_at } categories { int id PK int parent_id FK varchar(100) name varchar(100) slug UK int sort_order } addresses { bigint id PK bigint user_id FK varchar(100) recipient_name varchar(20) phone varchar(10) zipcode varchar(200) address1 varchar(100) address2 boolean is_default } ``` ### 출력 (DDL - PostgreSQL) ```sql -- ============================================ -- 쇼핑몰 스키마 DDL (PostgreSQL 14+) -- ============================================ -- 1. 카테고리 (의존성 없음) CREATE TABLE categories ( id SERIAL PRIMARY KEY, parent_id INT REFERENCES categories(id) ON DELETE SET NULL, name VARCHAR(100) NOT NULL, slug VARCHAR(100) NOT NULL UNIQUE, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- 2. 사용자 CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, is_active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- 3. 배송지 CREATE TABLE addresses ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, recipient_name VARCHAR(100) NOT NULL, phone VARCHAR(20) NOT NULL, zipcode VARCHAR(10) NOT NULL, address1 VARCHAR(200) NOT NULL, address2 VARCHAR(100), is_default BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- 4. 상품 CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, category_id INT NOT NULL REFERENCES categories(id), name VARCHAR(200) NOT NULL, description TEXT, price NUMERIC(10, 2) NOT NULL CHECK (price >= 0), stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0), is_active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- 5. 주문 CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), address_id BIGINT REFERENCES addresses(id) ON DELETE SET NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','paid','preparing','shipping','delivered','cancelled')), total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0), ordered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- 6. 주문 상품 (연결 테이블) CREATE TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id BIGINT NOT NULL REFERENCES products(id), quantity INT NOT NULL CHECK (quantity > 0), unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0), subtotal NUMERIC(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED ); -- ============================================ -- 인덱스 -- ============================================ CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_addresses_user_id ON addresses(user_id); CREATE INDEX idx_products_category ON products(category_id); CREATE INDEX idx_products_active ON products(is_active, created_at DESC); CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_status ON orders(status, ordered_at DESC); CREATE INDEX idx_order_items_order ON order_items(order_id); CREATE INDEX idx_order_items_product ON order_items(product_id); CREATE UNIQUE INDEX idx_categories_slug ON categories(slug); ``` ### 인덱스 전략 설명 | 인덱스 | 이유 | |--------|------| | `idx_users_email` | 로그인 시 이메일로 사용자 조회 빈번 | | `idx_orders_user_id` | 마이페이지 주문 목록 조회 | | `idx_orders_status` | 관리자 상태별 주문 조회, ordered_at DESC로 최신순 정렬 지원 | | `idx_products_active` | 활성 상품 목록 페이지, is_active=TRUE 필터 + 최신순 정렬 복합 | | `idx_order_items_order` | 주문 상세 페이지에서 주문 상품 조회 | ## 주의사항 - **BIGINT vs INT**: 사용자·주문·상품 등 증가 가능성이 있는 테이블은 `BIGINT`, 코드성 테이블(카테고리, 상태 등)은 `INT`. - **NUMERIC vs FLOAT**: 금액은 반드시 `NUMERIC(n, 2)`. `FLOAT`/`DOUBLE`은 부동소수점 오차 발생. - **ON DELETE 정책**: 핵심 데이터(주문)는 `RESTRICT` 또는 `SET NULL`, 하위 데이터(주문상품)는 `CASCADE`. - **`updated_at` 자동 갱신**: PostgreSQL은 트리거, MySQL은 `ON UPDATE CURRENT_TIMESTAMP` 필요. - **N:M 관계**: 중간 테이블(order_items)에 추가 속성(quantity, unit_price)이 있으면 독립 엔티티로 설계. - **소프트 삭제**: 실제 삭제 대신 `deleted_at TIMESTAMP` 또는 `is_deleted BOOLEAN` 컬럼으로 처리 권장. - **복합 인덱스 순서**: 등호 조건 컬럼 먼저, 범위/정렬 컬럼 나중에.