1 表规范

1.1 逻辑设计方法及规范

一个优秀的数据库设计，应该是满足需求的前提下，实现性能最优的综合考虑，因此，数据库表的设计都应经过2个阶段：逻辑设计阶段（将需求转化为数据库表达的方式），物理设计阶段（对逻辑设计的结果物理调优设计）。

1.1.1采用ER模型进行逻辑设计

实体关系模型（ER模型，以下简称ER模型）是人们认识客观世界的一种方法、工具。ER模型的设计过程，基本上是两大步：

先设计实体类型（此时不要涉及到“联系”）；
再设计联系类型（考虑实体间的联系）。

具体设计时，有时“实体”与“联系”两者之间的界线是模糊的。数据库设计者的任务就是要把现实世界中的数据以及数据间的联系抽象出来，用“实体”与“联系”来表示。E-R模型还描述了数据库对其内容必须遵守的某些约束，一个重要概念是映射基数，它表示通过某个联系集与另外一个实体的关联的实体数目，包括：一对一，一对多，多对一，多对多。另外， ER模型应该充分反映用户需求，ER模型要得到用户的认可才能确定下来。

建议采用主流的模型设计软件工具PowerDesigner， ERWin等进行数据库设计。

1.1.2 首先遵循范式设计

在设计前，要考虑表的规范化级别，即要求所有的关系模式都达到第几范式。由于函数依赖是现实环境中最重要的、最大量的数据依赖，一般提出3NF或BCNF的要求就足够了。简单来说，第三范式以上的设计消除了字段的冗余，能有效避免出现数据变化时容易产生的不一致的情况，设计范式定义如下：

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。

 -- 不符合1NF的设计（地址字段可再分）
CREATE TABLE EMPLOYEE_BAD (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(100),
    ADDRESS VARCHAR2(200) -- 包含街道、城市、邮编等多个属性
);
-- 符合1NF的设计
CREATE TABLE EMPLOYEE_GOOD (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(100),
    STREET VARCHAR2(100),
    CITY VARCHAR2(50),
    POSTAL_CODE VARCHAR2(10)
);

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

实体表中一般不会出现违反2NF的情况，因为都是“一个”主键列，而关系表是两个以上列的“复合”主键，故而关系表容易出现违反2NF的情况。

  -- 不符合2NF的设计（复合主键情况下）
CREATE TABLE ORDER_ITEM_BAD (
    ORDER_ID NUMBER,
    PRODUCT_ID NUMBER,
    QUANTITY NUMBER,
    PRODUCT_NAME VARCHAR2(100), -- 只依赖于PRODUCT_ID，不完全依赖主键
    CONSTRAINT PK_ORDER_ITEM_BAD PRIMARY KEY (ORDER_ID, PRODUCT_ID)
);
-- 符合2NF的设计
CREATE TABLE PRODUCT (
    PRODUCT_ID NUMBER PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(100)
);
CREATE TABLE ORDER_ITEM_GOOD (
    ORDER_ID NUMBER,
    PRODUCT_ID NUMBER,
    QUANTITY NUMBER,
    CONSTRAINT PK_ORDER_ITEM_GOOD PRIMARY KEY (ORDER_ID, PRODUCT_ID),
    CONSTRAINT FK_ORDER_ITEM_PRODUCT FOREIGN KEY (PRODUCT_ID)
        REFERENCES PRODUCT (PRODUCT_ID)
);

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A→ B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段x →非关键字段y 违反3NF的情况，绝大多数是在含有外键的表中。

 -- 不符合3NF的设计
CREATE TABLE EMPLOYEE_DEPT_BAD (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(100),
    DEPT_ID NUMBER,
    DEPT_NAME VARCHAR2(50), -- 依赖于DEPT_ID，而非直接依赖主键
    DEPT_LOCATION VARCHAR2(100) -- 依赖于DEPT_ID，而非直接依赖主键
);
-- 符合3NF的设计
CREATE TABLE DEPARTMENT (
    DEPT_ID NUMBER PRIMARY KEY,
    DEPT_NAME VARCHAR2(50),
    DEPT_LOCATION VARCHAR2(100)
);
CREATE TABLE EMPLOYEE_GOOD (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(100),
    DEPT_ID NUMBER,
    CONSTRAINT FK_EMP_DEPT FOREIGN KEY (DEPT_ID)
        REFERENCES DEPARTMENT (DEPT_ID)
);

鲍依斯-科得范式（BCNF）：在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BCNF范式。

