关于设计设计数据库的原则

[wenerme]

平时总结的自己做开发时设计 Schema 的一些原则，大家有什么这方面的分享么？ 这里放不下太多内容，贴一些主要的

• 表尽量不要前缀 - 清晰明了
  • MySQL 额外考虑
  • PG 支持 Schema 隔离 - 避免直接使用 public schema
• 字段尽量不要缩写
• 尽量不要用 拼音
  • 做国内环境除外 - 例如：政企数据无法很好翻译
• 维护开发字典 - 例如 开发用词字典
• 尽量 不要 用自增长 ID
  • 容易被遍历
  • 面向用户的可以 增加额外的 自增长 编号/序号
• 使用 有序的 随机主键 - ULID, UUID
• 建议主键增加 type tag
• PostgreSQL
  • 尽量用 text, bigint, jsonb, bool
  • 看情况用 array - array 能简化不少需要 join 表的场景 - 例如 tags text[]
  • 避免 varchar(n) 限定长度
    • 业务层控制 validation
    • 通过 check 验证

column	for
id	主键 - ULID, tagged ID
sid	租户维度单调递增 - 用户友好
uid	UUID
tid	租户 ID
eid	用于导入数据关联 - tid+eid 唯一
cid	外部系统租户 ID - Colocate ID/Corp ID - tid+cid+rid 唯一
rid	外部系统资源 ID - Ref ID/Relative ID
created_at
updated_at
deleted_at
version	基于版本的乐观锁
metadata	补充数据
attributes	使用端自定义数据 - 客户端 读写
properties	服务端自定义数据 - 客户端 只读
extensions	内部扩展数据 - 客户端 不可见
owner_id	所有者
owner_type	User, Team, Department, Organization
owner_user_id	case owner_type when 'User' then owner_id end
owner_team_id	case owner_type when 'Team' then owner_id end
entity_id	关联任意实体
entity_type
created_by_id
updated_by_id
deleted_by_id
state	状态 - 面向系统，不可自定义
status	业务状态、阶段、原因、细节 - 可自定义

• eid
  • 同质系统导入外建关联 - 例如: SaaS <-> 现存内部系统
  • 也可能会导出再导入
• cid & rid
  • 非同质系统 - 例如: 服务商、平台
  • -> sourceType+sourceId
  • -> vendorType+vendorId

create table tpl_res
(
    -- 基础
    id                  text        not null default gen_ulid(),
    tid                 bigint      not null default current_tenant_id(), -- 租户
    uid                 uuid        not null default gen_random_uuid(),
    sid                 bigint      not null default (next_res_sid('tpl_pri_resources')),
    eid                 text        null , -- 用于导入数据关联
    created_at          timestamptz not null default current_timestamp,
    updated_at          timestamptz not null default current_timestamp,
    deleted_at          timestamptz,
    -- auditor 信息
    created_by_id       text                 default current_setting('app.user.id'),
    updated_by_id       text                 default current_setting('app.user.id'),
    deleted_by_id       text,
    -- 按需附加任意层面的数据
    -- 例如: attributes 允许客户端修改, properties 不允许客户端修改, extensions 客户端不可见
    extensions          jsonb,
    properties          jsonb,
    attributes          jsonb,
    -- 业务 owner 信息
    owner_id            text,
    owner_type          text,
    owner_uid           uuid,
    owner_id            text,
    owner_type          text, -- User, Team, Department
    owner_user_id       text generated always as ( case owner_type when 'User' then owner_id end ) stored,
    owner_team_id       text generated always as ( case owner_type when 'Team' then owner_id end ) stored,
    owner_department_id text,
    primary key (tid, id),
    unique (tid, sid),
    unique (tid, uid)
);

FAQ

created_at vs create_time

• created_at, *_at
  • 语义 准确
  • 与 created_by_id 形式上类似
  • 使用: Spring, Gorm 默认
  • 面向 系统
• create_time, *_time
  • 使用: AIP
  • 面向 用户, 业务

扩展 {#extension}

• extensions
  • 内部使用
• properties
  • 服务端使用，前端可见
• attributes
  • 前端使用，服务端可见
• metadata
  • 对数据内容的补充说明
• raw
  • 外部导入原始数据
  • 也可以记录到 metadata, properties.raw, extensions.raw

单数还是复数表名 {#plural}

推荐单数形式。 部分关键词使用复数: users, groups 。

• 复数
  • 大多框架默认
  • 语义上更准确
  • 逻辑上更复杂
• 单数
  • 代码层面更好统一
  • 但部分单数形式可能需要 quote
  • user 也可以用 app_user 之类的作为区分
• 参考
  • https://stackoverflow.com/questions/338156

尽量使用外键 {#fk}

• 能一定程度提升查询性能
• 增加部分 插入 和 更新 成本
• 确保业务逻辑准确
• 非强业务看情况

---

• 原文 https://wener.me/notes/dev/design/schema
• 原始 https://github.com/wenerme/wener/blob/master/notes/dev/design/design-schema.md

[v2webdev]

笔记写的太简洁了，估计只有 OP 自己能看懂啊。