数据库设计规范

核心原则

数据库设计必须遵循第三范式（3NF），使用合理的索引策略，确保数据完整性和查询性能。

允许的做法

主键设计

• 使用 BIGINT 类型自增主键
• 使用 id 作为主键名称
• 避免 使用复合主键（除关联表）

-- ✅ 正确
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

外键字段设计

• 使用 {table}_id 命名外键字段
• 使用 BIGINT 类型
• 不使用 数据库外键约束（在应用层保证引用完整性）
• 使用 索引优化关联查询

-- ✅ 正确
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户 ID',
    product_id BIGINT NOT NULL COMMENT '商品 ID',
    created_at BIGINT NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_product_id (product_id)
);

时间字段

• 使用 BIGINT 存储 Unix 时间戳（秒）
• 使用 created_at、updated_at、deleted_at 标准命名
• 使用 deleted_at 实现软删除

-- ✅ 正确
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at BIGINT NOT NULL,
    updated_at BIGINT NOT NULL,
    deleted_at BIGINT DEFAULT NULL,
    INDEX idx_deleted_at (deleted_at)
);

字段类型选择

数据类型	MySQL 类型	说明
整数	BIGINT	主键、外键、时间戳
短整数	INT	计数、状态码
小数	DECIMAL(10,2)	金额、价格
短文本	VARCHAR(N)	名称、邮箱
长文本	TEXT	描述、内容
布尔值	TINYINT(1)	是/否
枚举	VARCHAR(50)	状态值
JSON	JSON	结构化数据

-- ✅ 正确示例
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    age INT,
    balance DECIMAL(10,2) DEFAULT 0.00,
    bio TEXT,
    is_active TINYINT(1) DEFAULT 1,
    status VARCHAR(50) DEFAULT 'active',
    settings JSON,
    created_at BIGINT NOT NULL,
    updated_at BIGINT NOT NULL,
    deleted_at BIGINT DEFAULT NULL
);

索引设计

• 使用 单列索引用于高频查询字段
• 使用 复合索引优化多条件查询
• 使用 UNIQUE 索引保证唯一性
• 必须 为外键字段添加索引

-- ✅ 正确
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at BIGINT NOT NULL,
    deleted_at BIGINT DEFAULT NULL,

    UNIQUE INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_deleted_at (deleted_at),
    INDEX idx_status_created (status, created_at)  -- 复合索引
);

关联表设计

• 使用 独立的关联表表示多对多关系
• 使用 两个外键字段作为复合主键
• 不使用 外键约束

-- ✅ 正确 - 用户和角色的多对多关系
CREATE TABLE user_roles (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    created_at BIGINT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    INDEX idx_user_id (user_id),
    INDEX idx_role_id (role_id)
);

禁止的做法

禁止的设计

• 禁止 使用数据库外键约束（FOREIGN KEY）
• 禁止 使用 UUID 字符串作为主键（性能问题）
• 禁止 使用 DATETIME 类型（使用 BIGINT 时间戳）
• 禁止 字段名使用保留字
• 避免 过多的 NULL 字段
• 避免 单表字段超过 30 个

-- ❌ 错误
CREATE TABLE users (
    uuid VARCHAR(36) PRIMARY KEY,          -- 不使用 UUID 字符串
    create_time DATETIME,                  -- 不使用 DATETIME
    order VARCHAR(100),                    -- 不使用保留字
    field1 VARCHAR(100),                   -- 不使用无意义命名
    field2 VARCHAR(100)
);

-- ❌ 错误 - 不使用外键约束
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)  -- 禁止
);

数据库设计示例

用户表

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '用户 ID',
    name VARCHAR(100) NOT NULL COMMENT '用户名',
    email VARCHAR(255) NOT NULL COMMENT '邮箱',
    password VARCHAR(255) NOT NULL COMMENT '密码（加密）',
    phone VARCHAR(20) COMMENT '手机号',
    avatar_url VARCHAR(500) COMMENT '头像 URL',
    status VARCHAR(50) NOT NULL DEFAULT 'active' COMMENT '状态：active, inactive',
    created_at BIGINT NOT NULL COMMENT '创建时间',
    updated_at BIGINT NOT NULL COMMENT '更新时间',
    deleted_at BIGINT DEFAULT NULL COMMENT '删除时间（软删除）',

    UNIQUE INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

商品表

CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '商品 ID',
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    description TEXT COMMENT '商品描述',
    price DECIMAL(10,2) NOT NULL COMMENT '价格',
    stock INT NOT NULL DEFAULT 0 COMMENT '库存',
    category_id BIGINT NOT NULL COMMENT '分类 ID',
    status VARCHAR(50) NOT NULL DEFAULT 'active' COMMENT '状态：active, inactive',
    created_at BIGINT NOT NULL COMMENT '创建时间',
    updated_at BIGINT NOT NULL COMMENT '更新时间',
    deleted_at BIGINT DEFAULT NULL COMMENT '删除时间',

    INDEX idx_category_id (category_id),
    INDEX idx_status (status),
    INDEX idx_price (price),
    INDEX idx_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

