往期回顾 链接

      Oracle数据库设计规范篇{一}——表规范  

2.1 基本规范

2.1.1 长度规范

凡是需要命名的对象其标识符均不能超过30个字符，也即：Oracle中的表名、字段名，函数名，过程名，触发器名，序列名，视图名的长度均不能超过30个字符；

-- 正确示例（长度合适）
CREATE TABLE EMPLOYEE_SALARY;
-- 错误示例（超过30字符）
CREATE TABLE EMPLOYEE_MONTHLY_SALARY_HISTORY_ARCHIVE;

2.1.2 构成规范

数据库各种名称必须以字母开头，但严禁使用SYS开头；名称只能含有字母，数字和下划线“_”三类字符，“_”用于间隔名称中的各语义字段；不要使用DUAL作表名；

-- 正确示例
CREATE TABLE HR_EMPLOYEE;
CREATE TABLE ORDER_DETAIL_2023;
-- 错误示例
CREATE TABLE 1EMPLOYEE;       -- 不能以数字开头
CREATE TABLE SYS_CONFIG;      -- 不能以SYS开头
CREATE TABLE ORDER-DETAIL;    -- 不能使用横杠
CREATE TABLE DUAL;            -- 不能使用DUAL作表名

 2.1.3 大小写规范

构成Oracle数据库中的各种名称（表明，字段名，过程名，视图名等等）的所有字符，必须使用大写，也就是不能在脚本中，对任何名称添加双引号“”来设定字符的大小写形式，只要不采用“”限制，Oracle自动会将各名称转化成大写。

-- 正确示例（Oracle会自动转为大写）
CREATE TABLE employee;  -- 实际创建为EMPLOYEE
-- 错误示例（使用双引号指定大小写）
CREATE TABLE "Employee";  -- 区分大小写，不符合规范

2.1.4 主键规范

主键设计应遵循以下规范：

 1. 除临时表和外部表，以及流水表，日志表外，其他表都要建立主键；

 2. 主键不能使用含有实际语义的列，应该增加一个xx_id字段做主键，类型为number，取值来自序列sequence；

 3. 对于500万以上的表，请数据组参与设计实施，采用先建唯一索引再添加主键约束的方式来创建主键。

 -- 创建序列
CREATE SEQUENCE SEQ_EMPLOYEE START WITH 1 INCREMENT BY 1;
-- 正确的主键设计
CREATE TABLE EMPLOYEE (
    EMPLOYEE_ID NUMBER PRIMARY KEY,  -- 无业务含义的ID作主键
    EMPLOYEE_CODE VARCHAR2(10) UNIQUE,  -- 业务编码设为唯一索引
    EMPLOYEE_NAME VARCHAR2(50) NOT NULL
);
-- 大表主键创建方式（500万行以上）
CREATE TABLE LARGE_TABLE (
    ID NUMBER,
    DATA VARCHAR2(100)
);
-- 先创建唯一索引
CREATE UNIQUE INDEX IDX_LARGE_TABLE_PK ON LARGE_TABLE(ID);
-- 再添加主键约束
ALTER TABLE LARGE_TABLE ADD CONSTRAINT PK_LARGE_TABLE PRIMARY KEY (ID) 
USING INDEX IDX_LARGE_TABLE_PK;

2.1.5 要有注释

每个表，每个字段都要有注释，说明其含义，对于冗余字段还要特别说明其维护方法，外键字段说明参照与那个表

CREATE TABLE EMPLOYEE (
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    DEPARTMENT_ID NUMBER,
    EMPLOYEE_NAME VARCHAR2(50) NOT NULL,
    TOTAL_SALARY NUMBER(10,2)
);
-- 表注释
COMMENT ON TABLE EMPLOYEE IS '员工基本信息表';
-- 字段注释
COMMENT ON COLUMN EMPLOYEE.EMPLOYEE_ID IS '员工ID，主键';
COMMENT ON COLUMN EMPLOYEE.DEPARTMENT_ID IS '部门ID，参照DEPARTMENT表';
COMMENT ON COLUMN EMPLOYEE.EMPLOYEE_NAME IS '员工姓名';
COMMENT ON COLUMN EMPLOYEE.TOTAL_SALARY IS '员工总薪资（冗余字段，每月薪资发放后更新）';

