Design7：數據刪除設計

在設計一個新系統的Table Schema的時候，不僅需要滿足業務邏輯的複雜需求，而且需要考慮如何設計schema能夠更快的增加數據和刪除數據。

模擬一個場景：

Product(ID,Name,Description)

ID是自增的ID欄位（Identity），用以唯一標識一個Product，在業務邏輯上要求以Name欄位是唯一，通過Name能夠確定一個Product。業務上和設計上有所衝突在所難免，解決衝突的方法其實很簡單：將ID做主鍵，並創建聚集index；在name上創建唯一約束，保證name欄位是唯一的。

如果業務人員操作失誤，將Product 的 Name 寫錯，需要將其刪除，最簡單的方式是使用delete 命令，直接將數據行刪除，但是這種方式帶來的隱患特別大：如果業務人員一不小心將重要的數據刪除，那麼，恢複數據的成本可能非常高，如果資料庫很大，僅僅為恢復一條數據，可能需要N個小時執行還原操作。如何設計Table Schema，以避免在維護系統上出現被動的情況？

delete Product
where Name='xxx'

設計目的：在短時間內恢復被誤刪除的數據，以使系統儘快恢復

數據刪除操作有兩種方式：軟刪除和硬刪除，也稱作Logic Delete 和 Physical Delete。硬刪除是指使用delete命令，從table中直接刪除數據行；軟刪除是在Table Schema中增加一個bit類型的column：IsDeleted，預設值是0，設置IsDeleted=1，表示該數據行在邏輯上是已刪除的。

Product(ID,Name,Content,IsDeleted,DeletedBy)

軟刪除實際上是一個Update 操作，將IsDeleted欄位更新為1，邏輯上將數據刪除，並沒有將數據行從物理上刪除。使用軟刪除，能夠保留有限的數據刪除的歷史記錄，以便audit，但是，這可能導致外鍵關係引用被邏輯刪除的數據；如果歷史記錄太多，這又會導致數據表中有效數據行的密度降低，降低查詢速度。

1，能夠快速恢復被誤刪除的數據

用戶的刪除操作是將IsDeleted設置為1，在邏輯上表示刪除數據，如果用戶由於誤操作，將重要數據行刪除，那麼只需要將IsDeleted重置為0，就能恢複數據。

update Product
set IsDeleted=1
where Name='xxx'  -- or  use ID=yyyy as filter

2，每次引用該表時，必須設置filter

任何引用該表的查詢語句中，必須設置Filter：IsDeleted=0，為來避免遺漏filter，可以創建視圖，不直接引用該表，而是直接引用視圖。

--view definition
select ID,Name,Content
from Product
where IsDeleted=0

3，手動處理外鍵關係

如果在該表上創建外鍵關係，那麼可能存在外鍵關係引用被邏輯刪除的數據，造成數據的不一致性，這可能是很難發現的bug：如果需要保持關鍵關係的一致性，需要做特殊的處理。在將數據行邏輯刪除之時，必須在一個事務中，將外鍵關係全部刪除。

4，不能被用作歷史表

數據表是用來存儲數據的，不是用來用戶操作的歷史記錄。如果需要存儲用戶操作的歷史記錄，必須使用另外一個HistoryOperation來存儲。

上述Product表中Name欄位上存在一個唯一約束，如果用戶將相同Name的Product重新插入到table中，Insert 操作因為違反唯一約束而失敗，針對這種情況，軟刪除操作必須額外進行一次判斷：

if exists(
    select null 
    from Product 
    where name ='xxx' and IsDeleted=1
)
update 
    set IsDeleted=0,
        ...
from Product 
where name ='xxx' and IsDeleted=1
else 
insert Product(...) 
values(....)

如果Product表的數據量十分大，額外的查詢操作，會增加插入操作的延遲，同時，也會降低數據查詢的速度。
5，將刪除的數據存儲到History表

使用軟刪除設計，增加IsDelete=1 欄位，實際上降低了有效數據的密度，在使用軟刪除時，必須慎重考慮這一點。改進的刪除數據的設計是：在一個事務中，將刪除的數據存儲到另外一個History表中。

delete from Product 
output deleted.ID,
    deleted.Name,
    deleted.Content,
    'Delete' as CommandType 
    '' as UpdatedBy,
    getdate() as UpdatedTime
into History_table
where Name ='xxx' -- or use Id=yyy as filter

恢復誤刪的數據，只需要到History表找到相應的數據，將其重新插入到Prodcut 表中，並且，History 表中不僅可以存儲用戶刪除操作的歷史記錄，而且可以存儲用戶更新的歷史記錄，對於系統的維護，解決用戶糾紛和故障排除，十分有幫助。

Product(ID,Name,Content)
OperationHistory(ID,ProductID,ProductName,ProductContent,CommandType,UpdatedBy,UpdatedTime)

為設計Product 表的刪除操作，需要兩個Table，對於OperationHistory表，可以做的更通用一些。拋磚引玉，提供一個思路，我就不做擴展了。