database-schema-design

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Schema Design

数据库Schema设计

When to use this skill

何时使用此技能

이 스킬을 트리거해야 하는 구체적인 상황을 나열합니다:

신규 프로젝트: 새 애플리케이션의 데이터베이스 스키마 설계
스키마 리팩토링: 기존 스키마를 성능이나 확장성을 위해 재설계
관계 정의: 테이블 간 1:1, 1:N, N:M 관계 구현
마이그레이션: 스키마 변경사항을 안전하게 적용
성능 문제: 느린 쿼리를 해결하기 위한 인덱스 및 스키마 최적화

以下是需要触发此技能的具体场景：

新项目：为新应用设计数据库Schema
Schema重构：为提升性能或可扩展性重新设计现有Schema
关系定义：实现表之间的1:1、1:N、N:M关系
迁移：安全应用Schema变更
性能问题：为解决慢查询优化索引及Schema

입력 형식 (Input Format)

输入格式 (Input Format)

사용자로부터 받아야 할 입력의 형식과 필수/선택 정보:

用户需提供的输入格式及必填/可选信息：

필수 정보

必填信息

데이터베이스 종류: PostgreSQL, MySQL, MongoDB, SQLite 등
도메인 설명: 어떤 데이터를 저장할 것인지 (예: 전자상거래, 블로그, SNS)
주요 엔티티: 핵심 데이터 객체들 (예: User, Product, Order)

数据库类型：PostgreSQL、MySQL、MongoDB、SQLite等
领域说明：需要存储的数据类型（例如：电商、博客、社交平台）
核心实体：关键数据对象（例如：User、Product、Order）

선택 정보

可选信息

예상 데이터량: 작음(<10K rows), 중간(10K-1M), 대용량(>1M) (기본값: 중간)
읽기/쓰기 비율: Read-heavy, Write-heavy, Balanced (기본값: Balanced)
트랜잭션 요구사항: ACID 필요 여부 (기본값: true)
샤딩/파티셔닝: 대용량 데이터 분산 필요 여부 (기본값: false)

预计数据量：小(<10K行)、中(10K-1M行)、大(>1M行)（默认：中）
读/写比例：读密集型、写密集型、均衡型（默认：均衡型）
事务要求：是否需要ACID（默认：是）
分片/分区：是否需要分散大容量数据（默认：否）

입력 예시

输入示例

전자상거래 플랫폼의 데이터베이스를 설계해줘:
- DB: PostgreSQL
- 엔티티: User(사용자), Product(상품), Order(주문), Review(리뷰)
- 관계:
  - User는 여러 Order를 가질 수 있음
  - Order는 여러 Product를 포함 (N:M)
  - Review는 User와 Product에 연결
- 예상 데이터: 10만 사용자, 1만 상품
- 읽기 중심 (상품 조회 빈번)

请设计电商平台的数据库：
- DB: PostgreSQL
- 实体: User(用户), Product(商品), Order(订单), Review(评论)
- 关系:
  - User可拥有多个Order
  - Order包含多个Product（N:M）
  - Review关联User和Product
- 预计数据: 10万用户，1万商品
- 读密集型（商品查询频繁）

Instructions

操作步骤

단계별로 정확하게 따라야 할 작업 순서를 명시합니다.

需严格遵循的分步操作流程：

Step 1: 엔티티 및 속성 정의

Step 1: 实体及属性定义

핵심 데이터 객체와 그 속성을 식별합니다.

작업 내용:

비즈니스 요구사항에서 명사 추출 → 엔티티
각 엔티티의 속성(칼럼) 나열
데이터 타입 결정 (VARCHAR, INTEGER, TIMESTAMP, JSON 등)
Primary Key 지정 (UUID vs Auto-increment ID)

예시 (전자상거래):

Users (사용자)
- id: UUID PRIMARY KEY
- email: VARCHAR(255) UNIQUE NOT NULL
- username: VARCHAR(50) UNIQUE NOT NULL
- password_hash: VARCHAR(255) NOT NULL
- created_at: TIMESTAMP DEFAULT NOW()
- updated_at: TIMESTAMP DEFAULT NOW()

