V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
wenerme
V2EX  ›  程序员

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

  •  
  •   wenerme ·
    wenerme · 214 天前 · 1566 次点击
    这是一个创建于 214 天前的主题,其中的信息可能已经有所发展或是发生改变。

    平时总结的自己做开发时设计 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 之类的作为区分
    • 参考

    尽量使用外键 {#fk}

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

    1 条回复    2024-04-20 19:39:32 +08:00
    v2webdev
        1
    v2webdev  
       205 天前
    笔记写的太简洁了,估计只有 OP 自己能看懂啊。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3361 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 11:50 · PVG 19:50 · LAX 03:50 · JFK 06:50
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.