数据设计规范

目录

一、数据库设计的原则

二、表设计原则

三、其他设计规范

四、最佳实践

---

数据库设计（Database Design）是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能够有效地存储数据，满足各种用户的应用需求（信息要求和处理要求）。以下是一些关键的数据库设计规范：

一、数据库设计的原则

1. 数据独立性：数据与应用程序相互独立，改变数据存储结构或访问方法不影响应用程序。数据独立性分为逻辑数据独立性和物理数据独立性。逻辑数据独立性指数据库的逻辑结构改变不影响应用程序，物理数据独立性指数据在物理存储介质上的变化不影响数据库的逻辑结构。实现数据独立性的方法包括使用抽象层次、数据模型和视图。
2. 数据完整性：数据的准确性和一致性。它包括实体完整性、参照完整性和域完整性。实体完整性要求每个表中的每一行都必须是唯一的，通常通过主键来实现。参照完整性要求外键值必须在参考表中存在，以确保数据的一致性。域完整性确保每列的数据类型、格式和范围的一致性。实现数据完整性的方法有使用约束（如主键、外键、唯一性约束）、触发器和存储过程。
3. 数据一致性：多个用户同时访问和修改数据库时，数据保持一致的状态。数据一致性通常通过事务管理来实现。事务是一个逻辑操作单元，包含一个或多个SQL语句，具有原子性、一致性、隔离性和持久性（ACID属性）。
4. 数据冗余最小化：在数据库设计中尽量减少数据的重复存储。过多的冗余数据不仅浪费存储空间，还容易导致数据的不一致。减少数据冗余的方法有规范化和数据库分区。
5. 数据安全性：通过身份验证、访问控制和数据加密等方法保护数据的安全性，防止数据泄露和篡改。
6. 性能优化：提高数据库的访问速度和处理能力。常见的性能优化方法有索引优化、查询优化和存储优化。

二、表设计原则

1. 规范化与反规范化：

   • 规范化：优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度。但是一个完全规范化的设计并不总能生成最优的性能，因为对数据库查询通常需要更多的连接操作，从而影响到查询的速度，而且范式越高性能就会越差。
   • 反规范化：为了提高某些查询或应用的性能，可以破坏规范规则。数据应当按两种类别进行组织，即频繁访问的数据和频繁修改的数据。对于频繁访问但是不频繁修改的数据，内部设计应当物理不规范化。对于频繁修改但并不频繁访问的数据，内部设计应当物理规范化。

2. 数据表分类：

   • 基本数据表：描述业务实体的基本信息，如人员基本信息、单位基本信息等。
   • 标准编码表：描述属性的列表值，如职称、民族、状态等。
   • 业务数据表：记录业务发生的过程和结果，如人员调动登记、变更通知单等。
   • 系统信息表：存放与系统操作、业务控制有关的参数，如用户信息、权限、用户配置信息等。
   • 统计数据表：存放业务数据统计值，如通知单统计、人员类别统计等。
   • 临时处理表：存放业务处理过程中的中间结果。
   • 其他类型表：存放应用层的日志、消息记录等。

3. 字段设计：

   • 应该使用能正确存储和表示数据的最小类型。如果不确定需要什么数据类型，则选择不会超出范围的最小类型。
   • 选择更简单的数据类型，例如比较整数的代价小于比较字符，因为字符集和排序规则使字符比较更复杂。
   • 尽可能把字段定义为NOT NULL。对于字段能否为NULL，应该在SQL建表脚本中明确指明，不应使用缺省。
   • 一个表中的字段不要太多，理论上不要超过80个。
   • 数据库中所有布尔型中数值0表示为假，数值1表示为真。
   • 当字段定义为字符串类型时使用VARCHAR2而不用NVARCHAR。
   • 字段尽可能有默认值，字符型的默认值为一个空字符值串，数字型的默认值为数值0。

4. 键和索引设计：

   • 为关联字段创建外键。
   • 所有的键都必须唯一。
   • 尽可能避免使用复合键。
   • 外键总是关联唯一的键字段。
   • 尽可能使用系统生成（如序列SEQUENCE产生）的主键。
   • 可选键有时可做主键。
   • 一个表中组合主键的字段个数尽可能少。
   • 如果一列出现在表达式或函数中，不会使用该列上的索引。
   • 要索引外键。
   • 对于索引选择性高的列使用B-Tree索引。
   • 对于索引选择性低的列使用位图索引。
   • HASH索引只适用于相等比较。
   • 不要索引大型字段（有很多字符的字段）。
   • 不要索引常用的小型表。如无特别需要，避免使用大字段（BLOB、CLOB、LONG等）。如使用时必须使用BLOB或CLOB类型。

