工作流程数据表设计Mysql数据库

最新推荐文章于 2025-09-13 10:38:36 发布

原创

最新推荐文章于 2025-09-13 10:38:36 发布 · 2.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#redis #mysql #sql

本文探讨了如何在Mysql数据库中有效地设计工作流程数据表，涵盖了关键的设计原则和最佳实践，以确保数据的高效存储和查询。通过合理组织表结构，利用索引优化，以及考虑与其他技术如Redis的集成，实现高性能的工作流程管理。

/*
Navicat MySQL Data Transfer 流程模块数据表设计

Source Server         : local
Source Server Version : 50724
Source Host           : localhost:3306
Source Database       : process

Target Server Type    : MYSQL
Target Server Version : 50724
File Encoding         : 65001

Date: 2021-04-09 16:22:58
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for process_account
-- ----------------------------
DROP TABLE IF EXISTS `process_account`;
CREATE TABLE `process_account` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_name` varchar(128) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '银行卡户名',
  `account_num` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '银行卡卡号',
  `account_bank` varchar(64) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '开户行名称',
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '余额额',
  `remark` varchar(1024) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '备注',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建人：用户id',
  `editer_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '编辑人：用户id',
  `company_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '细胞id',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '1=删除禁用,0=正常',
  `is_platform` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0=个人银行卡,1=平台银行卡',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='流程管理：银行卡账号管理表';

-- ----------------------------
-- Table structure for process_account_log
-- ----------------------------
DROP TABLE IF EXISTS `process_account_log`;
CREATE TABLE `process_account_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_sn` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '交易流水',
  `operation_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '操作类型：1=收入,0=支出',
  `transaction_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '交易类型：1=付款,0=购买,2=充值,3=出售 等',
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '收支变动的金额',
  `balance` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '变动后的余额',
  `remark` varchar(1024) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '备注',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `payer_account_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付款银行卡id',
  `payee_account_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '对方银行卡id',
  `files_url` varchar(2048) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '付款收款-截图URL',
  `program_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '流程id',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '1=删除禁用,0=正常',
  `pay_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '付款时间',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `payer_account_id` (`payer_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='流程管理：收款账号收入支出流水表';

-- ----------------------------
-- Table structure for process_contract
-- ----------------------------
DROP TABLE IF EXISTS `process_contract`;
CREATE TABLE `process_contract` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `con