資料庫再設計(Database Redesign)

資料庫設計有三個來源：（1）可以從現有資料開始設計資料庫，例如從excel表格等，這種模式下需要考慮的問題是資料的normalization，最終通常將資料轉化為BCNF範式；（2）設計新的資料庫，這種模式需要從構建E-R圖開始一步步構造完整的資料庫；（3）資料庫再設計，這種模式需要修改現有資料庫，使其滿足新的資料或者效能要求。本部落格介紹第三種資料庫設計模式。此外，為了介紹資料庫再設計，我們需要有一個現成的資料庫。我們將以一個藝術品拍賣會的資料庫為例介紹，圖一描述了其資料庫。

1.   資料庫再設計目的

       一般情況下，如果我們按照標準的模式從前兩種來源設計資料庫，最終的結果都是滿足相關要求的，為什麼還需要重新設計資料庫？該問題的解答包含兩方面：首先，正確地設計資料庫其實非常難，尤其是從無到有的全新設計。即使我們獲得了完整的使用者需求，並建立了一個正確的資料模型，將模型轉化為實際資料庫依舊非常難，尤其模型非常複雜時。更重要的一方面是，使用者的需求一直在變化，這直接導致資料庫的不停變更。因而資料庫再設計是資料庫設計和維護中很重要的一部分，再設計過程會存在於資料庫的整個生命週期。

2.   準備工作

在修改資料庫結構之前，我們需要做一些準備工作。首要的任務是熟悉當前資料庫的結構和儲存的資料，以瞭解資料庫中各結構的相互依賴關係。其次，在修改實際資料庫之前，我們需要在相當資料量的測試資料庫上進行測試，只有在測試資料庫上保證正確之後才能實際修改可操作資料庫（operational database）。最後，一定要備份可操作資料庫。

3.   依賴圖

在修改資料庫結構之前，我們還需要構造一個依賴圖（dependency graph），用來描述不同資料庫結構之間的依賴關係。當我們在修改資料庫的不同結構時，會影響其他結構，通過依賴圖就可以很清楚地看出修改一個結構會影響哪些結構。圖二描述了圖一資料庫中不同結構的依賴圖。

假設我們現在需要修改artist表，則依賴於它的表work，觸發器Trans_checkSalesPrice和視圖ArtistWorkNetView也需要修改。根據實際情況，修改表work可能還會遞迴導致依賴於它的結構也需要修改。可以看出資料庫再設計是一個相當複雜的過程。不過，通常情況下的修改只會導致子節點的變動，不太可能修改子子節點或者後續子節點。

資料庫再設計通常包含三個方面的修改：表或關係、列和基數，下面詳細介紹不同的修改。此外，本部落格涉及的資料庫再設計僅限於資料庫資料的範圍沒有大到不能複製的程度。

1.   資料庫表的修改4.1表名的修改

單純的表名修改沒有想象中那麼簡單，會導致一系列結構的修改。例如，假設我們現在要將work表名改為WORK_VERSION2，我們需要執行下列步驟：

1)       利用CREATE TABLEWORK_VERSION2語句建立新的表，在work中workID是一個surrogate key，在WORK_VERSION2中暫時不將該主鍵設為surrogate key。同時將work表中的constraint複製到新表中，注意修改constraint的名字，否則會提示衝突。

2)       將work表中的資料利用insert into語句添加到新表中，添加語句包含work所有的列。

3)       修改WORK_VERSION2表中workID為surrogatekey。修改方式在不同的資料庫產品上不同。

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 刪除表或關係

       刪除表通常遵循這樣一個原則：首先刪除依賴該表主鍵的外鍵約束，然後刪除該表。當然，在刪除過程中，視圖和觸發器也應該根據依賴圖進行相應的修改或刪除。

2.   資料庫表中列的修改

列的修改本質上還是屬於資料庫表的修改，但是由於其本身含有較多內容，單獨拿出來介紹。

5.1 列名的修改

如果修改的是一個nonkey形式的列，通常不會帶來什麼影響，但是還是需要檢查是否有觸發器或者視圖依賴於該列，如果有也需要修改相應的列名。如果修改的是主鍵，除了檢查視圖和觸發器，我們還需要將對應的外鍵名字也修改掉。

5.2 添加列

添加可以null的列直接採用alter table add column語句即可。在資料庫啟動並執行過程中，我們可以隨時修改該列的屬性，比如設定default值等。但是設定的default值只會存在於後續添加的行中，先前添加的行值可能還是null。

初次添加一個not null的列也是直接採用altertable 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 TABLEADD CONSTRAINT 和ALTER TABLE DROP CONSTRAINT陳述式完成。

3.   修改關係的基數（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;

級聯屬性需要根據具體的業務來確定。

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);

3)       將parking_permit中的外鍵EmployeeNumber刪除；

4)       在employee中添加外鍵約束；

5)       修改相應的觸發器和視圖。

1:N to N:M

將1:N的關係變為N:M的關係比較容易，只需要：建立一個只包含兩個表主鍵的新表（intersection table），然後將兩個主鍵的組合作為新表的主鍵，每一個分別是依賴於另外每一個表的外鍵；從child表匯入資料，然後刪除child中的外鍵約束。最後修改觸發器和視圖使其應用新表。

6.3 減小最大基數（存在資料丟失）

將一個N:M的關係降為一個1:N的關係，我們需要在child表中添加一個外鍵，然後從intersection table中匯入外鍵的資料。修改相應的觸發器和視圖等，最後將intersection table刪除。將一個1:N的關係降為1:1的關係，我們需要將child表中的外鍵資料變為1，然後將外鍵約束變為unique。這兩種情況都需要決定如何刪除資料。