订单表

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '订单 ID',
    order_no VARCHAR(50) NOT NULL COMMENT '订单号',
    user_id BIGINT NOT NULL COMMENT '用户 ID',
    total_amount DECIMAL(10,2) NOT NULL COMMENT '总金额',
    status VARCHAR(50) NOT NULL DEFAULT 'pending' COMMENT '状态：pending, paid, completed, cancelled',
    paid_at BIGINT DEFAULT NULL COMMENT '支付时间',
    completed_at BIGINT DEFAULT NULL COMMENT '完成时间',
    created_at BIGINT NOT NULL COMMENT '创建时间',
    updated_at BIGINT NOT NULL COMMENT '更新时间',
    deleted_at BIGINT DEFAULT NULL COMMENT '删除时间',

    UNIQUE INDEX idx_order_no (order_no),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

订单明细表

CREATE TABLE order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '明细 ID',
    order_id BIGINT NOT NULL COMMENT '订单 ID',
    product_id BIGINT NOT NULL COMMENT '商品 ID',
    product_name VARCHAR(200) NOT NULL COMMENT '商品名称（冗余）',
    price DECIMAL(10,2) NOT NULL COMMENT '单价（冗余）',
    quantity INT NOT NULL COMMENT '数量',
    subtotal DECIMAL(10,2) NOT NULL COMMENT '小计',
    created_at BIGINT NOT NULL COMMENT '创建时间',

    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

特殊场景

引用完整性保证

由于不使用数据库外键约束，必须在应用层保证引用完整性：

// ✅ 正确 - 在应用层检查引用完整性
func (s *OrderService) CreateOrder(ctx context.Context, req *CreateOrderRequest) (*Order, error) {
    // 1. 检查用户是否存在
    user, err := s.userRepo.FindByID(ctx, req.UserID)
    if err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, errors.New("用户不存在")
        }
        return nil, fmt.Errorf("查询用户失败: %w", err)
    }

    // 2. 检查商品是否存在
    product, err := s.productRepo.FindByID(ctx, req.ProductID)
    if err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, errors.New("商品不存在")
        }
        return nil, fmt.Errorf("查询商品失败: %w", err)
    }

    // 3. 使用事务创建订单
    var order *Order
    err = s.db.Transaction(func(tx *gorm.DB) error {
        order = &Order{
            UserID:      req.UserID,
            ProductID:   req.ProductID,
            TotalAmount: product.Price,
            Status:      "pending",
            CreatedAt:   time.Now().Unix(),
            UpdatedAt:   time.Now().Unix(),
        }
        if err := tx.Create(order).Error; err != nil {
            return fmt.Errorf("创建订单失败: %w", err)
        }
        return nil
    })

    return order, err
}

关键点：

• 使用 errors.Is(err, gorm.ErrRecordNotFound) 判断记录不存在
• 使用 fmt.Errorf("...: %w", err) 包装错误，保留原始错误链
• 使用事务保证数据一致性
• 返回明确的错误信息

枚举值存储

• 使用 VARCHAR 存储状态值
• 使用 CHECK 约束（MySQL 8.0.16+）或应用层验证

-- ✅ 正确
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    CHECK (status IN ('pending', 'paid', 'completed', 'cancelled'))
);

JSON 字段

• 使用 JSON 类型存储灵活的结构化数据
• 使用 虚拟列和索引支持 JSON 查询

-- ✅ 正确
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    settings JSON COMMENT '用户设置',
    email_virtual VARCHAR(255) GENERATED ALWAYS AS (settings->>'$.email') STORED,
    INDEX idx_email_virtual (email_virtual)
);

数据冗余

在以下情况允许适度冗余：

• 减少 JOIN 查询
• 历史数据快照
• 提高查询性能

-- ✅ 正确 - 订单明细冗余商品信息
CREATE TABLE order_items (
    product_id BIGINT NOT NULL,
    product_name VARCHAR(200) NOT NULL,  -- 冗余：记录下单时的商品名
    price DECIMAL(10,2) NOT NULL         -- 冗余：记录下单时的价格
);

性能优化原则

索引优化

• 必须 为所有外键字段添加索引
• 使用 复合索引覆盖常用查询条件
• 避免 过多索引（影响写入性能）

-- ✅ 正确 - 为外键字段添加索引
CREATE TABLE orders (
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at BIGINT NOT NULL,

    INDEX idx_user_id (user_id),          -- 外键索引
    INDEX idx_product_id (product_id),    -- 外键索引
    INDEX idx_status_created (status, created_at)  -- 复合索引
);

相关文档

• 命名规范 - 数据库命名规则
• GORM 使用 - ORM 规范
• 数据库迁移 - 迁移管理