化繁為簡：01 資料庫篇

更新於 分類於

img

前言

近年來在軟體開發領域可以說是必修課程越來越多，只會寫程式的人(Coder)似乎不能再稱作程式設計師(Programmer)，一些比較有制度的公司也會透過教育訓練來提升軟體品質，但是就專案類型的公司似乎很難進化，因為不論是新技術或是新架構的導入都會有很多問題要突破，例如：人員流動率、專案交期，有些專案甚至是接案後才開始找人，所以很多新東西都變成遙不可及的理想甚至是幻想，今天筆者分享一些委屈求全的經驗。

資料庫

筆者這幾年除非客戶指定，否則資料庫幾乎都使用 PostgreSQL，它在DB-Engines穩居第四，最重要的是免費的，而且功能十分齊全，對於經費有限的專案來說是不錯的選擇。
img

接下來我們針對資料庫的表單設計做出一些規範：

  • 主鍵(Primary Key)到底是要用 數值 還是 UUID？
    數值型別擁有較好的搜尋效能，但是如果需要資料移轉或是合併時主鍵重複的問題會讓人非常痛苦，UUID 在資料移轉上有著很大的方便，因為要發生重複機率很低，但是它的可讀性可以說是零，所以最簡單的方式就是兩者都並存，筆者會將 id 設為數值型別並設為主鍵，另外增設一個 uid 型別為 UUID，重點記得加入索引(Index)來增加搜尋效能，程式在查詢時會以 uid 當作條件來查詢。
    img

    以往筆者 id 都會使用 integer(4 bytes)，但是少數的資料表的筆數在未來有機會超出，所以一勞永逸的辦法就是統一使用 bigint(8 bytes)，規則越少學習或移轉成本才會越低。
    img
    當然有些客戶會自訂PK欄位規則(例如年、月、日加流水號)，這邊筆者建議就增設欄位來紀錄即可，把它當作一個普通欄位來對待，甚至不一定要設定唯一性(Unique Key)，如果保資料庫只能透過後端程式來存取，我們只要在後端程式新增及修改時去檢核是否有重複，因為客戶制定的編碼規則一般不會太長，最容易發生的就是流水號不足，所以真的拿客戶訂的PK當資料表的PK，無疑是放置一顆未爆彈。

  • 要不要加關聯性(Relation)？
    大部分的表單其實都是 Master-Detail 架構，主表下面會有多筆明細資料，所以加上關聯性似乎是必要動作，資料庫內的資料本來就應該要很嚴謹，但是筆者已經好幾年都沒有在資料表內加入過關聯性了，因為現在系統開發過程資料表的設計都是程式設計師一起包辦，所以欄位異動一般會很頻繁，關聯性很容易在開發階段造成測試困擾，真的有需要最後再加吧！(當然補上關聯性代表還要再花時間測試)。

  • 誤刪資料怎麼辦？
    一般系統的資料處理作業在儲存錢幾乎都會有一些再次確認的動作，最常見的就是彈出確認對話方塊，但是對於已經習慣系統操作的人來說其實效用不大，機會都會連續點過去，所以最可怕的是使用者誤刪資料而且還無法重建。
    img
    因此最好的辦法就是不要刪除資料，客戶所謂的刪除資料，其實就是系統上不要再看到這一筆資料，筆者便在資料表上增加一個布林值欄位 disable，當它為 true 時表示被刪除，所以後須所有查詢都必須增加 disable 為 false 的條件。
    img
    此外還增加 created_by、created_date、removed_by、removed_date 欄位來紀錄修改以及刪除人員與時間，u.有些資料庫會有 modified_by、modified_date 表示異動人員，但是筆者認為如果真的需要歷史紀錄，那就不單單只需要知道是誰修改資料就好，而是應該包含資料異動內容，這時候比較方便的做法就是建立一個結構相似的資料表(因為要記錄修改歷程，所以同一筆資料可能會在歷史紀錄表內有多筆，亦即原始PK會重複)，每次資料新增或是修改時就寫一份到歷史記料表內，這樣就可以查詢完整的異動歷程，所以筆者的 created_by、created_date 欄位是代表最後異動人員與時間。

    好吧！其實是修改成 modified_by、modified_date 的工程比較費時，所以…懶。

    如果你後端跟筆者一樣透過 .NET 開發並起資料庫透過 Entity Framework 處理，那我們可以透過複寫資料庫模型(DbContext)的 OnModelCreating 幫我們自動增加 disable 為 true，這樣我們查詢時就不需要再特別加入條件。 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        foreach (var entityType in modelBuilder.Model.GetEntityTypes()
                    .Where(e => typeof(IRowDisable).IsAssignableFrom(e.ClrType)))
        {
            modelBuilder.Entity(entityType.ClrType).Property<bool>("Disable");
            var parameter = Expression.Parameter(entityType.ClrType, "e");
            var body = Expression.Equal(
                        Expression.Call(typeof(EF), nameof(EF.Property), new[] { typeof(bool) }, parameter, Expression.Constant("Disable")),
                        Expression.Constant(false));
            modelBuilder.Entity(entityType.ClrType).HasQueryFilter(Expression.Lambda(body, parameter));
        }
    }

  • 是否允許空值(Null)？
    Null 最大的影響就是會把程式的運算式搞掛，大部分的運算子都無法處理 null 值，所以欄位允許 null 就意味著程式在處理資料前需要先判斷是否為 null，在座對應的處理，所以省工的方法就是欄位不允許 null 並給定預設值，這樣可以減少程式碼的判斷式，同時也提高程式可讀性(邏輯越少越容易理解)，當然有些欄位可能無法避免需要允許 null，例如筆者建立的 removed_date 欄位表示刪除時間，因此在資料移除前會用 null 表示，這部分可以透過規範或說明來提醒開發人員。
    img

    筆者過去會使用 1900/01/01 來表示未給值的日期，然後在程式內將年(Year)小於 2000 的日期清除，不過這等於是特殊處理，淺規則越多維護也越不方便。

  • 重要欄位避開常用詞彙
    不得不說這是筆者的偏見，這一樣是資料整合問題，移轉舊資料或是匯入外部資料似乎都是系統導入無可避免的步驟，整合過程有些欄位需要加工處理，例如編號規則改變，我們需要將既有資料的編號轉換成新的編碼規則再存入系統，比較保險的做法就是會加開欄位來儲存原始編號，這時候會有同性質的欄位命名問題，以往我們會以新系統為主，將舊資料的欄位改成其他別名，但是系統資料介接是很常遇到的狀況，當就資料庫被新資料庫替代之後，相關的介接程式，這時候容易會發生水土不服的狀況，那些介接程式所對應的系統無法配合新的規則，額外的討論協調就無法避免，所以有些常用欄位筆者會用冷門的名稱命名，例如用 code 表示編(單)號，notes 表示備註欄，用 removed 來做刪除紀錄。

    img

    過往經驗很多資料介接的小程式都是免費的，驗收前工程師被”拗”著做，既然是不用錢的，所以對資料庫處理的所有 SQL 語法幾乎都會直接寫死在程式內，而這種小程式最容易發生無法修改的問題，要嘛廠商報天價連本帶利的要回來，要嘛程式碼找不到。

  • 是否要用預存程序(Stored Procedure)？
    用與不用都有人支持，因為各有利弊，筆者則是站在不建議使用的立場，因為使用預存程序也代表你的部分資料處理邏輯被切割出來，它會變成一個黑盒子，相對地，修改預存程序當下很難知道會影響多少系統，如果沒有相關文件或說明記錄，有時候就會建立一個新的預存程序，讓資料庫存在多個版本。
    現在也有一些程式設計師沒有學過資料庫的 SQL 語法而是直接透過 ORM 技術來做資料交換，比較成熟的 ORM 技術甚至當你資料庫遷移到另一套資料庫系統上時，它可以讓你僅需調整部分設定就可以無痛(理論上)移轉，而預存程序的移轉就不見得能夠直接相容，你可能需要透過一些軟體工具甚至手動調整。