Products (상품)
- id: UUID PRIMARY KEY
- name: VARCHAR(255) NOT NULL
- description: TEXT
- price: DECIMAL(10, 2) NOT NULL
- stock: INTEGER DEFAULT 0
- category_id: UUID REFERENCES Categories(id)
- created_at: TIMESTAMP DEFAULT NOW()

Orders (주문)
- id: UUID PRIMARY KEY
- user_id: UUID REFERENCES Users(id)
- total_amount: DECIMAL(10, 2) NOT NULL
- status: VARCHAR(20) DEFAULT 'pending'
- created_at: TIMESTAMP DEFAULT NOW()

OrderItems (주문 상품 - 중간 테이블)
- id: UUID PRIMARY KEY
- order_id: UUID REFERENCES Orders(id) ON DELETE CASCADE
- product_id: UUID REFERENCES Products(id)
- quantity: INTEGER NOT NULL
- price: DECIMAL(10, 2) NOT NULL

识别核心数据对象及其属性。

工作内容:

从业务需求中提取名词 → 实体
列出每个实体的属性（列）
确定数据类型（VARCHAR、INTEGER、TIMESTAMP、JSON等）
指定主键（UUID vs 自增ID）

示例（电商）:

Users (用户)
- id: UUID PRIMARY KEY
- email: VARCHAR(255) UNIQUE NOT NULL
- username: VARCHAR(50) UNIQUE NOT NULL
- password_hash: VARCHAR(255) NOT NULL
- created_at: TIMESTAMP DEFAULT NOW()
- updated_at: TIMESTAMP DEFAULT NOW()

Products (商品)
- id: UUID PRIMARY KEY
- name: VARCHAR(255) NOT NULL
- description: TEXT
- price: DECIMAL(10, 2) NOT NULL
- stock: INTEGER DEFAULT 0
- category_id: UUID REFERENCES Categories(id)
- created_at: TIMESTAMP DEFAULT NOW()

Orders (订单)
- id: UUID PRIMARY KEY
- user_id: UUID REFERENCES Users(id)
- total_amount: DECIMAL(10, 2) NOT NULL
- status: VARCHAR(20) DEFAULT 'pending'
- created_at: TIMESTAMP DEFAULT NOW()

OrderItems (订单项 - 中间表)
- id: UUID PRIMARY KEY
- order_id: UUID REFERENCES Orders(id) ON DELETE CASCADE
- product_id: UUID REFERENCES Products(id)
- quantity: INTEGER NOT NULL
- price: DECIMAL(10, 2) NOT NULL

Step 2: 관계 설계 및 정규화

Step 2: 关系设计与规范化

테이블 간의 관계를 정의하고 정규화를 적용합니다.

작업 내용:

1:1 관계: Foreign Key + UNIQUE 제약
1:N 관계: Foreign Key
N:M 관계: 중간(Junction) 테이블 생성
정규화 레벨 결정 (1NF ~ 3NF)

판단 기준:

OLTP 시스템 → 3NF까지 정규화 (데이터 무결성)
OLAP/분석 시스템 → 비정규화 허용 (쿼리 성능)
읽기 중심 → 일부 비정규화로 JOIN 최소화
쓰기 중심 → 완전 정규화로 중복 제거

예시 (ERD Mermaid):

mermaid

