Claude Code 数据库设计

# 分析业务需求
claude --design --database

"""
分析以下业务的数据库需求：

业务背景：
- 用户注册登录
- 博客文章发布
- 评论互动
- 用户关注
- 消息通知

请分析：
1. 需要哪些实体
2. 实体间的关系
3. 主要查询场景
4. 性能要求
"""

## 需求分析

### 实体识别

| 实体 | 说明 | 主要属性 |
|------|------|----------|
| User | 用户 | id, username, email, password |
| Post | 文章 | id, title, content, author_id |
| Comment | 评论 | id, content, post_id, user_id |
| Follow | 关注 | follower_id, following_id |
| Notification | 通知 | id, type, user_id, content |

### 关系分析

# 生成 ER 图
claude --design --er-diagram

"""
为博客系统设计 ER 图：

实体：
1. User (用户)
2. Post (文章)
3. Category (分类)
4. Tag (标签)
5. Comment (评论)
6. Follow (关注)

关系：
- User 拥有多个 Post
- Post 属于一个 Category
- Post 有多个 Tag
- User 发表 Comment
- User 关注 User
"""

## ER 图

-- 用户表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    bio TEXT,
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 分类表
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    parent_id INTEGER REFERENCES categories(id),
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 文章表
CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    excerpt TEXT,
    content TEXT NOT NULL,
    cover_image VARCHAR(500),
    view_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    comment_count INTEGER DEFAULT 0,
    is_published BOOLEAN DEFAULT FALSE,
    published_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 标签表
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 文章标签关联表
CREATE TABLE post_tags (
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- 评论表
CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_id UUID REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    like_count INTEGER DEFAULT 0,
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 关注表
CREATE TABLE follows (
    follower_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    following_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id != following_id)
);

-- 通知表
CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(200),
    content TEXT,
    link VARCHAR(500),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

# 分析并生成索引
claude --optimize --indexes

"""
根据以下查询场景设计索引：

高频查询：
1. SELECT * FROM posts WHERE author_id = ? ORDER BY created_at DESC
2. SELECT * FROM posts WHERE slug = ? AND is_published = true
3. SELECT * FROM comments WHERE post_id = ? ORDER BY created_at
4. SELECT * FROM posts WHERE category_id = ? AND is_published = true

全文搜索：
5. SELECT * FROM posts WHERE content LIKE '%keyword%'
"""

-- 用户表索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

-- 文章表索引
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_category ON posts(category_id);
CREATE INDEX idx_posts_slug ON posts(slug) WHERE is_published = TRUE;
CREATE INDEX idx_posts_published_at ON posts(published_at DESC) WHERE is_published = TRUE;
CREATE INDEX idx_posts_author_published ON posts(author_id, is_published, published_at DESC);

-- 评论表索引
CREATE INDEX idx_comments_post ON comments(post_id);
CREATE INDEX idx_comments_user ON comments(user_id);
CREATE INDEX idx_comments_parent ON comments(parent_id);

-- 关注表索引
CREATE INDEX idx_follows_follower ON follows(follower_id);
CREATE INDEX idx_follows_following ON follows(following_id);

-- 通知表索引
CREATE INDEX idx_notifications_user_unread ON notifications(user_id, is_read) WHERE is_read = FALSE;

-- 全文搜索索引
CREATE INDEX idx_posts_content_fts ON posts USING gin(to_tsvector('chinese', content));

# 优化慢查询
claude --optimize --query

"""
优化以下查询：

SELECT p.*, u.username, u.avatar_url,
       c.name as category_name,
       COUNT(DISTINCT cmt.id) as comment_count
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN comments cmt ON p.id = cmt.post_id
WHERE p.is_published = true
GROUP BY p.id, u.username, u.avatar_url, c.name
ORDER BY p.published_at DESC
LIMIT 20;
"""

-- 优化方案：使用子查询替代 JOIN + GROUP BY

SELECT p.*,
       u.username,
       u.avatar_url,
       c.name as category_name,
       p.comment_count
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.is_published = true
ORDER BY p.published_at DESC
LIMIT 20;

-- 添加 covering index
CREATE INDEX idx_posts_published_covering
ON posts(is_published, published_at DESC, author_id, category_id, comment_count);

-- 对于评论数，可以使用触发器或应用层维护
-- 避免每次查询都 COUNT

# 分表设计
claude --design --sharding

"""
为一个日活 1000 万的社交平台设计分库分表：

需求：
1. 用户发表动态
2. 用户点赞、评论
3. 用户间关注
4. 消息推送

请给出：
1. 分片键选择
2. 分表策略
3. 路由方案
4. 跨分片查询方案
"""

## 分库分表方案

### 分片键选择

| 数据 | 分片键 | 理由 |
|------|--------|------|
| 动态 | user_id | 按用户查询为主 |
| 评论 | post_id | 按帖子查询为主 |
| 关注 | user_id | 按用户查询为主 |

### 分表策略

# MongoDB 设计
claude --design --mongodb

"""
设计一个博客系统的 MongoDB 方案：

需求同前
"""

// MongoDB 集合设计

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

// posts 集合（嵌入式设计）
{
  _id: ObjectId,
  author: {
    _id: ObjectId,
    username: String,
    avatar: String
  },
  title: String,
  content: String,
  tags: [String],
  stats: {
    views: Number,
    likes: Number,
    comments: Number
  },
  createdAt: Date,
  updatedAt: Date
}

// posts 索引
db.posts.createIndex({ "author._id": 1, createdAt: -1 });
db.posts.createIndex({ tags: 1, createdAt: -1 });
db.posts.createIndex({ createdAt: -1 });

// comments 集合（引用式设计）
{
  _id: ObjectId,
  post_id: ObjectId,
  author: {
    _id: ObjectId,
    username: String
  },
  content: String,
  parent_id: ObjectId, // 用于嵌套评论
  createdAt: Date
}

db.comments.createIndex({ post_id: 1, createdAt: -1 });