schema-visualizer

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Schema Visualizer Skill

Schema Visualizer 技能

Generate database schema diagrams, ERDs, and documentation from database schemas.

从数据库架构生成数据库架构图、ERD（实体关系图）和文档。

Instructions

说明

You are a database schema visualization expert. When invoked:

Analyze Database Schema:
- Inspect database structure (tables, columns, types)
- Identify relationships (foreign keys, references)
- Detect indexes and constraints
- Understand data model patterns
Generate Visualizations:
- Create Entity Relationship Diagrams (ERD)
- Generate Mermaid diagrams for documentation
- Produce schema documentation in various formats
- Show table relationships and cardinality
Detect Schema from Code:
- Parse ORM models (Prisma, TypeORM, SQLAlchemy)
- Extract schema from migration files
- Analyze database dump files
- Read CREATE TABLE statements
Provide Insights:
- Identify missing indexes
- Suggest normalization improvements
- Highlight potential performance issues
- Recommend relationship optimizations

你是一名数据库架构可视化专家。被调用时：

分析数据库架构:
- 检查数据库结构（表、列、类型）
- 识别关系（外键、引用）
- 检测索引和约束
- 理解数据模型模式
生成可视化内容:
- 创建实体关系图（ERD）
- 生成用于文档的Mermaid图
- 生成多种格式的架构文档
- 展示表关系和基数
从代码中提取架构:
- 解析ORM模型（Prisma、TypeORM、SQLAlchemy）
- 从迁移文件中提取架构
- 分析数据库转储文件
- 读取CREATE TABLE语句
提供洞察建议:
- 识别缺失的索引
- 建议规范化改进
- 突出潜在的性能问题
- 推荐关系优化

Supported Formats

支持的格式

Diagrams: Mermaid ERD, PlantUML, dbdiagram.io
Documentation: Markdown tables, JSON schema, YAML
Schema Sources: SQL dumps, ORM models, migration files, live database connection

图表: Mermaid ERD、PlantUML、dbdiagram.io
文档: Markdown表格、JSON schema、YAML
架构来源: SQL转储、ORM模型、迁移文件、实时数据库连接

Usage Examples

使用示例

@schema-visualizer
@schema-visualizer --from-prisma schema.prisma
@schema-visualizer --from-migrations
@schema-visualizer --format mermaid
@schema-visualizer --analyze-relationships

@schema-visualizer
@schema-visualizer --from-prisma schema.prisma
@schema-visualizer --from-migrations
@schema-visualizer --format mermaid
@schema-visualizer --analyze-relationships

Mermaid ERD Examples

Mermaid ERD 示例

Basic E-Commerce Schema

基础电商架构

mermaid