erDiagram
    Users ||--o{ Orders : places
    Orders ||--|{ OrderItems : contains
    Products ||--o{ OrderItems : "ordered in"
    Categories ||--o{ Products : categorizes
    Users ||--o{ Reviews : writes
    Products ||--o{ Reviews : "reviewed by"

    Users {
        uuid id PK
        string email UK
        string username UK
        string password_hash
        timestamp created_at
    }

    Products {
        uuid id PK
        string name
        decimal price
        int stock
        uuid category_id FK
    }

    Orders {
        uuid id PK
        uuid user_id FK
        decimal total_amount
        string status
        timestamp created_at
    }

    OrderItems {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal price
    }

定义表之间的关系并应用规范化。

工作内容:

1:1关系：外键 + UNIQUE约束
1:N关系：外键
N:M关系：创建中间（关联）表
确定规范化级别（1NF ~ 3NF）

判断标准:

OLTP系统 → 规范化至3NF（保证数据完整性）
OLAP/分析系统 → 允许非规范化（提升查询性能）
读密集型 → 部分非规范化以减少JOIN
写密集型 → 完全规范化以消除冗余

示例（Mermaid ERD）:

mermaid

erDiagram
    Users ||--o{ Orders : places
    Orders ||--|{ OrderItems : contains
    Products ||--o{ OrderItems : "ordered in"
    Categories ||--o{ Products : categorizes
    Users ||--o{ Reviews : writes
    Products ||--o{ Reviews : "reviewed by"

    Users {
        uuid id PK
        string email UK
        string username UK
        string password_hash
        timestamp created_at
    }

    Products {
        uuid id PK
        string name
        decimal price
        int stock
        uuid category_id FK
    }

    Orders {
        uuid id PK
        uuid user_id FK
        decimal total_amount
        string status
        timestamp created_at
    }

    OrderItems {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal price
    }

Step 3: 인덱스 전략 수립

Step 3: 制定索引策略

쿼리 성능을 위한 인덱스를 설계합니다.

작업 내용:

Primary Key는 자동으로 인덱스 생성됨
WHERE 절에 자주 사용되는 칼럼 → 인덱스 추가
JOIN에 사용되는 Foreign Key → 인덱스
복합 인덱스 고려 (WHERE col1 = ? AND col2 = ?)
UNIQUE 인덱스 (email, username 등)

확인 사항:

자주 조회되는 칼럼에 인덱스
Foreign Key 칼럼에 인덱스
복합 인덱스 순서 최적화 (선택도 높은 칼럼 먼저)
과도한 인덱스 지양 (INSERT/UPDATE 성능 저하)

예시 (PostgreSQL):

sql

-- Primary Keys (자동 인덱스)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,  -- UNIQUE = 자동 인덱스
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Foreign Keys + 명시적 인덱스
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 복합 인덱스 (status와 created_at 함께 조회 빈번)
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Products 테이블
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id UUID REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);  -- 가격 범위 검색
CREATE INDEX idx_products_name ON products(name);    -- 상품명 검색

-- Full-text search (PostgreSQL)
CREATE INDEX idx_products_name_fts ON products USING GIN(to_tsvector('english', name));
CREATE INDEX idx_products_description_fts ON products USING GIN(to_tsvector('english', description));

为提升查询性能设计索引。

工作内容:

主键会自动创建索引
WHERE子句中频繁使用的列 → 添加索引
JOIN中使用的外键 → 添加索引
考虑复合索引（WHERE col1 = ? AND col2 = ?）
唯一索引（email、username等）

检查项:

为频繁查询的列添加索引
为外键列添加索引
优化复合索引顺序（选择性高的列优先）
避免过度索引（会降低INSERT/UPDATE性能）

示例（PostgreSQL）:

sql

-- 主键（自动创建索引）
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,  -- UNIQUE = 自动创建索引
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 外键 + 显式索引
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 复合索引（status和created_at频繁一起查询）
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Products表
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id UUID REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);  -- 价格范围查询
CREATE INDEX idx_products_name ON products(name);    -- 商品名称查询

-- 全文搜索（PostgreSQL）
CREATE INDEX idx_products_name_fts ON products USING GIN(to_tsvector('english', name));
CREATE INDEX idx_products_description_fts ON products USING GIN(to_tsvector('english', description));

Step 4: 제약조건 및 트리거 설정

Step 4: 设置约束与触发器

데이터 무결성을 위한 제약조건을 추가합니다.

작업 내용:

NOT NULL: 필수 칼럼
UNIQUE: 중복 불가 칼럼
CHECK: 값 범위 제한 (예: price >= 0)
Foreign Key + CASCADE 옵션
Default 값 설정

예시:

sql

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    discount_percent INTEGER CHECK (discount_percent >= 0 AND discount_percent <= 100),
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Trigger: updated_at 자동 갱신
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

添加保证数据完整性的约束。

工作内容:

NOT NULL：必填列
UNIQUE：不可重复列
CHECK：值范围限制（例如: price >= 0）
外键 + CASCADE选项
设置默认值

示例:

sql

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    discount_percent INTEGER CHECK (discount_percent >= 0 AND discount_percent <= 100),
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 触发器：自动更新updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

Step 5: 마이그레이션 스크립트 작성

Step 5: 编写迁移脚本

스키마 변경사항을 안전하게 적용하는 마이그레이션을 작성합니다.

작업 내용:

UP 마이그레이션: 변경 적용
DOWN 마이그레이션: 롤백
트랜잭션으로 래핑
데이터 손실 방지 (ALTER TABLE 신중히)

예시 (SQL 마이그레이션):

sql

-- migrations/001_create_initial_schema.up.sql
BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) UNIQUE NOT NULL,
    parent_id UUID REFERENCES categories(id)
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id UUID REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);

COMMIT;

-- migrations/001_create_initial_schema.down.sql
BEGIN;

DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS users CASCADE;

COMMIT;

编写用于安全应用Schema变更的迁移脚本。

工作内容:

UP迁移：应用变更
DOWN迁移：回滚变更
用事务包裹
防止数据丢失（谨慎使用ALTER TABLE）

示例（SQL迁移）:

sql

-- migrations/001_create_initial_schema.up.sql
BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) UNIQUE NOT NULL,
    parent_id UUID REFERENCES categories(id)
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id UUID REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);

COMMIT;

-- migrations/001_create_initial_schema.down.sql
BEGIN;

DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS users CASCADE;

COMMIT;

Output format

输出格式

결과물이 따라야 할 정확한 형식을 정의합니다.

定义结果需遵循的严格格式。

기본 구조

基本结构

프로젝트/
├── database/
│   ├── schema.sql                    # 전체 스키마
│   ├── migrations/
│   │   ├── 001_create_users.up.sql
│   │   ├── 001_create_users.down.sql
│   │   ├── 002_create_products.up.sql
│   │   └── 002_create_products.down.sql
│   ├── seeds/
│   │   └── sample_data.sql           # 테스트 데이터
│   └── docs/
│       ├── ERD.md                     # Mermaid ERD 다이어그램
│       └── SCHEMA.md                  # 스키마 설명 문서
└── README.md

项目/
├── database/
│   ├── schema.sql                    # 完整Schema
│   ├── migrations/
│   │   ├── 001_create_users.up.sql
│   │   ├── 001_create_users.down.sql
│   │   ├── 002_create_products.up.sql
│   │   └── 002_create_products.down.sql
│   ├── seeds/
│   │   └── sample_data.sql           # 测试数据
│   └── docs/
│       ├── ERD.md                     # Mermaid ERD图
│       └── SCHEMA.md                  # Schema说明文档
└── README.md

ERD 다이어그램 (Mermaid 형식)

ERD图（Mermaid格式）

markdown

undefined

markdown

undefined

Database Schema

Entity Relationship Diagram

实体关系图

```mermaid erDiagram Users ||--o{ Orders : places Orders ||--|{ OrderItems : contains Products ||--o{ OrderItems : "ordered in"

Users {
    uuid id PK
    string email UK
    string username UK
}

Products {
    uuid id PK
    string name
    decimal price
}

```

```mermaid erDiagram Users ||--o{ Orders : places Orders ||--|{ OrderItems : contains Products ||--o{ OrderItems : "ordered in"

Users {
    uuid id PK
    string email UK
    string username UK
}

Products {
    uuid id PK
    string name
    decimal price
}

```

Table Descriptions

表说明

users

Purpose: Store user account information
Indexes: email, username
Estimated rows: 100,000

用途: 存储用户账户信息
索引: email, username
预计行数: 100,000

products

Purpose: Product catalog
Indexes: category_id, price, name
Estimated rows: 10,000

undefined

用途: 商品目录
索引: category_id, price, name
预计行数: 10,000

undefined

Constraints

约束规则

반드시 지켜야 할 규칙과 금지 사항을 명시합니다.

必须遵守的规则与禁止事项：

필수 규칙 (MUST)

必守规则（MUST）

Primary Key 필수: 모든 테이블에 Primary Key 정의
- 레코드 고유 식별
- 참조 무결성 보장
Foreign Key 명시: 관계가 있는 테이블은 반드시 Foreign Key 설정
- ON DELETE CASCADE/SET NULL 옵션 명시
- Orphan 레코드 방지
NOT NULL 적절히 사용: 필수 칼럼은 NOT NULL
- NULL 허용 여부 명확히
- 기본값 제공 권장

必填主键: 所有表必须定义主键
- 唯一标识记录
- 保证引用完整性
显式外键: 存在关系的表必须设置外键
- 明确ON DELETE CASCADE/SET NULL选项
- 防止孤立记录
合理使用NOT NULL: 必填列设置NOT NULL
- 明确是否允许NULL
- 建议提供默认值

금지 사항 (MUST NOT)

禁止事项（MUST NOT）

EAV 패턴 남용: Entity-Attribute-Value 패턴은 특별한 경우에만
- 쿼리 복잡도 급증
- 성능 저하
과도한 비정규화: 성능을 위한 비정규화는 신중히
- 데이터 일관성 문제
- 업데이트 이상 발생 위험
민감정보 평문 저장: 비밀번호, 카드번호 등은 절대 평문 저장 금지
- 해싱/암호화 필수
- 법적 책임 문제

滥用EAV模式: 实体-属性-值模式仅适用于特殊场景
- 会大幅增加查询复杂度
- 导致性能下降
过度非规范化: 为提升性能的非规范化需谨慎
- 引发数据一致性问题
- 存在更新异常风险
明文存储敏感信息: 绝对禁止明文存储密码、卡号等敏感信息
- 必须进行哈希/加密
- 避免法律责任风险

보안 규칙

安全规则

최소 권한 원칙: 애플리케이션 DB 계정은 필요한 권한만 부여
SQL Injection 방지: Prepared Statements/Parameterized Queries 사용
민감 칼럼 암호화: 개인정보는 암호화 저장 고려

最小权限原则: 应用数据库账户仅授予必要权限
防止SQL注入: 使用预编译语句/参数化查询
加密敏感列: 考虑加密存储个人信息

Examples

示例

실제 사용 사례를 통해 스킬의 적용 방법을 보여줍니다.

通过实际使用案例展示技能的应用方法。

예시 1: 블로그 플랫폼 스키마

示例1: 博客平台Schema

상황: Medium 스타일의 블로그 플랫폼 데이터베이스 설계

사용자 요청:

블로그 플랫폼을 위한 PostgreSQL 스키마를 설계해줘:
- 사용자는 여러 포스트를 작성
- 포스트는 여러 태그를 가짐 (N:M)
- 사용자는 포스트에 좋아요, 북마크 가능
- 댓글 기능 (대댓글 지원)

최종 결과:

sql

-- Users
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Posts
CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published_at);
CREATE INDEX idx_posts_slug ON posts(slug);

-- Tags
CREATE TABLE tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

-- Post-Tag relationship (N:M)
CREATE TABLE post_tags (
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_post ON post_tags(post_id);
CREATE INDEX idx_post_tags_tag ON post_tags(tag_id);

-- Likes
CREATE TABLE post_likes (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

-- Bookmarks
CREATE TABLE post_bookmarks (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

-- Comments (self-referencing for nested comments)
CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_comments_post ON comments(post_id);
CREATE INDEX idx_comments_author ON comments(author_id);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id);

场景: 设计Medium风格博客平台的数据库

用户请求:

请设计博客平台的PostgreSQL Schema：
- 用户可撰写多篇文章
- 文章可关联多个标签（N:M）
- 用户可点赞、收藏文章
- 支持评论功能（含回复）

最终结果:

sql

-- Users
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Posts
CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published_at);
CREATE INDEX idx_posts_slug ON posts(slug);

-- Tags
CREATE TABLE tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

-- 文章-标签关系（N:M）
CREATE TABLE post_tags (
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_post ON post_tags(post_id);
CREATE INDEX idx_post_tags_tag ON post_tags(tag_id);

-- 点赞
CREATE TABLE post_likes (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

-- 收藏
CREATE TABLE post_bookmarks (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

-- 评论（自引用实现嵌套评论）
CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_comments_post ON comments(post_id);
CREATE INDEX idx_comments_author ON comments(author_id);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id);

예시 2: MongoDB 스키마 (NoSQL)

示例2: MongoDB Schema（NoSQL）

상황: 실시간 채팅 앱을 위한 MongoDB 스키마

사용자 요청:

실시간 채팅 앱의 MongoDB 스키마를 설계해줘.
읽기가 매우 빈번하고, 메시지 히스토리를 빠르게 조회해야 해.

최종 결과:

javascript

// users collection
{
  _id: ObjectId,
  username: String,  // indexed, unique
  email: String,     // indexed, unique
  avatar_url: String,
  status: String,    // 'online', 'offline', 'away'
  last_seen: Date,
  created_at: Date
}

// conversations collection (비정규화 - 읽기 최적화)
{
  _id: ObjectId,
  participants: [    // indexed
    {
      user_id: ObjectId,
      username: String,
      avatar_url: String
    }
  ],
  last_message: {    // 비정규화로 최근 메시지 빠른 조회
    content: String,
    sender_id: ObjectId,
    sent_at: Date
  },
  unread_counts: {   // 각 참여자별 읽지 않은 메시지 수
    "user_id_1": 5,
    "user_id_2": 0
  },
  created_at: Date,
  updated_at: Date
}

// messages collection
{
  _id: ObjectId,
  conversation_id: ObjectId,  // indexed
  sender_id: ObjectId,
  content: String,
  attachments: [
    {
      type: String,  // 'image', 'file', 'video'
      url: String,
      filename: String
    }
  ],
  read_by: [ObjectId],  // 읽은 사용자 ID 배열
  sent_at: Date,        // indexed
  edited_at: Date
}

// Indexes
db.users.createIndex({ username: 1 }, { unique: true });
db.users.createIndex({ email: 1 }, { unique: true });

db.conversations.createIndex({ "participants.user_id": 1 });
db.conversations.createIndex({ updated_at: -1 });

db.messages.createIndex({ conversation_id: 1, sent_at: -1 });
db.messages.createIndex({ sender_id: 1 });

설계 특징:

읽기 최적화를 위한 비정규화 (last_message 임베딩)
자주 조회되는 필드에 인덱스
배열 필드 활용 (participants, read_by)

场景: 设计实时聊天应用的MongoDB Schema

用户请求:

请设计实时聊天应用的MongoDB Schema。
读操作非常频繁，需要快速查询消息历史。

最终结果:

javascript

// users 集合
{
  _id: ObjectId,
  username: String,  // 已索引，唯一
  email: String,     // 已索引，唯一
  avatar_url: String,
  status: String,    // 'online', 'offline', 'away'
  last_seen: Date,
  created_at: Date
}

// conversations 集合（非规范化 - 优化读性能）
{
  _id: ObjectId,
  participants: [    // 已索引
    {
      user_id: ObjectId,
      username: String,
      avatar_url: String
    }
  ],
  last_message: {    // 非规范化实现快速查询最新消息
    content: String,
    sender_id: ObjectId,
    sent_at: Date
  },
  unread_counts: {   // 每个参与者的未读消息数
    "user_id_1": 5,
    "user_id_2": 0
  },
  created_at: Date,
  updated_at: Date
}

// messages 集合
{
  _id: ObjectId,
  conversation_id: ObjectId,  // 已索引
  sender_id: ObjectId,
  content: String,
  attachments: [
    {
      type: String,  // 'image', 'file', 'video'
      url: String,
      filename: String
    }
  ],
  read_by: [ObjectId],  // 已读用户ID数组
  sent_at: Date,        // 已索引
  edited_at: Date
}

// 索引
db.users.createIndex({ username: 1 }, { unique: true });
db.users.createIndex({ email: 1 }, { unique: true });

db.conversations.createIndex({ "participants.user_id": 1 });
db.conversations.createIndex({ updated_at: -1 });

db.messages.createIndex({ conversation_id: 1, sent_at: -1 });
db.messages.createIndex({ sender_id: 1 });

设计特点:

为优化读性能采用非规范化（嵌入last_message）
为频繁查询的字段创建索引
利用数组字段（participants、read_by）

Best practices

最佳实践

품질 향상

提升质量

명명 규칙 일관성: 테이블/칼럼 이름은 snake_case 사용
- users, post_tags, created_at
- 복수형/단수형 일관되게 (테이블은 복수, 칼럼은 단수 등)
Soft Delete 고려: 중요 데이터는 물리 삭제 대신 논리 삭제
- deleted_at TIMESTAMP (NULL이면 활성, NOT NULL이면 삭제됨)
- 실수로 삭제한 데이터 복구 가능
- 감사(Audit) 추적
Timestamp 필수: created_at, updated_at은 대부분 테이블에 포함
- 데이터 추적 및 디버깅
- 시계열 분석

统一命名规则: 表/列名使用snake_case
- 例如: users, post_tags, created_at
- 统一单复数（表用复数，列用单数等）
考虑软删除: 重要数据采用逻辑删除而非物理删除
- 添加deleted_at TIMESTAMP（NULL表示活跃，非NULL表示已删除）
- 可恢复误删数据
- 便于审计追踪
必加时间戳: 大多数表需包含created_at、updated_at
- 便于数据追踪与调试
- 支持时序分析

효율성 개선

提升效率

Partial Indexes: 조건부 인덱스로 인덱스 크기 최소화

sql

CREATE INDEX idx_posts_published ON posts(published_at) WHERE published_at IS NOT NULL;

Materialized Views: 복잡한 집계 쿼리는 Materialized View로 캐싱
Partitioning: 대용량 테이블은 날짜/범위 기준 파티셔닝

部分索引: 使用条件索引最小化索引大小

sql

CREATE INDEX idx_posts_published ON posts(published_at) WHERE published_at IS NOT NULL;

物化视图: 用物化视图缓存复杂聚合查询
分区: 大容量表按日期/范围分区

자주 발생하는 문제 (Common Issues)

常见问题（Common Issues）

문제 1: N+1 쿼리 문제

问题1: N+1查询问题

증상: 한 쿼리로 충분한데 여러 번 DB 호출

원인: JOIN 없이 반복문에서 개별 조회

해결방법:

sql

-- ❌ 나쁜 예: N+1 queries
SELECT * FROM posts;  -- 1번
-- 각 post마다
SELECT * FROM users WHERE id = ?;  -- N번

-- ✅ 좋은 예: 1 query
SELECT posts.*, users.username, users.avatar_url
FROM posts
JOIN users ON posts.author_id = users.id;

症状: 本可一次查询完成却多次调用数据库

原因: 未使用JOIN而是在循环中逐个查询

解决方法:

sql

-- ❌ 不良示例: N+1查询
SELECT * FROM posts;  -- 1次
-- 每个post单独查询
SELECT * FROM users WHERE id = ?;  -- N次

-- ✅ 良好示例: 1次查询
SELECT posts.*, users.username, users.avatar_url
FROM posts
JOIN users ON posts.author_id = users.id;

문제 2: 인덱스 없는 Foreign Key로 인한 느린 JOIN

问题2: 无索引外键导致JOIN缓慢

증상: JOIN 쿼리가 매우 느림

원인: Foreign Key 칼럼에 인덱스 누락

해결방법:

sql

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

症状: JOIN查询速度极慢

原因: 外键列未创建索引

解决方法:

sql

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

문제 3: UUID vs Auto-increment 성능

问题3: UUID vs 自增ID的性能差异

증상: UUID Primary Key 사용 시 삽입 성능 저하

원인: UUID는 랜덤하여 인덱스 조각화

해결방법:

PostgreSQL:
```
uuid_generate_v7()
```
사용 (시간 순서 UUID)
MySQL:
```
UUID_TO_BIN(UUID(), 1)
```
사용
또는 Auto-increment BIGINT 사용 고려

症状: 使用UUID作为主键时插入性能下降

原因: UUID是随机值，会导致索引碎片化

解决方法:

PostgreSQL: 使用
```
uuid_generate_v7()
```
（时序UUID）
MySQL: 使用
```
UUID_TO_BIN(UUID(), 1)
```
或考虑使用自增BIGINT

References

参考资料

공식 문서

官方文档

도구

工具

dbdiagram.io - ERD 다이어그램 작성
PgModeler - PostgreSQL 모델링 도구
Prisma - ORM + 마이그레이션

dbdiagram.io - 绘制ERD图
PgModeler - PostgreSQL建模工具
Prisma - ORM + 迁移工具

학습 자료

学习资料

Database Design Course (freecodecamp)
Use The Index, Luke - SQL 인덱싱 가이드

Database Design Course (freecodecamp)
Use The Index, Luke - SQL索引指南

Metadata

元数据

버전

版本

현재 버전: 1.0.0
최종 업데이트: 2025-01-01
호환 플랫폼: Claude, ChatGPT, Gemini

当前版本: 1.0.0
最后更新: 2025-01-01
兼容平台: Claude, ChatGPT, Gemini