三、其他设计规范

1. 采用数据库系统实现数据的完整性：这不但包括通过标准化实现的完整性，而且还包括数据的功能性。在写数据的时候还可以增加触发器来保证数据的正确性。不要依赖于应用程序保证数据完整性，它不能保证表之间（外键）的完整性。每个表要求有主键，主键字段或组合字段必须满足非空属性和唯一性要求。

2. 视图设计：为了在数据库和应用程序之间提供另一层抽象，可以为应用程序建立专门的视图而不必非要应用程序直接访问表。这样做还在处理数据库变更时提供了更多的自由。

   • 为简化查询，将复杂的检索或子查询通过视图实现。
   • 提高数据的安全性，只将需要查看的数据信息显示给权限有限的人员。
   • 视图中如果嵌套使用视图，级数不要超过3级。
   • 对于数据量较大或随时间的推移逐渐增多的表，不宜使用视图，可以采用实体化视图代替。
   • 除特殊需要，避免类似“SELECT * FROM [TableName]”而没有检索条件的视图。
   • 视图中尽量避免出现数据排序的SQL语句。

3. 命名规范：

   • 所有命名采用26个英文大小写字母和0~9这十个自然数，加上下划线_组成，不能出现其他字符（注释除外）。
   • 长度不超过30个字符。
   • 实际名字尽量描述实体的内容，由英文单词、单词组合或单词缩写组成，不以数字和_开头。
   • 命名中禁止使用SQL关键字。
   • 对象名尽量短。表以单数形式名词或名词短语命名。如果表名仅有一个单词，那么建议不使用缩写，而是用完整的单词。数据表t_inf_<系统标识>_<表标识>，编码表t_cod_<系统标识>_<表标识>，系统表t_sys_<系统标识>_<表标识>，统计表t_sta_<系统标识>_<表标识>，临时表t_tmp_<系统标识>_<表标识>，日志表t_log_<系统标识>_<表标识>。
   • 采用有意义的字段名，应该是易于理解、能表达字段功能的英文单词或单词缩写，一般不超过三个英文单词。
   • 系统中所有属于内码的字段（仅用于表示唯一性和程序内部用到的标识性字段），名称取为ID。
   • 系统中属于业务范围内的编号的字段，其代表一定的业务信息，这样的字段建议命名为CODE，其数据类型为VARCHAR，该字段需加唯一索引。
   • 不要在列的名称中包含数据类型。主键PK_<表名>，外键FK_<表名>_<主表名>_<外键字段名>。如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。视图V_<系统标识>_<视图标识>，存储过程SP_<系统标识>_<存储过程标识>，函数F_<系统标识>_<函数标识>，触发器TR_<表名>_<i、u、d的任意组合>，用户定义数据类型UD_<自定义数据类型标识>，序列SEQ_<序列标识>，局部变量L_<变量标识>，全局变量G_<变量标识>，游标变量L_CUR_<变量标识>或G_CUR_<变量标识>，存储过程或函数定义中的参数（IN型参数P_<参数标识>，OUT型参数R_<参数标识>，函数返回值R_<变量标识>）。

4. 安全性设计：在生产环境中，必须严格管理SYS和SYSTEM用户，必须修改其默认密码，禁止用该用户建立应用数据库对象。删除或锁定SCOTT等默认安装但不使用的用户。

四、最佳实践

1. 考量所有相关利益者：在构建数据库之前，先去收集信息，了解他们对数据库的期望以及对数据库的操作熟练度。这样就能得出数据库应当采用的技术水平，以及是否要就数据库的功能来训练用户。
2. 选择正确的数据库类型：数据库有多种类型，选择正确类型则是数据库设计的关键。可以将数据库以两种方式分类，一是基于数据库用以定义和操作数据的查询语言，二是基于数据模型。研究数据库的不同类型，并针对应用需求作出选择，是必要的初始步骤。
3. 文档化：文档化对于良好的数据库设计至关重要，可以追踪所有的小细节。数据库设计应当附有指示说明、ER图、存储过程及所有其他相关的信息。文档还应当为编程者和终端用户提供足够信息量，确保他们能够理解并使用。
4. 考虑隐私和安全性：为了获得最大程度的安全性，应当对密码加密，使用身份验证来限制数据库的访问，