数据库再设计(Database Redesign)-MySQL-Tutorial-php.cn

数据库设计有三个来源：（1）可以从现有数据开始设计数据库，例如从excel表等，这种模式下需要考虑的问题是数据的normalization，最终通常将数据转化为BCNF范式；（2）设计新的数据库，这种模式需要从构建E-R图开始一步步构造完整的数据库；（3）数据库再设

数据库设计有三个来源：（1）可以从现有数据开始设计数据库，例如从excel表格等，这种模式下需要考虑的问题是数据的normalization，最终通常将数据转化为BCNF范式；（2）设计新的数据库，这种模式需要从构建E-R图开始一步步构造完整的数据库；（3）数据库再设计，这种模式需要修改现有数据库，使其满足新的数据或者性能要求。本博客介绍第三种数据库设计模式。此外，为了介绍数据库再设计，我们需要有一个现成的数据库。我们将以一个艺术品拍卖会的数据库为例介绍，图一描述了其数据库。

数据库再设计(Database Redesign)

1. 数据库再设计目的

一般情况下，如果我们按照标准的模式从前两种来源设计数据库，最终的结果都是满足相关要求的，为什么还需要重新设计数据库？该问题的解答包含两方面：首先，正确地设计数据库其实非常难，尤其是从无到有的全新设计。即使我们获得了完整的用户需求，并创建了一个正确的数据模型，将模型转化为实际数据库依旧非常难，尤其模型非常复杂时。更重要的一方面是，用户的需求一直在变化，这直接导致数据库的不停变更。因而数据库再设计是数据库设计和维护中很重要的一部分，再设计过程会存在于数据库的整个生命周期。

2. 准备工作

在修改数据库结构之前，我们需要做一些准备工作。首要的任务是熟悉当前数据库的结构和存储的数据，以了解数据库中各结构的相互依赖关系。其次，在修改实际数据库之前，我们需要在相当数据量的测试数据库上进行测试，只有在测试数据库上保证正确之后才能实际修改可操作数据库（operational database）。最后，一定要备份可操作数据库。

3. 依赖图

在修改数据库结构之前，我们还需要构造一个依赖图（dependency graph），用来描述不同数据库结构之间的依赖关系。当我们在修改数据库的不同结构时，会影响其他结构，通过依赖图就可以很清楚地看出修改一个结构会影响哪些结构。图二描述了图一数据库中不同结构的依赖图。

数据库再设计(Database Redesign)

假设我们现在需要修改artist表，则依赖于它的表work，触发器Trans_checkSalesPrice和视图ArtistWorkNetView也需要修改。根据实际情况，修改表work可能还会递归导致依赖于它的结构也需要修改。可以看出数据库再设计是一个相当复杂的过程。不过，通常情况下的修改只会导致子节点的变动，不太可能修改子子节点或者后续子节点。

数据库再设计通常包含三个方面的修改：表或关系、列和基数，下面详细介绍不同的修改。此外，本博客涉及的数据库再设计仅限于数据库数据的范围没有大到不能复制的程度。

4. 数据库表的修改

4.1表名的修改

单纯的表名修改没有想象中那么简单，会导致一系列结构的修改。例如，假设我们现在要将work表名改为WORK_VERSION2，我们需要执行下列步骤：

1) 利用CREATE TABLE WORK_VERSION2语句创建新的表，在work中workID是一个surrogate key，在WORK_VERSION2中暂时不将该主键设为surrogate key。同时将work表中的constraint复制到新表中，注意修改constraint的名字，否则会提示冲突。

2) 将work表中的数据利用insert into语句添加到新表中，添加语句包含work所有的列。

3) 修改WORK_VERSION2表中workID为surrogate key。修改方式在不同的数据库产品上不同。

4) 修改触发器Trans_checkSalesPrice和视图ArtistWorkNetView，主要是将旧表名work改为新表名WORK_VERSION2。

5) 删除旧表work。删除时需要先将表trans中的外键约束删除，然后再删除work。

6) 在trans表上添加新的外键约束使其指向新表WORK_VERSION2的workID。

4.2 添加新表或关系

添加新表或者关系通常没有什么难点，直接利用create table创建新表并附加各种constraint即可。唯一的一点例外是，如果创建的新表是某个现存表的父节点（新表和现存表有1：N的最大基数关系），我们需要在现存表中添加外键约束。

4.3 删除表或关系

删除表通常遵循这样一个原则：首先删除依赖该表主键的外键约束，然后删除该表。当然，在删除过程中，视图和触发器也应该根据依赖图进行相应的修改或删除。

5. 数据库表中列的修改

列的修改本质上还是属于数据库表的修改，但是由于其本身含有较多内容，单独拿出来介绍。

5.1 列名的修改

如果修改的是一个nonkey形式的列，通常不会带来什么影响，但是还是需要检查是否有触发器或者视图依赖于该列，如果有也需要修改相应的列名。如果修改的是主键，除了检查视图和触发器，我们还需要将对应的外键名字也修改掉。