-- BCNF示例
-- 假设场景：一个学生在一个时间段只能上一门课
-- 但一门课在不同时间段可以有不同老师教授
-- 不符合BCNF的设计
CREATE TABLE COURSE_SCHEDULE_BAD (
    STUDENT_ID NUMBER,
    TIME_SLOT VARCHAR2(20),
    COURSE_ID NUMBER,
    TEACHER_ID NUMBER,
    CONSTRAINT PK_SCHEDULE_BAD PRIMARY KEY (STUDENT_ID, TIME_SLOT)
);
-- 问题：(COURSE_ID, TIME_SLOT) -> TEACHER_ID，但COURSE_ID不是主键
-- 符合BCNF的设计
CREATE TABLE COURSE_TIME_TEACHER (
    COURSE_ID NUMBER,
    TIME_SLOT VARCHAR2(20),
    TEACHER_ID NUMBER,
    CONSTRAINT PK_CTT PRIMARY KEY (COURSE_ID, TIME_SLOT)
);
CREATE TABLE STUDENT_SCHEDULE (
    STUDENT_ID NUMBER,
    TIME_SLOT VARCHAR2(20),
    COURSE_ID NUMBER,
    CONSTRAINT PK_SS PRIMARY KEY (STUDENT_ID, TIME_SLOT),
    CONSTRAINT FK_SS_CTT FOREIGN KEY (COURSE_ID, TIME_SLOT)
        REFERENCES COURSE_TIME_TEACHER (COURSE_ID, TIME_SLOT)
);

当处理表间的关联约束所付出的代价（常常是使用性上的代价）超过了保证不会出现修改、删除、更改异常所付出的代价，并且数据冗余也不是主要的问题时，表设计可以不符合四个范式。四个范式确保了不会出现异常，但也可能由此导致过于纯洁的设计，使得表结构难于使用，所以在设计时需要进行综合判断，但首先确保符合四个范式，然后再进行精化修正是刚刚进入数据库设计领域时可以采用的最好办法。

1.2 根据实际需要进行反规范化设计

数据库规范化设计目前占数据库设计主导地位，规范化设计达到了数据存储空间最佳利用和控制数据冗余，但是也带来了查询性能的降低，因此在适当的条件下，对数据库进行反规范化设计也是必要的。

反规范化设计通过增加数据冗余，数据分片等策略来改进原有规范设计，以达到改善数据库性能的目的。

反规范化设计的几个原则：

1）确信对系统的逻辑设计有全面的了解

使系统设计人员清楚，当改变数据库设计时，会对其它部分造成哪些影响。

2）尽量采取索引、数据存储等技术来提升数据库性能

分析系统的数据量，对于数据量不大，响应时间不高的尽量通过数据库本身技术来提升性能。

3）数据完整性维护

使用反规范带来最大的问题使数据完整性，在程序设计中充分考虑到数据库事务的处理，增删改查保证数据库操作一致性；完整性约束还可以通过批处理及触发器实现（不建议）。

1.3 反规范化设计的几个方法

1）增加冗余列

在表中增加经常需要访问而频繁访问其它表的列，导致多个表有相同的列，即冗余列。常用在两个地方：（1）关系表中的冗余：在关系表中增加相关实体表的相关属性，以达到关连查询时减少表的关联数量的目的（2）层次关系中的冗余：在多层次的子父表关系中，将父表的属性存储在“子表”或者“孙子表”或者“重孙表”中。

-- 标准设计（符合3NF）
CREATE TABLE DEPARTMENT (
    DEPT_ID NUMBER PRIMARY KEY,
    DEPT_NAME VARCHAR2(50)
);
CREATE TABLE EMPLOYEE (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(100),
    DEPT_ID NUMBER,
    CONSTRAINT FK_EMP_DEPT FOREIGN KEY (DEPT_ID)
        REFERENCES DEPARTMENT (DEPT_ID)
);
-- 反规范化设计（增加冗余列提高查询性能）
CREATE TABLE EMPLOYEE_DENORM (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(100),
    DEPT_ID NUMBER,
    DEPT_NAME VARCHAR2(50), -- 冗余列
    CONSTRAINT FK_EMP_DENORM_DEPT FOREIGN KEY (DEPT_ID)
        REFERENCES DEPARTMENT (DEPT_ID)
);
-- 维护冗余数据的触发器（Oracle具体语法）
CREATE OR REPLACE TRIGGER TRG_DEPT_UPDATE
AFTER UPDATE OF DEPT_NAME ON DEPARTMENT
FOR EACH ROW
BEGIN
    UPDATE EMPLOYEE_DENORM
    SET DEPT_NAME = :NEW.DEPT_NAME
    WHERE DEPT_ID = :NEW.DEPT_ID;
END;
/

2）数据分片主要有三种方式：水平分片，垂直分片，混合分片

水平分片：是指按一定规则，将数据库所有行划分成若干不相交的子集，即多个表。在数据库比较庞大的情况下，通常在以下几个情况下使用：一是数据访问频率差别极大时候，可以将经常访问的数据放在一个表中，不常访问的放在另外一个表，比如历史表；一个是数据表中数据本身有独立性，如状态表中状态字段。

 -- 水平分片示例：将订单表按年份分片
