admin/CompanyRegister
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
CompanyRegister/plans/database_design.md
ChuXun f98da73376 Initial commit
2026-01-28 23:56:33 +08:00

14 KiB
Raw Permalink Blame History

智能办公管理系统 - 数据库设计文档

数据库架构概述

系统采用多数据库设计，根据业务特点选择最适合的数据库类型：

  • PostgreSQL: 核心业务数据（用户、角色、权限、考勤、审批等）
  • MongoDB: 文档协作、聊天记录、文件元数据等非结构化数据
  • Redis: 缓存、会话、分布式锁、消息队列
  • Elasticsearch: 日志、搜索、分析

PostgreSQL数据库设计

1. 认证授权模块 (auth_service)

1.1 用户表 (sys_user)

CREATE TABLE sys_user (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(200) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    nickname VARCHAR(50),
    real_name VARCHAR(50),
    avatar VARCHAR(500),
    gender INTEGER DEFAULT 0,
    birthday TIMESTAMP,
    dept_id BIGINT,
    position VARCHAR(100),
    status INTEGER DEFAULT 1,
    last_login_time TIMESTAMP,
    last_login_ip VARCHAR(50),
    login_count INTEGER DEFAULT 0,
    failed_login_count INTEGER DEFAULT 0,
    lock_time TIMESTAMP,
    password_reset_time TIMESTAMP,
    is_super_admin BOOLEAN DEFAULT FALSE,
    remark VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_by BIGINT,
    update_by BIGINT,
    deleted BOOLEAN DEFAULT FALSE
);

CREATE INDEX idx_username ON sys_user(username);
CREATE INDEX idx_email ON sys_user(email);
CREATE INDEX idx_phone ON sys_user(phone);
CREATE INDEX idx_dept_id ON sys_user(dept_id);

1.2 角色表 (sys_role)

CREATE TABLE sys_role (
    id BIGSERIAL PRIMARY KEY,
    role_code VARCHAR(50) NOT NULL UNIQUE,
    role_name VARCHAR(50) NOT NULL,
    description VARCHAR(200),
    data_scope INTEGER DEFAULT 1,
    status INTEGER DEFAULT 1,
    sort INTEGER DEFAULT 0,
    is_system BOOLEAN DEFAULT FALSE,
    remark VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_by BIGINT,
    update_by BIGINT,
    deleted BOOLEAN DEFAULT FALSE
);

CREATE INDEX idx_role_code ON sys_role(role_code);
CREATE INDEX idx_role_name ON sys_role(role_name);

1.3 权限表 (sys_permission)

CREATE TABLE sys_permission (
    id BIGSERIAL PRIMARY KEY,
    permission_code VARCHAR(100) NOT NULL UNIQUE,
    permission_name VARCHAR(50) NOT NULL,
    permission_type INTEGER NOT NULL,
    parent_id BIGINT DEFAULT 0,
    path VARCHAR(200),
    component VARCHAR(200),
    icon VARCHAR(100),
    sort INTEGER DEFAULT 0,
    status INTEGER DEFAULT 1,
    is_visible BOOLEAN DEFAULT TRUE,
    is_cache BOOLEAN DEFAULT FALSE,
    is_external BOOLEAN DEFAULT FALSE,
    external_url VARCHAR(500),
    request_method VARCHAR(20),
    api_path VARCHAR(500),
    remark VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_by BIGINT,
    update_by BIGINT,
    deleted BOOLEAN DEFAULT FALSE
);

CREATE INDEX idx_permission_code ON sys_permission(permission_code);
CREATE INDEX idx_parent_id ON sys_permission(parent_id);
CREATE INDEX idx_sort ON sys_permission(sort);

1.4 关联表

-- 用户角色关联表
CREATE TABLE sys_user_role (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, role_id)
);

-- 角色权限关联表
CREATE TABLE sys_role_permission (
    id BIGSERIAL PRIMARY KEY,
    role_id BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(role_id, permission_id)
);

2. 考勤管理模块 (attendance_service)

2.1 考勤记录表 (attendance_record)

