数据库设计最佳实践：我们团队沉淀下来的规范

数据库设计的规范化是保障产品质量的重要基础。结合qKnow、qData、qModel等产品的实践经验我们制定了本《数据库设计规范》。通过统一的数据库设计标准团队可在不同项目间保持一致的结构风格提高性能、降低维护成本并确保产品具备良好的扩展能力。一、模块设计1. 模块划分规范模块划分是数据库设计的基础环节应以业务逻辑清晰、结构合理、易于维护与扩展为原则确保系统在开发、迭代及后期运维中的可持续性。业务驱动模块划分应基于实际的业务需求确保每个模块对应一个明确的功能或业务流程避免人为拆分或随意堆叠。示例用户管理模块User Management Module负责用户注册、登录、角色分配等。项目管理模块Project Management Module负责项目信息维护、任务分配、进度跟踪。报表统计模块Report Module负责数据统计与可视化展示。高内聚低耦合各模块之间的耦合应尽量降低确保模块内部功能的高内聚性。比如用户管理模块um不应直接依赖项目管理模块pm的表结构而应通过接口或服务层交互。说明如果一个模块需要频繁调用另一个模块的接口说明划分可能存在问题应重新审视职责边界。分层设计模块划分可根据业务逻辑和功能层次进行设计通常包括基础层如数据库交互、通用工具类逻辑层如业务规则计算、状态机处理应用层如用户接口、API服务、前端对接逻辑。分层结构有助于系统解耦、测试隔离和分布式部署。均衡性模块划分应尽量均衡避免某些模块过大如包含上百张表影响后期的维护和扩展。建议单个模块所辖表数量不超过30张若超出应考虑进一步拆分。灵活性与扩展性模块设计应考虑到未来可能的扩展或变更避免过度耦合保持模块的灵活性和扩展性。例如用户模块未来可能需要支持多租户应在设计初期预留 tenant_id 字段或采用独立 schema 方案。2. 模块命名规范良好的模块命名是数据库可读性和可维护性的基础模块命名应遵循统一、简洁、有意义的原则确保团队成员一目了然。简洁且有意义模块名称应简洁且能清晰表达模块的功能避免过长或过于抽象的命名。示例用户管理模块 → user 或 um项目管理模块 → project 或 pm使用缩写由于模块名将作为表名的前缀命名时应简化为适当的缩写确保不超过合理长度建议2~4个字母。常见示例模块名称缩写示例表名用户管理模块umum_user项目管理模块pmpm_project系统配置模块scsc_config避免冗余命名中避免使用“系统”、“管理”等冗余词汇应侧重反映模块的核心功能。不推荐user_manage_system推荐um_user统一命名规则所有项目必须采用统一的命名规范避免因不同团队或开发人员命名方式不一致而影响维护性。建议在项目启动阶段由架构组统一发布模块缩写对照表。二、数据库命名规范1. 环境区分命名为保障开发、测试、生产环境的数据隔离与部署安全数据库命名必须严格区分环境开发库[项目代号]_dev示例qData_dev、qKnow_dev测试库[项目代号]_test示例qData_test、qModel_test生产库[项目代号]_prod示例qKnow_prod、qThing_prod⚠️ 注意严禁在生产环境中直接使用 _dev 或 _test 后缀的数据库。2. 项目代号命名项目代号应简短建议2~10个字符、唯一、具有业务识别性便于管理和区分不同项目的数据库。 示例qData千桐数据中台、qKnow千桐知识平台、qAuth统一身份认证平台3. 统一命名规则所有环境下的数据库命名格式必须统一确保运维人员能快速识别环境类型。建议在数据库创建脚本、CI/CD 配置文件中强制校验命名规范。三、表设计1. 表命名规范小写字母与下划线所有表名必须使用小写字母单词之间用下划线分隔。✅ 正确示例user_info、order_details、product_category❌ 错误示例UserInfo、ORDERDETAILS、user-info 若客户有特殊要求如 Oracle 环境偏好大写可使用 PDManer 等建模工具在导出 DDL 时统一转换大小写但逻辑设计阶段仍以小写为准。简洁且准确表名应简短且准确反映其存储的数据实体。示例um_user用户管理模块中的用户主表pm_project项目管理模块中的项目主表避免业务术语泛化禁止使用“数据表”、“记录表”、“信息表”等模糊词汇。应聚焦实体本身如 user 而非 user_data_table。避免特殊字符和数字表名不得包含空格、中文、-、#、 等特殊字符也不应以纯数字开头或结尾。前缀规则所有业务表必须以所属模块缩写作为前缀。示例um_user_role_rel用户-角色关系表om_order_history订单历史表sm_system_config系统配置表2. 特殊类型表命名标识为提升数据库可读性特定用途的表需使用统一后缀标识表类型命名后缀示例说明关系表_relum_user_role_rel多对多关联表日志表_logsm_operation_log操作日志、错误日志等历史记录表_historyom_order_history用于记录数据变更历史配置表_configsm_app_config存储系统或应用级配置参数 建议历史表和日志表应定期归档避免主库膨胀。四、字段设计1. 字段逻辑名命名规范技术命名简短且明确字段名应体现其业务含义如 user_id、order_amount避免 uid、amt 等过度缩写。小写字母与下划线字段名统一使用小写下划线风格。✅ 示例create_time、request_ip、del_flag避免保留字严禁使用 SQL 保留字作为字段名如 order、group、desc、user 等。若必须使用需加反引号不推荐。统一命名规则同一语义字段在不同表中应保持命名一致。示例所有表中的“创建人”字段均命名为 create_by而非 creator 或 created_by_user。2. 字段名命名规范业务命名 / 中文注释用注此处“字段名”指在数据库建模工具如 PDManer、PowerDesigner中用于显示的逻辑字段名称非物理列名。中文命名在数据模型文档或ER图中字段应使用中文描述确保业务人员可理解。示例物理列名user_id → 逻辑名称用户ID物理列名create_time → 逻辑名称创建时间简洁明了避免技术术语堆砌如“用户唯一标识符”应简化为“用户ID”。避免歧义如“时间”应明确为“创建时间”或“更新时间”不可仅写“时间”。3. 字段注释规范高质量的字段注释是数据库自文档化的关键简洁明了注释应一句话说明字段用途。示例user_status 的注释为“用户状态0-禁用1-启用”业务驱动注释应从用户或业务视角出发而非技术实现。✅ 好注释“是否接收营销短信0-否1-是”❌ 差注释“布尔标志位用于短信订阅”注释内容应包含字段业务含义枚举值说明如有是否允许为空NULL是否为主键/外键默认值如有统一风格建议采用如下模板“[业务含义]。取值[枚举说明]。[其他约束]”示例“订单支付状态。取值0-未支付1-已支付2-已退款。非空。”示例 SQLsqlCREATE TABLE um_user (id BIGINT PRIMARY KEY COMMENT 主键ID。非空唯一标识用户记录。,username VARCHAR(50) NOT NULL COMMENT 用户名。唯一且非空。,password VARCHAR(100) NOT NULL COMMENT 登录密码。加密存储非空。,status TINYINT DEFAULT 1 COMMENT 用户状态。取值0禁用1启用。非空默认1。,create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 记录创建时间。默认当前时间。,update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 记录最后修改时间。自动更新。) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户信息表。用于存储系统用户的基本信息。;4. 字段数据类型设置规范合理选择数据类型业务场景推荐类型示例用户ID、订单IDBIGINT自增主键或雪花ID用户名、邮箱VARCHAR(50~255)根据实际长度预估密码VARCHAR(100)存储加密后的字符串金额DECIMAL(18,2)避免使用 FLOAT/DOUBLE创建时间DATETIME精确到秒是否删除TINYINT(1)0-否1-是长文本如描述TEXT不建议超过 64KB预设类型配置建议在数据库建模工具中预定义常用字段模板如“标准主键”、“标准时间戳”供设计时直接复用。兼顾扩展性如手机号当前为11位但未来可能支持国际号码建议 VARCHAR(20) 而非 CHAR(11)。避免过度设计不要为“可能将来会用到”的场景预留超大字段。例如用户昵称通常不超过30字符无需定义为 VARCHAR(255)。五、字段排序原则合理的字段顺序有助于提升可读性和查询效率。1. 常规字段排序推荐顺序主键字段如 id外键字段如 user_id, project_id核心业务字段如 username, order_no, amount状态/分类字段如 status, type, gender元数据字段如 create_by, create_time, update_time, del_flag示例um_user 表字段顺序sqlid,tenant_id, -- 多租户场景username,email,phone,status, -- 0-禁用 1-启用gender, -- 性别0-未知 1-男 2-女create_by,create_time,update_time,del_flag2. 类型字段排序分类字段靠前如 gender、user_type、order_source 等便于 WHERE 条件过滤。枚举字段优先高频使用的枚举字段如状态建议排在业务字段之后、时间字段之前。六、字段分组原则1. 相关字段分组逻辑相关字段集中放置用户基本信息username, email, phone, avatar审计字段create_by, create_time, update_by, update_time软删除字段del_flag, delete_time数据类型相似字段分组所有时间字段集中create_time, update_time, login_time所有金额字段集中order_amount, discount, pay_amount2. 频繁查询字段优先高频查询字段靠前如 user_id、order_status、create_time 等常用于 WHERE 或 JOIN 的字段应置于表前部。索引字段显性化虽然字段物理位置不影响索引性能但将索引字段靠前有助于开发人员快速识别关键字段。附录常见字段命名及其规范序号字段含义字段名称备注类型长度1分类/类别category用于表示类别或分类的字段。VARCHAR1282类型type用于表示类型的字段常见于状态类型等。VARCHAR103描述description用于简要描述某个数据实体的字段。SQL中多处使用 varchar(512)。VARCHAR5124介绍introduction用于详细介绍或说明某个数据实体的字段。TEXT—5编码code唯一标识编码如商品编码用户编码等。VARCHAR1286级别level用于表示级别、权限等级等。INT—7内容content用于存储主要的内容字段。TEXT—8排序值order_num排序字段通常用于排序或优先级控制。INT—9状态status状态字段如启用/禁用激活/冻结等。CHAR110审核状态audit_status审核状态如待审核、审核通过、审核拒绝。CHAR111审核人auditor_id审核人通常是审核人的ID或用户名。BIGINT—12审核时间audit_time表示审核的时间通常用于记录审核的具体时间点。DATETIME—13名称name用于表示名称字段如用户姓名项目名称等。VARCHAR12814手机号phone手机号码字段。VARCHAR2015邮箱email邮箱字段。VARCHAR10016地址address地址字段。VARCHAR25517性别gender性别字段如男/女。TINYINT118年龄age年龄字段。INT—19生日birthday出生日期字段。DATE—20头像avatar头像字段。VARCHAR25621创建人create_by记录创建人的姓名。VARCHAR3222创建人idcreator_id记录创建人的id。BIGINT—23创建时间create_time记录创建时间字段。DATETIME—24更新人update_by记录最后更新人的姓名。VARCHAR3225更新人idupdater_id记录最后更新人的id。BIGINT—26更新时间update_time记录最后更新时间字段。DATETIME—27删除人delete_by软删除字段表示记录删除者的姓名。VARCHAR3228删除人iddeleter_id软删除字段表示记录删除者的id。BIGINT—29删除时间delete_time软删除字段表示记录删除的时间。DATETIME—30是否删除del_flag软删除标志0: 未删除1: 已删除。VARCHAR131是否有效valid_flag用于表示是否有效0: 无效1: 有效。VARCHAR132是否锁定lock_flag用于表示是否被锁定0: 未锁定1: 已锁定。VARCHAR133是否启用enable_flag0表示禁用1表示启用。CHAR134是否必填require_flag用于表示字段是否为必填项0: 不必填1: 必填。VARCHAR135备注remark用于补充说明或备注信息。VARCHAR51236父级IDparent_id记录的父级ID用于树状结构或层级关系的父节点标识。BIGINT—37子级IDchild_id用于树状结构或层级关系的子节点标识。BIGINT—38路径path用于存储路径信息如文件路径URL路径等。VARCHAR25639关联IDrelated_id关联其他实体的ID如订单关联的用户ID。BIGINT—40操作类型action_type操作的类型如添加、删除、更新等。CHAR141权限permission权限字段。VARCHAR10042权限组role_group权限组字段例如角色分配。VARCHAR10043文件名file_name文件的名称。VARCHAR12844文件大小file_size文件的大小。BIGINT—45文件路径file_path文件存储路径。VARCHAR25646文件类型file_type文件类型如pdfjpgpng等。VARCHAR3247过期时间expiry_date表示某个数据是否过期的时间字段。DATETIME—48时间戳timestamp用于记录时间戳。DATETIME—49请求来源source请求来源字段。CHAR150请求参数params请求参数字段。TEXT—51请求IPrequest_ip请求来源IP地址。VARCHAR4552IP地址ip_address存储IP地址字段。VARCHAR4553版本号version数据版本字段记录版本号。INT—54是否默认default_flag是否为默认值字段0: 否1: 是。VARCHAR155触发时间trigger_time触发事件的时间。DATETIME—56响应时间response_time响应事件的时间。DATETIME—57访问次数visit_count记录访问次数。INT—58验证信息verification_info用于存储验证信息如验证码。VARCHAR10059唯一标识符uuid唯一标识符常用于分布式系统中。VARCHAR3660序列号serial_no常用于物品、订单、产品等的唯一编号。VARCHAR6461标签tag记录项目、用户、产品等的标签信息。VARCHAR128遵守规范方能行稳致远。原文链接https://juejin.cn/post/7572087281022681134