菜单
Design and optimize database schemas for SQL and NoSQL databases. Use when creating new databases, designing tables, defining relationships, indexing strategies, or database migrations. Handles PostgreSQL, MySQL, MongoDB, normalization, and performance optimization.
默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。
决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
直接命令不会经过审查 Prompt；运行前请先检查来源。
npx skills add https://github.com/autohandai/community-skills --skill database-schema-design命令会保持在同一行。复制前请横向滚动并检查完整内容。
想先保存到本地？可下载 SkillsMP 当前能够提供的文件。
基于 SOC 职业分类
正在显示 SKILL.md
| name | database-schema-design |
| description | Design and optimize database schemas for SQL and NoSQL databases. Use when creating new databases, designing tables, defining relationships, indexing strategies, or database migrations. Handles PostgreSQL, MySQL, MongoDB, normalization, and performance optimization. |
| metadata | {"tags":"database, schema, SQL, NoSQL, PostgreSQL, MySQL, MongoDB, migration","platforms":"Claude, ChatGPT, Gemini"} |
Lists specific situations where this skill should be triggered:
The required and optional input information to collect from the user:
Design a database for an e-commerce platform:
- DB: PostgreSQL
- Entities: User, Product, Order, Review
- Relationships:
- A User can have multiple Orders
- An Order contains multiple Products (N:M)
- A Review is linked to a User and a Product
- Expected data: 100,000 users, 10,000 products
- Read-heavy (frequent product lookups)
Specifies the step-by-step task sequence to follow precisely.
Identify core data objects and their attributes.
Tasks:
Example (E-commerce):
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 (Junction table)
- 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
Define relationships between tables and apply normalization.
Tasks:
Decision Criteria:
Example (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
}
Design indexes for query performance.
Tasks:
Checklist:
Example (PostgreSQL):
-- Primary Keys (auto-indexed)
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL, -- UNIQUE = auto-indexed
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- Foreign Keys + explicit indexes
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);
-- Composite index (status and created_at frequently queried together)
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
-- Products table
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); -- price range search
CREATE INDEX idx_products_name ON products(name); -- product name search
-- 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));
Add constraints to ensure data integrity.
Tasks:
Example:
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: auto-update 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();
Write migrations that safely apply schema changes.
Tasks:
Example (SQL migration):
-- 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;
Defines the exact format that deliverables should follow.
project/
├── database/
│ ├── schema.sql # full 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 # test data
│ └── docs/
│ ├── ERD.md # Mermaid ERD diagram
│ └── SCHEMA.md # schema documentation
└── README.md
# 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
}
\`\`\`
## Table Descriptions
### users
- **Purpose**: Store user account information
- **Indexes**: email, username
- **Estimated rows**: 100,000
### products
- **Purpose**: Product catalog
- **Indexes**: category_id, price, name
- **Estimated rows**: 10,000
Specifies mandatory rules and prohibited actions.
Primary Key Required: Define a Primary Key on every table
Explicit Foreign Keys: Tables with relationships must define Foreign Keys
Use NOT NULL Appropriately: Required columns must be NOT NULL
Avoid EAV Pattern Abuse: Use the Entity-Attribute-Value pattern only in special cases
Excessive Denormalization: Be careful when denormalizing for performance
No Plaintext Storage of Sensitive Data: Never store passwords, card numbers, etc. in plaintext
Demonstrates how to apply the skill through real-world use cases.
Situation: Database design for a Medium-style blog platform
User Request:
Design a PostgreSQL schema for a blog platform:
- Users can write multiple posts
- Posts can have multiple tags (N:M)
- Users can like and bookmark posts
- Comment feature (with nested replies)
Final Result:
-- 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);
Situation: MongoDB schema for a real-time chat app
User Request:
Design a MongoDB schema for a real-time chat app.
Reads are very frequent, and message history needs to be retrieved quickly.
Final Result:
// 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 (denormalized - read-optimized)
{
_id: ObjectId,
participants: [ // indexed
{
user_id: ObjectId,
username: String,
avatar_url: String
}
],
last_message: { // denormalized for fast recent-message retrieval
content: String,
sender_id: ObjectId,
sent_at: Date
},
unread_counts: { // unread message count per participant
"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], // array of user IDs who have read the message
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 });
Design Highlights:
Naming Convention Consistency: Use snake_case for table/column names
Consider Soft Delete: Use logical deletion instead of physical deletion for important data
Timestamps Required: Include created_at and updated_at in most tables
CREATE INDEX idx_posts_published ON posts(published_at) WHERE published_at IS NOT NULL;
Symptom: Multiple DB calls when a single query would suffice
Cause: Individual lookups in a loop without JOINs
Solution:
-- ❌ Bad example: N+1 queries
SELECT * FROM posts; -- 1 time
-- for each post
SELECT * FROM users WHERE id = ?; -- N times
-- ✅ Good example: 1 query
SELECT posts.*, users.username, users.avatar_url
FROM posts
JOIN users ON posts.author_id = users.id;
Symptom: JOIN queries are very slow
Cause: Missing index on Foreign Key column
Solution:
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);
Symptom: Insert performance degradation when using UUID Primary Keys
Cause: UUIDs are random, causing index fragmentation
Solution:
uuid_generate_v7() (time-ordered UUID)UUID_TO_BIN(UUID(), 1)#database #schema #PostgreSQL #MySQL #MongoDB #SQL #NoSQL #migration #ERD