CREATE TABLE ORDERS_2023 (
    ORDER_ID NUMBER PRIMARY KEY,
    CUSTOMER_ID NUMBER NOT NULL,
    ORDER_DATE DATE NOT NULL,
    AMOUNT NUMBER(12,2),
    CONSTRAINT CHK_DATE_2023 CHECK (ORDER_DATE BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD'))
);
CREATE TABLE ORDERS_2024 (
    ORDER_ID NUMBER PRIMARY KEY,
    CUSTOMER_ID NUMBER NOT NULL,
    ORDER_DATE DATE NOT NULL,
    AMOUNT NUMBER(12,2),
    CONSTRAINT CHK_DATE_2024 CHECK (ORDER_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD'))
);
-- 创建视图统一访问
CREATE OR REPLACE VIEW ORDERS_ALL AS
  SELECT * FROM ORDERS_2023
  UNION ALL
  SELECT * FROM ORDERS_2024;

垂直分片：是指把一个数据库属性集分成若干子集，在每个子集上做投影运算，每个投影称为垂直分片。比如可以将一个表中主键和经常访问的列放在一个表中，主键和不常访问的放在另一表中。优点是查询是可以直接查找经常访问的表，减少了IO操作，缺点是查询所有数据时需要多表连接。

-- 垂直分片示例：将用户表拆分为基本信息表和详细信息表
-- 原表
CREATE TABLE USER_ORIGINAL (
    USER_ID NUMBER PRIMARY KEY,
    USERNAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(100) NOT NULL,
    PHONE VARCHAR2(20),
    ADDRESS VARCHAR2(200),
    PROFILE_TEXT CLOB,
    PREFERENCES VARCHAR2(4000)
);
-- 垂直分片后
CREATE TABLE USER_BASIC (
    USER_ID NUMBER PRIMARY KEY,
    USERNAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(100) NOT NULL,
    PHONE VARCHAR2(20)
);
CREATE TABLE USER_DETAIL (
    USER_ID NUMBER PRIMARY KEY,
    ADDRESS VARCHAR2(200),
    PROFILE_TEXT CLOB,
    PREFERENCES VARCHAR2(4000),
    CONSTRAINT FK_USER_DETAIL FOREIGN KEY (USER_ID)
        REFERENCES USER_BASIC (USER_ID)
);

混合分片：实际应用中，根据需要将水平分片及垂直分片结合使用的做法。

-- 混合分片示例：先垂直分片，再水平分片
-- 1. 垂直分片：将订单表分为基本信息和详细信息
CREATE TABLE ORDER_BASIC (
    ORDER_ID NUMBER PRIMARY KEY,
    CUSTOMER_ID NUMBER NOT NULL,
    ORDER_DATE DATE NOT NULL,
    STATUS VARCHAR2(20) NOT NULL
);
CREATE TABLE ORDER_DETAIL (
    ORDER_ID NUMBER PRIMARY KEY,
    SHIPPING_ADDRESS VARCHAR2(200),
    PAYMENT_METHOD VARCHAR2(50),
    NOTES CLOB,
    CONSTRAINT FK_ORDER_DETAIL FOREIGN KEY (ORDER_ID)
        REFERENCES ORDER_BASIC (ORDER_ID)
);
-- 2. 水平分片：将基本信息表按年份分片
CREATE TABLE ORDER_BASIC_2023 (
    ORDER_ID NUMBER PRIMARY KEY,
    CUSTOMER_ID NUMBER NOT NULL,
    ORDER_DATE DATE NOT NULL,
    STATUS VARCHAR2(20) NOT NULL,
    CONSTRAINT CHK_DATE_BASIC_2023 CHECK (ORDER_DATE BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD'))
);
CREATE TABLE ORDER_BASIC_2024 (
    ORDER_ID NUMBER PRIMARY KEY,
    CUSTOMER_ID NUMBER NOT NULL,
    ORDER_DATE DATE NOT NULL,
    STATUS VARCHAR2(20) NOT NULL,
    CONSTRAINT CHK_DATE_BASIC_2024 CHECK (ORDER_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD'))
);
-- 创建视图
CREATE OR REPLACE VIEW ORDER_BASIC_ALL AS
  SELECT * FROM ORDER_BASIC_2023
  UNION ALL
  SELECT * FROM ORDER_BASIC_2024;

在这里插入图片描述

通过遵循表规范设计，可以在保证数据完整性的同时获得更好的性能。在下一章节中，我们将详细介绍物理设计规范，包括命名、主键、索引等详细要求。

未完待续...

Oracle数据库设计规范篇{二}——物理设计规范【上】（基本规范、表设计规范）

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

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

1.1 逻辑设计方法及规范

1.1.1采用ER模型进行逻辑设计

1.1.2 首先遵循范式设计

1.2 根据实际需要进行反规范化设计

1.3 反规范化设计的几个方法

1）增加冗余列

2）数据分片主要有三种方式：水平分片，垂直分片，混合分片

Oracle数据库设计规范篇{二}——物理设计规范【上】（基本规范、表设计规范）

最新文章

ITPub博客

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

1.1 逻辑设计方法及规范

1.1.1采用ER模型进行逻辑设计

1.1.2 首先遵循范式设计

1.2 根据实际需要进行反规范化设计

1.3 反规范化设计的几个方法

1）增加冗余列

2）数据分片 主要有三种方式：水平分片，垂直分片，混合分片

Oracle数据库设计规范篇{二}——物理设计规范【上】（基本规范、表设计规范）

最新文章

2）数据分片主要有三种方式：水平分片，垂直分片，混合分片