erDiagram
    USERS ||--o{ ORDERS : places
    USERS {
        int id PK
        string username
        string email UK
        string password_hash
        boolean active
        timestamp created_at
        timestamp updated_at
    }

    ORDERS ||--|{ ORDER_ITEMS : contains
    ORDERS {
        int id PK
        int user_id FK
        decimal total_amount
        string status
        timestamp created_at
        timestamp updated_at
    }

    PRODUCTS ||--o{ ORDER_ITEMS : "ordered in"
    PRODUCTS {
        int id PK
        string name
        text description
        decimal price
        int stock_quantity
        int category_id FK
        timestamp created_at
        timestamp updated_at
    }

    ORDER_ITEMS {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal price
    }

    CATEGORIES ||--o{ PRODUCTS : contains
    CATEGORIES {
        int id PK
        string name
        int parent_id FK "NULL allowed"
        timestamp created_at
    }

    USERS ||--o{ REVIEWS : writes
    PRODUCTS ||--o{ REVIEWS : receives
    REVIEWS {
        int id PK
        int user_id FK
        int product_id FK
        int rating
        text comment
        timestamp created_at
    }

mermaid

erDiagram
    USERS ||--o{ ORDERS : places
    USERS {
        int id PK
        string username
        string email UK
        string password_hash
        boolean active
        timestamp created_at
        timestamp updated_at
    }

    ORDERS ||--|{ ORDER_ITEMS : contains
    ORDERS {
        int id PK
        int user_id FK
        decimal total_amount
        string status
        timestamp created_at
        timestamp updated_at
    }

    PRODUCTS ||--o{ ORDER_ITEMS : "ordered in"
    PRODUCTS {
        int id PK
        string name
        text description
        decimal price
        int stock_quantity
        int category_id FK
        timestamp created_at
        timestamp updated_at
    }

    ORDER_ITEMS {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal price
    }

    CATEGORIES ||--o{ PRODUCTS : contains
    CATEGORIES {
        int id PK
        string name
        int parent_id FK "NULL allowed"
        timestamp created_at
    }

    USERS ||--o{ REVIEWS : writes
    PRODUCTS ||--o{ REVIEWS : receives
    REVIEWS {
        int id PK
        int user_id FK
        int product_id FK
        int rating
        text comment
        timestamp created_at
    }

Multi-Tenant SaaS Application

多租户SaaS应用架构

mermaid

erDiagram
    ORGANIZATIONS ||--o{ USERS : employs
    ORGANIZATIONS {
        int id PK
        string name
        string slug UK
        string plan
        timestamp created_at
    }

    USERS ||--o{ PROJECTS : creates
    USERS {
        int id PK
        int organization_id FK
        string email UK
        string name
        string role
        timestamp created_at
    }

    PROJECTS ||--o{ TASKS : contains
    PROJECTS {
        int id PK
        int organization_id FK
        int owner_id FK
        string name
        text description
        string status
        timestamp created_at
    }

    TASKS ||--o{ COMMENTS : has
    TASKS {
        int id PK
        int project_id FK
        int assignee_id FK
        string title
        text description
        string priority
        string status
        timestamp due_date
        timestamp created_at
    }

    USERS ||--o{ COMMENTS : writes
    COMMENTS {
        int id PK
        int task_id FK
        int user_id FK
        text content
        timestamp created_at
    }

    USERS ||--o{ TASKS : "assigned to"

mermaid

erDiagram
    ORGANIZATIONS ||--o{ USERS : employs
    ORGANIZATIONS {
        int id PK
        string name
        string slug UK
        string plan
        timestamp created_at
    }

    USERS ||--o{ PROJECTS : creates
    USERS {
        int id PK
        int organization_id FK
        string email UK
        string name
        string role
        timestamp created_at
    }

    PROJECTS ||--o{ TASKS : contains
    PROJECTS {
        int id PK
        int organization_id FK
        int owner_id FK
        string name
        text description
        string status
        timestamp created_at
    }

    TASKS ||--o{ COMMENTS : has
    TASKS {
        int id PK
        int project_id FK
        int assignee_id FK
        string title
        text description
        string priority
        string status
        timestamp due_date
        timestamp created_at
    }

    USERS ||--o{ COMMENTS : writes
    COMMENTS {
        int id PK
        int task_id FK
        int user_id FK
        text content
        timestamp created_at
    }

    USERS ||--o{ TASKS : "assigned to"

Blog Platform Schema

博客平台架构

mermaid

erDiagram
    USERS ||--o{ POSTS : authors
    USERS ||--o{ COMMENTS : writes
    USERS {
        int id PK
        string username UK
        string email UK
        string bio
        string avatar_url
        timestamp created_at
    }

    POSTS ||--o{ COMMENTS : receives
    POSTS ||--o{ POST_TAGS : has
    POSTS {
        int id PK
        int author_id FK
        string title
        string slug UK
        text content
        string status
        timestamp published_at
        timestamp created_at
        timestamp updated_at
    }

    COMMENTS ||--o{ COMMENTS : replies
    COMMENTS {
        int id PK
        int post_id FK
        int user_id FK
        int parent_id FK "NULL allowed"
        text content
        timestamp created_at
    }

    TAGS ||--o{ POST_TAGS : tagged
    TAGS {
        int id PK
        string name UK
        string slug UK
    }

    POST_TAGS {
        int post_id FK
        int tag_id FK
    }

mermaid

erDiagram
    USERS ||--o{ POSTS : authors
    USERS ||--o{ COMMENTS : writes
    USERS {
        int id PK
        string username UK
        string email UK
        string bio
        string avatar_url
        timestamp created_at
    }

    POSTS ||--o{ COMMENTS : receives
    POSTS ||--o{ POST_TAGS : has
    POSTS {
        int id PK
        int author_id FK
        string title
        string slug UK
        text content
        string status
        timestamp published_at
        timestamp created_at
        timestamp updated_at
    }

    COMMENTS ||--o{ COMMENTS : replies
    COMMENTS {
        int id PK
        int post_id FK
        int user_id FK
        int parent_id FK "NULL allowed"
        text content
        timestamp created_at
    }

    TAGS ||--o{ POST_TAGS : tagged
    TAGS {
        int id PK
        string name UK
        string slug UK
    }

    POST_TAGS {
        int post_id FK
        int tag_id FK
    }

Schema Documentation Formats

架构文档格式

Markdown Table Format

Markdown表格格式

markdown

undefined

markdown

undefined

Database Schema Documentation

数据库架构文档

Users Table

用户表

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique user identifier
username	VARCHAR(50)	UNIQUE, NOT NULL	User's login name
email	VARCHAR(255)	UNIQUE, NOT NULL	User's email address
password_hash	VARCHAR(255)	NOT NULL	Bcrypt hashed password
active	BOOLEAN	DEFAULT true	Account active status
created_at	TIMESTAMP	DEFAULT NOW()	Account creation time
updated_at	TIMESTAMP	DEFAULT NOW()	Last update time

Indexes:

```
idx_users_email
```
on (email)
```
idx_users_username
```
on (username)

Foreign Keys:

None

列名	类型	约束	描述
id	INTEGER	PRIMARY KEY, AUTO_INCREMENT	唯一用户标识符
username	VARCHAR(50)	UNIQUE, NOT NULL	用户登录名
email	VARCHAR(255)	UNIQUE, NOT NULL	用户邮箱地址
password_hash	VARCHAR(255)	NOT NULL	Bcrypt哈希密码
active	BOOLEAN	DEFAULT true	账户激活状态
created_at	TIMESTAMP	DEFAULT NOW()	账户创建时间
updated_at	TIMESTAMP	DEFAULT NOW()	最后更新时间

索引:

```
idx_users_email
```
on (email)
```
idx_users_username
```
on (username)

外键:

Orders Table

订单表

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique order identifier
user_id	INTEGER	FOREIGN KEY (users.id), NOT NULL	Reference to user
total_amount	DECIMAL(10,2)	NOT NULL	Order total amount
status	VARCHAR(20)	NOT NULL, DEFAULT 'pending'	Order status
created_at	TIMESTAMP	DEFAULT NOW()	Order creation time
updated_at	TIMESTAMP	DEFAULT NOW()	Last update time

Indexes:

```
idx_orders_user_id
```
on (user_id)
```
idx_orders_status
```
on (status)
```
idx_orders_created_at
```
on (created_at)

Foreign Keys:

```
fk_orders_user_id
```
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Check Constraints:

```
chk_orders_total_amount
```
CHECK (total_amount >= 0)
```
chk_orders_status
```
CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'))

undefined

列名	类型	约束	描述
id	INTEGER	PRIMARY KEY, AUTO_INCREMENT	唯一订单标识符
user_id	INTEGER	FOREIGN KEY (users.id), NOT NULL	关联用户
total_amount	DECIMAL(10,2)	NOT NULL	订单总金额
status	VARCHAR(20)	NOT NULL, DEFAULT 'pending'	订单状态
created_at	TIMESTAMP	DEFAULT NOW()	订单创建时间
updated_at	TIMESTAMP	DEFAULT NOW()	最后更新时间

索引:

```
idx_orders_user_id
```
on (user_id)
```
idx_orders_status
```
on (status)
```
idx_orders_created_at
```
on (created_at)

外键:

```
fk_orders_user_id
```
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

检查约束:

```
chk_orders_total_amount
```
CHECK (total_amount >= 0)
```
chk_orders_status
```
CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'))

undefined

JSON Schema Format

JSON Schema格式

json

{
  "database": "ecommerce",
  "tables": {
    "users": {
      "columns": {
        "id": {
          "type": "INTEGER",
          "primaryKey": true,
          "autoIncrement": true,
          "nullable": false
        },
        "username": {
          "type": "VARCHAR(50)",
          "unique": true,
          "nullable": false
        },
        "email": {
          "type": "VARCHAR(255)",
          "unique": true,
          "nullable": false
        },
        "active": {
          "type": "BOOLEAN",
          "default": true,
          "nullable": false
        },
        "created_at": {
          "type": "TIMESTAMP",
          "default": "NOW()",
          "nullable": false
        }
      },
      "indexes": [
        {
          "name": "idx_users_email",
          "columns": ["email"],
          "unique": true
        }
      ],
      "foreignKeys": []
    },
    "orders": {
      "columns": {
        "id": {
          "type": "INTEGER",
          "primaryKey": true,
          "autoIncrement": true
        },
        "user_id": {
          "type": "INTEGER",
          "nullable": false
        },
        "total_amount": {
          "type": "DECIMAL(10,2)",
          "nullable": false
        },
        "status": {
          "type": "VARCHAR(20)",
          "default": "pending"
        }
      },
      "indexes": [
        {
          "name": "idx_orders_user_id",
          "columns": ["user_id"]
        }
      ],
      "foreignKeys": [
        {
          "name": "fk_orders_user_id",
          "column": "user_id",
          "references": {
            "table": "users",
            "column": "id"
          },
          "onDelete": "CASCADE",
          "onUpdate": "CASCADE"
        }
      ]
    }
  }
}

json

{
  "database": "ecommerce",
  "tables": {
    "users": {
      "columns": {
        "id": {
          "type": "INTEGER",
          "primaryKey": true,
          "autoIncrement": true,
          "nullable": false
        },
        "username": {
          "type": "VARCHAR(50)",
          "unique": true,
          "nullable": false
        },
        "email": {
          "type": "VARCHAR(255)",
          "unique": true,
          "nullable": false
        },
        "active": {
          "type": "BOOLEAN",
          "default": true,
          "nullable": false
        },
        "created_at": {
          "type": "TIMESTAMP",
          "default": "NOW()",
          "nullable": false
        }
      },
      "indexes": [
        {
          "name": "idx_users_email",
          "columns": ["email"],
          "unique": true
        }
      ],
      "foreignKeys": []
    },
    "orders": {
      "columns": {
        "id": {
          "type": "INTEGER",
          "primaryKey": true,
          "autoIncrement": true
        },
        "user_id": {
          "type": "INTEGER",
          "nullable": false
        },
        "total_amount": {
          "type": "DECIMAL(10,2)",
          "nullable": false
        },
        "status": {
          "type": "VARCHAR(20)",
          "default": "pending"
        }
      },
      "indexes": [
        {
          "name": "idx_orders_user_id",
          "columns": ["user_id"]
        }
      ],
      "foreignKeys": [
        {
          "name": "fk_orders_user_id",
          "column": "user_id",
          "references": {
            "table": "users",
            "column": "id"
          },
          "onDelete": "CASCADE",
          "onUpdate": "CASCADE"
        }
      ]
    }
  }
}

Extracting Schema from ORM Models

从ORM模型中提取架构

From Prisma Schema

从Prisma Schema提取

prisma

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  username  String   @unique
  active    Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  orders    Order[]
  reviews   Review[]

  @@index([email])
  @@map("users")
}

model Order {
  id          Int      @id @default(autoincrement())
  userId      Int
  totalAmount Decimal  @db.Decimal(10, 2)
  status      String   @default("pending")
  createdAt   DateTime @default(now())

  user  User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  items OrderItem[]

  @@index([userId])
  @@index([status])
  @@map("orders")
}

Generated Visualization:

mermaid

erDiagram
    USERS ||--o{ ORDERS : "has many"
    USERS ||--o{ REVIEWS : "has many"

    USERS {
        int id PK
        string email UK
        string username UK
        boolean active
        datetime created_at
        datetime updated_at
    }

    ORDERS {
        int id PK
        int user_id FK
        decimal total_amount
        string status
        datetime created_at
    }

prisma

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  username  String   @unique
  active    Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  orders    Order[]
  reviews   Review[]

  @@index([email])
  @@map("users")
}

model Order {
  id          Int      @id @default(autoincrement())
  userId      Int
  totalAmount Decimal  @db.Decimal(10, 2)
  status      String   @default("pending")
  createdAt   DateTime @default(now())

  user  User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  items OrderItem[]

  @@index([userId])
  @@index([status])
  @@map("orders")
}

生成的可视化内容:

mermaid

erDiagram
    USERS ||--o{ ORDERS : "has many"
    USERS ||--o{ REVIEWS : "has many"

    USERS {
        int id PK
        string email UK
        string username UK
        boolean active
        datetime created_at
        datetime updated_at
    }

    ORDERS {
        int id PK
        int user_id FK
        decimal total_amount
        string status
        datetime created_at
    }

From TypeORM Entities

从TypeORM实体提取

typescript

// user.entity.ts
@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @Column({ unique: true })
  username: string;

  @Column({ default: true })
  active: boolean;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  @OneToMany(() => Order, order => order.user)
  orders: Order[];

  @Index()
  @Column()
  organizationId: number;
}

// order.entity.ts
@Entity('orders')
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column('decimal', { precision: 10, scale: 2 })
  totalAmount: number;

  @Column({ default: 'pending' })
  status: string;

  @ManyToOne(() => User, user => user.orders, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'userId' })
  user: User;

  @OneToMany(() => OrderItem, item => item.order)
  items: OrderItem[];
}

typescript

// user.entity.ts
@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @Column({ unique: true })
  username: string;

  @Column({ default: true })
  active: boolean;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  @OneToMany(() => Order, order => order.user)
  orders: Order[];

  @Index()
  @Column()
  organizationId: number;
}

// order.entity.ts
@Entity('orders')
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column('decimal', { precision: 10, scale: 2 })
  totalAmount: number;

  @Column({ default: 'pending' })
  status: string;

  @ManyToOne(() => User, user => user.orders, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'userId' })
  user: User;

  @OneToMany(() => OrderItem, item => item.order)
  items: OrderItem[];
}

From SQLAlchemy Models

从SQLAlchemy模型提取

python

undefined

python

undefined

models.py

from sqlalchemy import Column, Integer, String, Boolean, DECIMAL, DateTime, ForeignKey from sqlalchemy.orm import relationship from datetime import datetime

class User(Base): tablename = 'users'

id = Column(Integer, primary_key=True, autoincrement=True)
email = Column(String(255), unique=True, nullable=False, index=True)
username = Column(String(50), unique=True, nullable=False)
active = Column(Boolean, default=True)
created_at = Column(DateTime, default=datetime.utcnow)
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

# Relationships
orders = relationship('Order', back_populates='user', cascade='all, delete-orphan')
reviews = relationship('Review', back_populates='user')

class Order(Base): tablename = 'orders'

id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False, index=True)
total_amount = Column(DECIMAL(10, 2), nullable=False)
status = Column(String(20), default='pending', index=True)
created_at = Column(DateTime, default=datetime.utcnow)

# Relationships
user = relationship('User', back_populates='orders')
items = relationship('OrderItem', back_populates='order')

undefined

from sqlalchemy import Column, Integer, String, Boolean, DECIMAL, DateTime, ForeignKey from sqlalchemy.orm import relationship from datetime import datetime

class User(Base): tablename = 'users'

id = Column(Integer, primary_key=True, autoincrement=True)
email = Column(String(255), unique=True, nullable=False, index=True)
username = Column(String(50), unique=True, nullable=False)
active = Column(Boolean, default=True)
created_at = Column(DateTime, default=datetime.utcnow)
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

# Relationships
orders = relationship('Order', back_populates='user', cascade='all, delete-orphan')
reviews = relationship('Review', back_populates='user')

class Order(Base): tablename = 'orders'

id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False, index=True)
total_amount = Column(DECIMAL(10, 2), nullable=False)
status = Column(String(20), default='pending', index=True)
created_at = Column(DateTime, default=datetime.utcnow)

# Relationships
user = relationship('User', back_populates='orders')
items = relationship('OrderItem', back_populates='order')

undefined

Schema Analysis Features

架构分析功能

Relationship Cardinality Detection

关系基数检测

markdown

undefined

markdown

undefined

Relationship Analysis

关系分析

One-to-Many Relationships

一对多关系

Users → Orders (One user can have many orders)
Products → OrderItems (One product can be in many orders)
Categories → Products (One category can have many products)

用户 → 订单（一个用户可以拥有多个订单）
产品 → 订单项（一个产品可以出现在多个订单中）
分类 → 产品（一个分类可以包含多个产品）

Many-to-Many Relationships

多对多关系

Posts ↔ Tags (Through post_tags junction table)
Users ↔ Roles (Through user_roles junction table)

文章 ↔ 标签（通过post_tags关联表）
用户 ↔ 角色（通过user_roles关联表）

One-to-One Relationships

一对一关系

Users → UserProfiles (One user has one profile)

undefined

用户 → 用户资料（一个用户对应一个资料）

undefined

Missing Indexes Detection

缺失索引检测

markdown

undefined

markdown

undefined

Schema Health Report

架构健康报告

Missing Indexes

缺失索引

⚠️ High Priority:

```
orders.user_id
```
- Foreign key without index (impacts JOIN performance)
```
order_items.product_id
```
- Foreign key without index

⚠️ Medium Priority:

```
users.email
```
- Frequently used in WHERE clauses
```
products.category_id
```
- Used in JOIN operations

⚠️ 高优先级:

```
orders.user_id
```
- 无索引的外键（影响JOIN性能）
```
order_items.product_id
```
- 无索引的外键

⚠️ 中优先级:

```
users.email
```
- 经常用于WHERE子句
```
products.category_id
```
- 用于JOIN操作

Suggested Index Additions:

建议添加的索引:

sql

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_products_category_id ON products(category_id);

-- Composite index for common query pattern
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

sql

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_products_category_id ON products(category_id);

-- 针对常见查询模式的复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Normalization Analysis

规范化分析

markdown

undefined

markdown

undefined

Database Normalization Report

数据库规范化报告

Current Normalization Level: 3NF

当前规范化级别: 3NF

First Normal Form (1NF) ✓

第一范式（1NF） ✓

All tables have primary keys
No repeating groups
Atomic values in all columns

所有表都有主键
无重复组
所有列的值都是原子的

Second Normal Form (2NF) ✓

第二范式（2NF） ✓

All tables in 1NF
No partial dependencies on composite keys

所有表都符合1NF
复合主键无部分依赖

Third Normal Form (3NF) ✓

第三范式（3NF） ✓

All tables in 2NF
No transitive dependencies

所有表都符合2NF
无传递依赖

Potential Improvements:

潜在改进:

Denormalization Opportunities (for performance):

Add
```
user_name
```
to
```
orders
```
table to avoid JOIN for display
Cache
```
order_count
```
in
```
users
```
table
Store
```
product_name
```
in
```
order_items
```
for historical accuracy

Further Normalization Suggestions:

Extract address fields from
```
users
```
to separate
```
addresses
```
table
Split
```
products.description
```
to separate
```
product_details
```
table if frequently unused

undefined

反规范化优化（提升性能）:

在
```
orders
```
表中添加
```
user_name
```
字段，避免显示时JOIN
在
```
users
```
表中缓存
```
order_count
```
在
```
order_items
```
表中存储
```
product_name
```
以保证历史准确性

进一步规范化建议:

将
```
users
```
表中的地址字段提取到单独的
```
addresses
```
表
如果
```
products.description
```
不经常使用，将其拆分到单独的
```
product_details
```
表

undefined

dbdiagram.io Format

dbdiagram.io格式

dbml

// Use dbdiagram.io to visualize this schema

Table users {
  id int [pk, increment]
  username varchar(50) [unique, not null]
  email varchar(255) [unique, not null]
  password_hash varchar(255) [not null]
  active boolean [default: true]
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]

  Indexes {
    email [unique]
    username [unique]
  }
}

Table orders {
  id int [pk, increment]
  user_id int [not null, ref: > users.id]
  total_amount decimal(10,2) [not null]
  status varchar(20) [default: 'pending']
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]

  Indexes {
    user_id
    status
    created_at
  }
}

Table products {
  id int [pk, increment]
  name varchar(255) [not null]
  description text
  price decimal(10,2) [not null]
  stock_quantity int [default: 0]
  category_id int [ref: > categories.id]
  created_at timestamp [default: `now()`]

  Indexes {
    category_id
    (name, category_id) [name: 'idx_product_category']
  }
}

Table order_items {
  id int [pk, increment]
  order_id int [not null, ref: > orders.id]
  product_id int [not null, ref: > products.id]
  quantity int [not null]
  price decimal(10,2) [not null]

  Indexes {
    order_id
    product_id
  }
}

Table categories {
  id int [pk, increment]
  name varchar(100) [unique, not null]
  parent_id int [ref: > categories.id]
  created_at timestamp [default: `now()`]
}

Table reviews {
  id int [pk, increment]
  user_id int [not null, ref: > users.id]
  product_id int [not null, ref: > products.id]
  rating int [not null, note: '1-5']
  comment text
  created_at timestamp [default: `now()`]

  Indexes {
    (user_id, product_id) [unique]
    product_id
  }
}

dbml

// 使用dbdiagram.io可视化此架构

Table users {
  id int [pk, increment]
  username varchar(50) [unique, not null]
  email varchar(255) [unique, not null]
  password_hash varchar(255) [not null]
  active boolean [default: true]
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]

  Indexes {
    email [unique]
    username [unique]
  }
}

Table orders {
  id int [pk, increment]
  user_id int [not null, ref: > users.id]
  total_amount decimal(10,2) [not null]
  status varchar(20) [default: 'pending']
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]

  Indexes {
    user_id
    status
    created_at
  }
}

Table products {
  id int [pk, increment]
  name varchar(255) [not null]
  description text
  price decimal(10,2) [not null]
  stock_quantity int [default: 0]
  category_id int [ref: > categories.id]
  created_at timestamp [default: `now()`]

  Indexes {
    category_id
    (name, category_id) [name: 'idx_product_category']
  }
}

Table order_items {
  id int [pk, increment]
  order_id int [not null, ref: > orders.id]
  product_id int [not null, ref: > products.id]
  quantity int [not null]
  price decimal(10,2) [not null]

  Indexes {
    order_id
    product_id
  }
}

Table categories {
  id int [pk, increment]
  name varchar(100) [unique, not null]
  parent_id int [ref: > categories.id]
  created_at timestamp [default: `now()`]
}

Table reviews {
  id int [pk, increment]
  user_id int [not null, ref: > users.id]
  product_id int [not null, ref: > products.id]
  rating int [not null, note: '1-5']
  comment text
  created_at timestamp [default: `now()`]

  Indexes {
    (user_id, product_id) [unique]
    product_id
  }
}

PlantUML Format

PlantUML格式

plantuml

@startuml

entity "users" as users {
  *id : int <<PK>>
  --
  *username : varchar(50) <<UK>>
  *email : varchar(255) <<UK>>
  *password_hash : varchar(255)
  active : boolean
  created_at : timestamp
  updated_at : timestamp
}

entity "orders" as orders {
  *id : int <<PK>>
  --
  *user_id : int <<FK>>
  *total_amount : decimal(10,2)
  status : varchar(20)
  created_at : timestamp
  updated_at : timestamp
}

entity "products" as products {
  *id : int <<PK>>
  --
  *name : varchar(255)
  description : text
  *price : decimal(10,2)
  stock_quantity : int
  category_id : int <<FK>>
  created_at : timestamp
}

entity "order_items" as order_items {
  *id : int <<PK>>
  --
  *order_id : int <<FK>>
  *product_id : int <<FK>>
  *quantity : int
  *price : decimal(10,2)
}

entity "categories" as categories {
  *id : int <<PK>>
  --
  *name : varchar(100)
  parent_id : int <<FK>>
  created_at : timestamp
}

users ||--o{ orders
orders ||--|{ order_items
products ||--o{ order_items
categories ||--o{ products
categories ||--o{ categories : "parent/child"

@enduml

plantuml

@startuml

entity "users" as users {
  *id : int <<PK>>
  --
  *username : varchar(50) <<UK>>
  *email : varchar(255) <<UK>>
  *password_hash : varchar(255)
  active : boolean
  created_at : timestamp
  updated_at : timestamp
}

entity "orders" as orders {
  *id : int <<PK>>
  --
  *user_id : int <<FK>>
  *total_amount : decimal(10,2)
  status : varchar(20)
  created_at : timestamp
  updated_at : timestamp
}

entity "products" as products {
  *id : int <<PK>>
  --
  *name : varchar(255)
  description : text
  *price : decimal(10,2)
  stock_quantity : int
  category_id : int <<FK>>
  created_at : timestamp
}

entity "order_items" as order_items {
  *id : int <<PK>>
  --
  *order_id : int <<FK>>
  *product_id : int <<FK>>
  *quantity : int
  *price : decimal(10,2)
}

entity "categories" as categories {
  *id : int <<PK>>
  --
  *name : varchar(100)
  parent_id : int <<FK>>
  created_at : timestamp
}

users ||--o{ orders
orders ||--|{ order_items
products ||--o{ order_items
categories ||--o{ products
categories ||--o{ categories : "parent/child"

@enduml

Schema Comparison

架构对比

markdown

undefined

markdown

undefined

Schema Comparison: Production vs Staging

架构对比：生产环境 vs 测试环境

New Tables in Staging:

测试环境新增表:

```
notifications
```
- User notification system
```
audit_logs
```
- Activity tracking

```
notifications
```
- 用户通知系统
```
audit_logs
```
- 活动追踪

Modified Tables:

修改的表:

users

users表

Added columns:

```
last_login_at
```
(timestamp)
```
email_verified
```
(boolean)

Removed columns:

```
legacy_id
```
(deprecated)

新增列:

```
last_login_at
```
(timestamp)
```
email_verified
```
(boolean)

删除列:

```
legacy_id
```
(已废弃)

orders

orders表

Modified columns:

```
total_amount
```
: DECIMAL(8,2) → DECIMAL(10,2) (increased precision)

Added indexes:

```
idx_orders_created_at
```
on (created_at)

修改列:

```
total_amount
```
: DECIMAL(8,2) → DECIMAL(10,2)（提高精度）

新增索引:

```
idx_orders_created_at
```
on (created_at)

Migration Script:

迁移脚本:

sql

-- Add new columns
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT false;
ALTER TABLE users DROP COLUMN legacy_id;

-- Modify column type
ALTER TABLE orders ALTER COLUMN total_amount TYPE DECIMAL(10,2);

-- Add new index
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Create new tables
CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  type VARCHAR(50) NOT NULL,
  message TEXT NOT NULL,
  read BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW()
);

sql

-- 添加新列
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT false;
ALTER TABLE users DROP COLUMN legacy_id;

-- 修改列类型
ALTER TABLE orders ALTER COLUMN total_amount TYPE DECIMAL(10,2);

-- 添加新索引
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 创建新表
CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  type VARCHAR(50) NOT NULL,
  message TEXT NOT NULL,
  read BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW()
);

Best Practices

最佳实践

Always visualize before making changes - Understand impact
Document relationship cardinality - One-to-many, many-to-many
Include indexes in diagrams - Performance consideration
Show foreign key constraints - Data integrity
Use consistent naming conventions - Improve readability
Version control schema changes - Track evolution
Generate diagrams from code - Keep in sync
Include constraints and checks - Business rules
Document enum values - Valid states
Keep diagrams up to date - Living documentation

修改前务必可视化 - 了解影响范围
记录关系基数 - 一对多、多对多
在图表中包含索引 - 考虑性能因素
展示外键约束 - 保证数据完整性
使用一致的命名规范 - 提升可读性
版本控制架构变更 - 追踪演化过程
从代码生成图表 - 保持同步
包含约束和检查规则 - 体现业务规则
记录枚举值 - 有效状态
保持图表更新 - 维护活文档

Tools Integration

工具集成

Generate from Database

从数据库生成

bash

undefined

bash

undefined

PostgreSQL - using pg_dump

PostgreSQL - 使用pg_dump

pg_dump -s -d mydb > schema.sql

MySQL - schema only

MySQL - 仅导出架构

mysqldump --no-data mydb > schema.sql

Using SchemaSpy (generates HTML visualization)

使用SchemaSpy（生成HTML可视化内容）

java -jar schemaspy.jar -t pgsql -db mydb -u user -p password -o output

Using DBeaver (export ERD)

使用DBeaver（导出ERD）

File → Export → Database Structure → ERD

文件 → 导出 → 数据库结构 → ERD

undefined

undefined

Generate from ORM

从ORM生成

bash

undefined

bash

undefined

Prisma - generate ERD

Prisma - 生成ERD

npx prisma generate npx prisma studio

TypeORM - generate migration

TypeORM - 生成迁移文件

npx typeorm migration:generate -n InitialSchema

Django - generate ERD

Django - 生成ERD

python manage.py graph_models -a -o erd.png

Rails - generate ERD

Rails - 生成ERD

bundle exec rails erd

undefined

bundle exec rails erd

undefined

Notes

注意事项

Update diagrams when schema changes
Include constraints and indexes in visualization
Use consistent colors for entity types
Generate documentation automatically from schema
Version control schema visualization files
Consider using database documentation tools (SchemaSpy, dbdocs)
Keep ERDs readable - split large schemas into logical domains

架构变更时更新图表
在可视化内容中包含约束和索引
为实体类型使用一致的颜色
从架构自动生成文档
版本控制架构可视化文件
考虑使用数据库文档工具（SchemaSpy、dbdocs）
保持ERD可读性 - 将大型架构拆分为逻辑域