数据库设计原则

发表于 | 分类于 |

引言

  • 本文小结了数据设计原则；
  • 数据库设计对于数据库的可维护性、可扩展性至关重要，某些原则必须严格遵守；

数据库设计范式

  • 第一范式：属性具有原子性，不可再分解，即不能表中有表；
  • 第二范式：唯一性约束，每条记录有唯一标示，所有的非主键字段均需依赖于主键字段；
  • 第三范式：冗余性约束，非主键字段间不能相互依赖；

数据库设计原则

  • 完整性：
    • not null声明禁止插入空值；
    • check子句限制属性域；
  • 去冗余：
    • 避免冗余属性，冗余属性会带来数据不一致性；
    • 学生选课系统中，老师可以开课、学生可以选课，数据库设计中，课程可以由课程编号和课程名称等确定；
    • 如果现在维护两个表，一个表A存储课程信息（课程编号、名称、简介、学分、院系等），另一个表B存储开课信息（有哪些课程开课），如果B中重复存储了A的课程名称、简介、学分、院系等信息，一旦A中的信息更新，B中和A中信息便出现不一致；
    • 正确的做法是，B中只存储课程编号，并以此和A相关联；
  • 解耦合：
    • 一个表只存储它应该存储的信息，和此表无关的信息放到另一个表去存储，表之间尽量解耦；
    • 上面的例子中，A中存储且只存储面向课程的信息，另外有表C，存储且只存储面向学生的信息（学号、姓名、性别、年龄、选课id等），对于“课程级别”的信息，应当坚决的存储在A而不是C中，而且尽量避免将A、C合并成一个表（可能刚开始是设计成一个表），而且A、C间尽量解耦；
  • 字段不可再分：
    • 一个字段中不要出现分隔符，或者在一个字段中存储多个信息;
    • 例如，first name和last name不要放在同一个字段中，稳定版本号和临时版本号不要放在同一个字段中；
  • 考虑性能：
    • 上述原则可能造成多表连接查询的情况出现，降低性能；
    • 如果性能成为主要矛盾，则上述原则也不绝对；

数据库命名原则

  • 数据库的命名会直接影响到上层应用的名称，所以要和业务部门仔细讨论、慎重确定；
  • 每个属性名在数据库中只有唯一的含义，number这个属性名可能表示电话号码或是房间号，这是一种容易引起歧义的命名；
  • 数据库的名词要一致，不能在这个地方叫一个名字，到另外一个表又叫另外一个名字；
  • 一般来说，Table命名用单数，Column命名用单数；
  • Table不用Prefix前缀来表示不同的组，而用schema来划分命名空间（postgresql中）；
  • 命名用snake_case，不要有其他特殊字符；
  • 名称中不要有sql关键字；
  • 如果确实需要使用sql关键字，可用双引号包围，比如CREATE TABLE "order"(...)；
  • 主键的名字永远都是同一的，就是id，外键名称才需要加table的名字，诸如xxx_id、yyy_id；
  • 命名不要用缩写，比如date缩写成dt；
  • 用 create_date/update_date/sample_date这些含义更明确的名称代替date这个命名，这样不仅表达更准确，而且避免了用关键字的麻烦；
  • timestamp类型的字段要有timezone（时区），字段名用xxx_date的形式，仅表示年月日用xxx_day，仅表示时分秒用xxx_time；
  • 表示数量、次数等概念的字段名称最好写为xxx_count，不要写为xxx_number/xxx_num/xxx_no等；
  • boolean类型的命名要用is_xxx格式；

数据库设计其他注意事项

  • 每个表都要有主键，名称是id，类型为bigint；
  • 主键的类型是设为integer还是long，取决于这个系统用多长时间，如果要用100年，主键还是设置为long类型较好，这样用的很久以后id也不会超出范围；
  • 一个字段不要有多个用途，空间不是问题，清晰才是重点；
  • 不要过早优化，先把东西做出来再说，遇到性能问题再去优化；
  • 对于varchar类型的字段，当字符串并不是非常明确到底限制是多少的时候，通常选择255这个长度，varchar(50)并不比varchar(255)节省空间，varchar(50)仅仅是表示最多分配50个字符而已；
  • varchar(100)类型在PostgreSQL中代表100个字符，而在Oracle中代表100字节，具体的占用空间数目和语言、编码方式有关；
  • 对外键要加Index；
  • 数据库里面的密码一定要加密，不能保存明文；
  • 用is_deleted=true来表示本条记录的业务上的删除，不要在数据库中真正删除记录，或者仅仅是版本化修改，这样能防止数据丢失；

数据库性能提升方案

  • 使用索引会大大提升查询效率，同时降低在被索引的表上INSERT和DELETE效率；
  • 分离频繁和不频繁使用的数据到多个表中；
    • 例如，原先，一个表中保存用户名、密码、年龄、个人简介、学校等信息，但是发现访问用户名、密码、年龄的频率远高于其他字段，此时就应当将这个表分为两个表，分别存储频繁访问项和非频繁访问项；

数据库安全策略

  • 至少保存3个月的系统访问日志；
  • 数据库中的表可以有创建和更新时间戳，及所创建/修改行的用户标示；
  • 不删除字段，而是打上一个被删除的标记；
  • 版本化修改；

大型数据库设计

  • 负载均衡；
  • 读写分离；
  • 分布式存储；
  • 参考这篇文章；
您的支持是对我最大的鼓励！