CREATE TABLE attendance_record (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    dept_id BIGINT,
    attendance_date DATE NOT NULL,
    check_in_time TIMESTAMP,
    check_out_time TIMESTAMP,
    work_hours DECIMAL(5,2),
    late_minutes INTEGER DEFAULT 0,
    early_minutes INTEGER DEFAULT 0,
    overtime_hours DECIMAL(5,2) DEFAULT 0,
    attendance_status INTEGER DEFAULT 0, -- 0:正常, 1:迟到, 2:早退, 3:缺勤, 4:请假, 5:加班
    location VARCHAR(200),
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    device_info VARCHAR(200),
    ip_address VARCHAR(50),
    remark VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_date ON attendance_record(user_id, attendance_date);
CREATE INDEX idx_dept_date ON attendance_record(dept_id, attendance_date);

2.2 请假申请表 (leave_request)

CREATE TABLE leave_request (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    leave_type INTEGER NOT NULL, -- 1:年假, 2:病假, 3:事假, 4:婚假, 5:产假, 6:丧假
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    duration_days DECIMAL(5,2),
    reason VARCHAR(500),
    attachment_url VARCHAR(500),
    status INTEGER DEFAULT 0, -- 0:待审批, 1:已通过, 2:已拒绝, 3:已取消
    approval_flow_id BIGINT,
    current_approver_id BIGINT,
    remark VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_status ON leave_request(user_id, status);
CREATE INDEX idx_approval_flow ON leave_request(approval_flow_id);

2.3 加班申请表 (overtime_request)

CREATE TABLE overtime_request (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    overtime_date DATE NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    duration_hours DECIMAL(5,2),
    reason VARCHAR(500),
    compensation_type INTEGER DEFAULT 1, -- 1:调休, 2:加班费
    status INTEGER DEFAULT 0,
    approval_flow_id BIGINT,
    current_approver_id BIGINT,
    remark VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. 审批流程模块 (approval_service)

3.1 工作流定义表 (workflow_definition)

CREATE TABLE workflow_definition (
    id BIGSERIAL PRIMARY KEY,
    workflow_code VARCHAR(50) NOT NULL UNIQUE,
    workflow_name VARCHAR(100) NOT NULL,
    workflow_type INTEGER NOT NULL, -- 1:请假, 2:加班, 3:报销, 4:采购, 5:通用
    description VARCHAR(500),
    version INTEGER DEFAULT 1,
    status INTEGER DEFAULT 1,
    form_schema JSONB,
    process_definition JSONB,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_by BIGINT,
    update_by BIGINT
);

3.2 工作流实例表 (workflow_instance)

CREATE TABLE workflow_instance (
    id BIGSERIAL PRIMARY KEY,
    workflow_definition_id BIGINT NOT NULL,
    business_key VARCHAR(100),
    business_type VARCHAR(50),
    business_data JSONB,
    initiator_id BIGINT NOT NULL,
    current_node_id BIGINT,
    current_assignee_id BIGINT,
    status INTEGER DEFAULT 0, -- 0:进行中, 1:已完成, 2:已终止, 3:已取消
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP,
    duration_days INTEGER,
    remark VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_business_key ON workflow_instance(business_key);
CREATE INDEX idx_initiator_status ON workflow_instance(initiator_id, status);

3.3 审批节点表 (approval_node)

CREATE TABLE approval_node (
    id BIGSERIAL PRIMARY KEY,
    workflow_instance_id BIGINT NOT NULL,
    node_code VARCHAR(50) NOT NULL,
    node_name VARCHAR(100) NOT NULL,
    node_type INTEGER NOT NULL, -- 1:开始, 2:审批, 3:会签, 4:或签, 5:结束
    assignee_type INTEGER NOT NULL, -- 1:指定人, 2:角色, 3:部门负责人, 4:发起人自选
    assignee_ids JSONB,
    status INTEGER DEFAULT 0, -- 0:待处理, 1:已通过, 2:已拒绝, 3:已跳过
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    duration_hours DECIMAL(5,2),
    comment VARCHAR(500),
    attachment_url VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4. 文档协作模块 (document_service)

4.1 文档表 (document) - MongoDB集合

{
  "_id": ObjectId,
  "document_id": String,
  "title": String,
  "content": String,
  "content_type": String, // "text", "markdown", "html"
  "owner_id": Number,
  "owner_name": String,
  "space_id": String,
  "parent_id": String,
  "tags": [String],
  "permissions": {
    "view": [Number], // 用户ID列表
    "edit": [Number],
    "comment": [Number],
    "share": [Number]
  },
  "version": Number,
  "current_version_id": String,
  "is_published": Boolean,
  "published_version": Number,
  "status": String, // "draft", "published", "archived"
  "metadata": {
    "word_count": Number,
    "page_count": Number,
    "language": String,
    "created_from": String // "web", "mobile", "import"
  },
  "statistics": {
    "view_count": Number,
    "edit_count": Number,
    "comment_count": Number,
    "share_count": Number
  },
  "created_at": Date,
  "updated_at": Date,
  "deleted_at": Date
}

4.2 文档版本表 (document_version) - MongoDB集合

{
  "_id": ObjectId,
  "version_id": String,
  "document_id": String,
  "version_number": Number,
  "title": String,
  "content": String,
  "content_hash": String,
  "change_summary": String,
  "author_id": Number,
  "author_name": String,
  "created_at": Date,
  "metadata": {
    "operation": String, // "create", "edit", "restore"
    "client_info": String,
    "ip_address": String
  }
}

5. 即时通讯模块 (chat_service)

5.1 聊天室表 (chat_room) - MongoDB集合

{
  "_id": ObjectId,
  "room_id": String,
  "room_name": String,
  "room_type": String, // "private", "group", "channel"
  "avatar": String,
  "description": String,
  "owner_id": Number,
  "admin_ids": [Number],
  "member_ids": [Number],
  "settings": {
    "mute_all": Boolean,
    "allow_invite": Boolean,
    "need_approval": Boolean,
    "max_members": Number
  },
  "last_message": {
    "message_id": String,
    "content": String,
    "sender_id": Number,
    "sender_name": String,
    "timestamp": Date
  },
  "unread_count": Map, // userId -> count
  "created_at": Date,
  "updated_at": Date
}

5.2 消息表 (message) - MongoDB集合

{
  "_id": ObjectId,
  "message_id": String,
  "room_id": String,
  "sender_id": Number,
  "sender_name": String,
  "sender_avatar": String,
  "content_type": String, // "text", "image", "file", "voice", "video"
  "content": String,
  "metadata": {
    "file_url": String,
    "file_name": String,
    "file_size": Number,
    "duration": Number,
    "width": Number,
    "height": Number
  },
  "reply_to": String, // 回复的消息ID
  "mentions": [Number], // 被@的用户ID
  "reactions": Map, // emoji -> [userId]
  "read_by": [Number], // 已读用户ID
  "deleted_for": [Number], // 对哪些用户不可见
  "is_system": Boolean,
  "is_edited": Boolean,
  "edited_at": Date,
  "is_recalled": Boolean,
  "recalled_at": Date,
  "created_at": Date,
  "updated_at": Date
}

6. 系统管理模块

6.1 部门表 (sys_department)

CREATE TABLE sys_department (
    id BIGSERIAL PRIMARY KEY,
    dept_code VARCHAR(50) NOT NULL UNIQUE,
    dept_name VARCHAR(100) NOT NULL,
    parent_id BIGINT DEFAULT 0,
    ancestors VARCHAR(500),
    leader_id BIGINT,
    sort INTEGER DEFAULT 0,
    status INTEGER DEFAULT 1,
    phone VARCHAR(20),
    email VARCHAR(100),
    address VARCHAR(200),
    remark VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_by BIGINT,
    update_by BIGINT,
    deleted BOOLEAN DEFAULT FALSE
);

CREATE INDEX idx_dept_code ON sys_department(dept_code);
CREATE INDEX idx_parent_id ON sys_department(parent_id);

6.2 操作日志表 (sys_operation_log)

CREATE TABLE sys_operation_log (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(50),
    real_name VARCHAR(50),
    operation_type VARCHAR(50),
    operation_module VARCHAR(100),
    operation_description VARCHAR(500),
    request_method VARCHAR(10),
    request_url VARCHAR(500),
    request_params TEXT,
    request_ip VARCHAR(50),
    request_location VARCHAR(100),
    user_agent VARCHAR(500),
    execute_time BIGINT,
    status INTEGER DEFAULT 1,
    error_message TEXT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_time ON sys_operation_log(user_id, create_time);
CREATE INDEX idx_module_time ON sys_operation_log(operation_module, create_time);

数据库优化策略

1. 索引优化

  • 为所有外键字段创建索引
  • 为频繁查询的组合字段创建复合索引
  • 为时间范围查询字段创建索引

2. 分区策略

  • 按时间分区：操作日志、考勤记录等时间序列数据
  • 按业务分区：不同租户的数据分离

3. 读写分离

  • 主库：写操作和实时读操作
  • 从库：报表查询、数据分析等非实时读操作

4. 缓存策略

  • Redis缓存热点数据：用户信息、权限信息、配置信息
  • 缓存查询结果：复杂查询结果缓存5-30分钟
  • 分布式会话：用户会话信息存储在Redis中

5. 数据归档

  • 历史数据归档到冷存储
  • 定期清理过期数据
  • 数据备份和恢复策略

数据库初始化脚本

系统启动时会自动执行以下初始化：

  1. 创建数据库和用户
  2. 创建表结构和索引
  3. 插入基础数据（系统角色、权限、管理员用户）
  4. 创建数据库函数和触发器

数据迁移策略

  1. 使用Flyway进行数据库版本管理
  2. 支持回滚操作
  3. 数据迁移前备份
  4. 灰度发布，逐步迁移

监控和告警

  1. 数据库连接池监控
  2. 慢查询监控和优化
  3. 死锁检测和解决
  4. 空间使用监控
  5. 备份状态监控

文档版本: v1.0 最后更新: 2026-01-27 作者: 数据库设计团队

Reference in New Issue View Git Blame Copy Permalink