備份、還原

“不怕一萬、就怕萬一”，以往筆者其實都沒有在意這件事情，知道資料庫有備份還原功能，但是從來都沒有去玩過，心中都是抱著出問題時在求助於 Google 大神，但是現在這種網路發達時代，惡意的駭客入侵、勒索病毒事件層出不窮，所以如何快速還原系統就變得很重要，因為這可能會是攸關公司營運的問題。
所以對於自己系統所使用的資料庫，應該至少把備份還原的操作方式或是指令記錄在專案的說明文件內，筆者每天進辦公室第一件事就是備份資料庫，並把備份檔複製一份到 NAS 上。
img

PostgreSQL 也可透過視覺化工具 pgAdmin 來執行備份與還原，有興趣的可以參考上一篇：PostgreSQL 安裝、備份、還原。

後記

不知道現在還有多少人聽過資料庫管理師(Database Administrator, 簡稱DBA)，感覺起來大概只剩金融業、物流業或是電商平台這類資料增長快速的領域會有專屬的 DBA 之外，很多都是 MIS 兼任，出問題負責通知廠商，因為資料庫也都是由承接專案的廠商自行規劃，而大部分的開發商都是由負責的程式設計師自行發揮，也就導致現在開發又區分為 Code First 與 Database First，因此會使用資料庫進階功能的機會越來越少(使用的越多代表可以接手的人也就越少)，對不少程式設計師來說資料庫跟 Excel 差不多，僅是用來存放資料而已。

如果你有著新鮮的肝，那就盡力讓好還要更好，努力朝理想方案前進，因為任何的偷工減料都是有相應的代價。如果需要帶領參差不齊的團隊完成專案，那麼你往往只能從一堆不好的方案中挑選可行方案，所以看開一點，換個角度想想，你的可行方案已經幫助客戶向前一小步了，雖然距離理想還很遙遠，但是至少不是停留在原地，據說，賈伯斯一開始是想要推出 iPad 的，只是因為當時的觸控技術無法符合預期，在無奈之下只能妥協先推出小尺寸的 iPhone，如今觸控技術已經十分成熟了，但是 iPhone 依然是 Apple 最大的營收來源，反過來說，如果當時強制推出 iPad，那 Apple 一樣有辦法跟現在一樣強大嗎？沒人能保證吧！