2.1.6一个表所含字段总长度的规范

一个表中的所有字段，应当能存储在一个数据块中（BLOCK），也即：表的单行字段总长度<db_block(减去pctfree)

2.2 表设计规范

2.2.1 一般需要有主键

• 主键最好设计成单一主键；

• 尽量不用复合主键；

• 尽量使用没有业务语义的字段作为主键，建议使用系统生成的键作为主键。

例如:oracle的sequence来维护一个主键。主键一般建议使用数值型，这样提高检索效率。

 -- 推荐：使用序列生成的ID作主键

CREATE SEQUENCE SEQ_CUSTOMER;
CREATE TABLE CUSTOMER (
    CUSTOMER_ID NUMBER PRIMARY KEY,  -- 使用序列值
    CUSTOMER_CODE VARCHAR2(20) UNIQUE,  -- 业务编码设为唯一
    CUSTOMER_NAME VARCHAR2(100) NOT NULL
);

-- 不推荐：使用业务字段作主键

CREATE TABLE CUSTOMER_BAD (
    CUSTOMER_CODE VARCHAR2(20) PRIMARY KEY,  -- 使用业务编码作主键
    CUSTOMER_NAME VARCHAR2(100) NOT NULL
);

2.2.2 尽量规划在同一表空间

对于每个应用系统都能用到的表放到同一个表空间中，一般是系统配置表。如果应用系统还包括一些大数据量的图片等多媒体数据表，应该放入第三个库中，这样可改善数据库的性能。

-- 业务数据表
CREATE TABLE CUSTOMER (
    CUSTOMER_ID NUMBER PRIMARY KEY,
    CUSTOMER_NAME VARCHAR2(100)
) TABLESPACE BUSINESS_DATA;
-- 系统配置表
CREATE TABLE SYS_PARAMETER (
    PARAM_ID NUMBER PRIMARY KEY,
    PARAM_NAME VARCHAR2(50),
    PARAM_VALUE VARCHAR2(200)
) TABLESPACE SYSTEM_CONFIG;
-- 多媒体数据表
CREATE TABLE DOCUMENT (
    DOC_ID NUMBER PRIMARY KEY,
    DOC_NAME VARCHAR2(100),
    DOC_CONTENT BLOB
) TABLESPACE MEDIA_DATA;

2.2.3  大表尽量要有明确的数据保留策略

• 在设计阶段 应与需求人员沟通，明确业务字段的生命周期，并与本单位的数据生命周期管理向匹配，制定表的数据迁移、保留策略。

• 在表的物理设计阶段 每个表中都应该添加的3 个有用的字段：记录创建日期，记录创建/更新人，记录的版本标记；有助于准确说明记录中出现null数据或者丢失数据的原因。

2.2.4 因大表坚决不允许有触发器

触发器的功能通常可以用其他方式实现。在调试程序时触发器可能成为干扰。假如你确实需要采用触发器，你最好集中对它文档化。

2.2.5 包含版本机制

建议你在数据库中引入版本控制机制来确定使用中的数据库的版本。无论如何你都要实现这一要求。时间一长，用户的需求总是会改变的。最终可能会要求修改数据库结构。虽然你可以通过检查新字段或者索引来确定数据库结构的版本，但把版本信息直接存放到数据库中会更方便。

2.2.6 中间表

• 在通过ER模型设计表时，中间表是针对多对多关系的。

• 一般中间表都是有一个本表的自增主键，还有另外两个表的主键。

• 中间表是没有属性的因为它不是一个基本表。

未完待续...

Oracle数据库设计规范篇{三}——物理设计规范【中】（分区设计规范）

公众号：收获不止数据库

微信：ljbyxl1213

• “大白话人工智能” 系列

• “数据库拍案惊奇” 系列

• “世事洞明皆学问” 系列