5.2 添加列

添加可以null的列直接采用alter table add column语句即可。在数据库运行的过程中，我们可以随时修改该列的属性，比如设置default值等。但是设置的default值只会存在于后续添加的行中，先前添加的行值可能还是null。

初次添加一个not null的列也是直接采用alter table add column语句。但是我们不能将其设置为not null，因为表中已经存在很多行，直接设置not null会报错。然后更新所有的行使其值为not null，然后再利用SQL语句ALTER TABLE ALTER COLUMN设置列属性为not null。

5.3 删除列

nonkey的列直接删除一般不会存在问题，但是还是需要注意视图和触发器。如果删除的是外键，则需要先删除外键对应的constraint，然后再删除外键对应的列。

当删除主键时，情况会复杂很多。例如我们要删除work表的主键，并替换为新的主键，则需要执行下列步骤：

1) 首先删除表trans中的外键约束WorkFK；

2) 其次删除表work中的主键约束WorkPK；

3) 利用列(Title,Copy, ArtistID)创建新的主键约束WorkPK；

4) 在trans表中创建依赖于work中列(Title, Copy, ArtistID)的外键约束WorkFK；

5) 删除列workID。

5.4 修改列的数据类型或者约束

可以用命令ALTER TABLE ALTER COLUMN命令修改列的属性。如果将列由null变为not null，则必须确保所有的列都为not null。不同数据类型之间的转换可能导致数据丢失。但是通常情况下，将数字类型、时间、货币类型转换为char或者varchar都没有问题，反过来则可能不被DBMS允许。

添加删除constraint通过ALTER TABLE ADD CONSTRAINT 和ALTER TABLE DROP CONSTRAINT语句完成。

6. 修改关系的基数（Cardinalities）

6.1 修改最小基数

parent端

修改parent端的最小基数，也就是让child表有或者没有parent，也即判断child中外键是否为null。例如，针对1:N的关系DEPARTMENT to EMPLOYEE，外键DepartmentNumber出现在employee中。修改employee是否需要指定department也即修改DepartmentNumber的null状态。

如果将最小基数从0改为1，就需要将外键设置为not null，这时需要确保目前存在的数据外键都为not null，否则我们需要先更新数据。根据不同的DBMS产品，修改外键的方式也不一样，但是可以遵循下面的步骤：

1) 删除旧的外键约束；

2) 设置外键列为not null；

3) 添加新的外键约束。

上面的例子修改最小基数的SQL语句如下：

ALTER TABLE EMPLOYEE
    DROP CONSTRAINT DepartmentFK;
ALTER TABLE EMPLOYEE
    ALTER COLUMN DepartmentNumber Int NOT NULL;
ALTER TABLE EMPLOYEE
    ADD CONSTRAINT DepartmentFK FOREIGN KEY (DepartmentNumber)
        REFERENCES DEPARTMENT (DepartmentNumber)    
	    ON UPDATE CASCADE;

Nach dem Login kopieren

级联属性需要根据具体的业务来确定。

child端

在child端设置最小基数非零需要利用触发器或者application code，因而将最小基数从零变为一也需要利用触发器。如果将最小基数从1变为0，直接删除触发器即可。

6.2 增大最大基数

1:1 to 1:N

假设在EMPLOYEE 和PARKING_PERMIT之间存在如图三所示的1:1关系，现在默认employee是parent，因为外键在parking_permit中。当将1:1的关系变为1:N的关系时，需要考虑哪端是parent端（为1）。假如employee还是parent端，则只需要将parking_permit中的外键unique属性去掉即可。如果parking_permit变为parent端，则我们需要将外键移到employee中，还需要数据导入employee表中。具体的操作如下：

1) 在employee中添加可以为null的外键PermitNumber；

2) 将parking_permit中的数据导入employee表中：

UPDATE EMPLOYEE
    SET EMPLOYEE.PermitNumber =
        (SELECT  PP.PermitNumber
         FROM  PARKING_PERMIT AS PP
         WHERE  PP.EmployeeNumber = EMPLOYEE.EmployeeNumber);

Nach dem Login kopieren

3) 将parking_permit中的外键EmployeeNumber删除；

4) 在employee中添加外键约束；

5) 修改相应的触发器和视图。

数据库再设计(Database Redesign)

1:N to N:M

将1:N的关系变为N:M的关系比较容易，只需要：创建一个只包含两个表主键的新表（interdiv table），然后将两个主键的组合作为新表的主键，每一个分别是依赖于另外每一个表的外键；从child表导入数据，然后删除child中的外键约束。最后修改触发器和视图使其应用新表。

6.3 减小最大基数（存在数据丢失）

将一个N:M的关系降为一个1:N的关系，我们需要在child表中添加一个外键，然后从interdiv table中导入外键的数据。修改相应的触发器和视图等，最后将interdiv table删除。将一个1:N的关系降为1:1的关系，我们需要将child表中的外键数据变为1，然后将外键约束变为unique。这两种情况都需要决定如何删除数据。