馮火：HIT數據庫表設計實踐

作者：馮火發布時間：2020-04-16

開始今天文章之前，請允許我先回顧前段時間在CHIMA公眾號寫了一篇文章“馮火：如何設計可以運行20年的新一代HIS系統”，我看到有一條留言說我的文章觀點獨到，你可能會好奇是我是怎么想到的？“攜號轉網”是我在別人文章里看到的，本質是解決“用戶被運營商綁架”的問題，當時只是覺得這個了不起，沒有想到它跟我從事的HIT有什么關系。那天聽HIT專家網組織了“新一代醫院信息系統”網課，提到“如何打造20年不做顛覆性改變”，其中又提到廠商需要開放，我突然想到HIT最大問題是“綁架”，本質上“攜號轉網”解決一樣的問題，兩件事情在我腦子里瞬間完成了碰撞，怎么可以做到“攜號轉網”，帶著這個疑問，繼續深思，運營商綁架的是什么，是手機號碼，HIT廠商綁架的是什么，是“醫療數據”，再逐步展開分析，就有了你們看到的文章！

言歸正傳，既然上文中提到了如何設計“數據庫”是關鍵。數據庫設計特指關系型數據庫設計，nosql不是本文所要討論的內容。我們經常遇到HIT表結構設計的亂糟糟（也有少數公司設計精良的），可以用一個詞來形容“隨心所欲”，有命名像天書的、沒有注釋字段都是靠猜、不加相應索引導致系統跑段時間突然變“龜速”、有照搬教科書設計范式等等，數據庫設計好與壞，你我心里都有一桿“秤”。

在今天大數據、人工智能非常火爆的情況下，對數據的質量非常重視，即當下有一個火熱的詞匯“數據治理”，我寫了一篇文章“淺談數據治理”（https://www.jianshu.com/p/ee4850a9a1be），大家有興趣可以去看看。既然數據的源頭基本來自“數據庫”。怎么樣設計出一個合理的數據庫？這是一個很重要的問題，以下是根據我多年經驗試著回答這個問題。

一

不合理的數據庫設計

天書般的命名。你應該見過這樣的命名（AKA01、AKA02、BAA、P1、P2等），軟件開發有句俗話“代碼即文檔”，什么意思，看你的代碼就像看你寫的文檔一樣的易懂。這樣的命名無疑增加大家的使用成本，有人說這樣做是為了數據庫安全，數據庫的安全不是靠用不規范的命名就可以解決？再強調一次，安全問題本質非法訪問未授權的資源！這是人為造成不便利，公司開發效率低、運維效率低。

表結構與注釋分離。字段命名采用拼音縮寫的，字段的注釋沒有在一起描述，很多軟件公司不在數據庫表直接寫注釋，而是單獨用一個txt或者doc來記錄，我個人認為這是一個不好的習慣，我們經常有這樣的困惑，寫sql的時候，突然想知道這個表這個字段是什么意思，第一種方法，靠猜，看它的記錄是什么數據；第二種方法，打開公司提供的數據字典，ctrl+f查找這個字段是什么意思。或許你說這是為了技術保密，保密不是靠不寫注釋就可以保障的！這是人為造成不便利，用戶、公司的開發、運維、實施也不方便，不經意間降低工作效率！

注釋那一列是空白

范式使用場景不對。首先了解范式的是什么，范式是用來解決數據冗余存儲的問題的，范式別級越高，冗余度越小。由于經驗缺乏的關系，業務數據表經常被設計成3NF，導致數據完整性受影響、增加寫sql復雜程度問題。為什么會有范式使用場景不對的，會有什么影響，后面我將舉例說明。

基礎表與業務表設計沒有充分解耦。“解耦”這個詞，大家并不陌生，在數據庫這個層面，個人認為“基礎表與業務表”需要充分解耦。這個怎么理解，表之間關系通常是一對一、一對多、多對多，問題主要出在“一對多”這種關系，我們一般在維護了“外鍵”來表達這種關系。舉例說明，“醫囑表”設計一個來自于“醫生表”外鍵，設計人員本意要表達一種“一對多”關聯關系。在實際使用過程當中會產生什么問題，第一個問題，業務表數據量通常是非常大，勢必增加系統開銷；第二個問題，假設刪除某個醫生，數據庫就會關聯業務表的外鍵報錯，業務表一般認為不可變的數據（人為后臺修改除外），影響用戶體驗。有經驗設計者，一般去掉這個外鍵，然后在注釋當中寫明“外鍵關系”，降低基礎表與業務表耦合。

業務表設計一個來自于基礎表的外鍵

我總結的經驗：1、業務數據表，一般設計冗余存儲，可以不加外鍵，在注釋當中說明這種“一對多”關系；2、基礎數據表，一般設計成3NF，基礎數據表之間可以加“外鍵”，遵循最小代價修改原則，后面我還會提到這個問題，因為十分重要。

關鍵字段沒有索引。這個很好理解，系統跑了一段時間以后，由于沒有對關鍵字段維護索引，系統突然變“龜速”。

二

我認為合理的設計

合理的命名。表、字段的命名，可以采用簡寫拼音、英語單詞、英語單詞縮寫。比方說“患者姓名”字段，可以是xm(簡寫拼音)、patient_name(英語單詞)、pat_name(英語單詞縮寫）。對于英文水平很高優先采用英語單詞、英語單詞縮寫，對于英文差的，可以簡寫拼音，總之不要用“六親不認”的命名。

表結構與注釋放在一起。如果時間一長，公司開發人員經常變動，做不到嚴格的文檔管理，到時候誰都搞不清楚這個字段是什么意思，對于公司來說也是一個風險。現在數據庫管理工具都是圖形化的，要做這件事情不難，我建議表“外鍵”的說明寫入到注釋當中，以便以后分析表與表之間的關系。

表結構與注釋一起

mysql表，外鍵描述

范式合理使用場。 首先分析HIT數據庫的構成，我通常分為2個部分，基礎表（職工基礎表、醫生基礎表、科室基礎表、項目基礎表）、業務表（掛號業務表、發票業務表、醫囑業務表）。基礎表的特點，記錄少，經常增、刪、改、查操作，業務表的特點，記錄多，查詢操作占到90%以上。

對于“業務數據”表結構設計，盡可能接近1NF

對于一些不經常變動基礎的數據進行冗余保存到業務表，比如，保存“發票類別代碼+發票類別名稱”、“醫生工號+醫生姓名”、“科室代碼+科室名稱”、“結算方式+結算方式名稱”等。這樣做的目的，1、盡可能避免多表查詢，考慮效率，因為業務數據是要被經常訪問的，能訪問一張表搞定，就不要訪問兩張表；2、保持歷史數據獨立完整性，比如，某位員工離職了，就把操作權限連同基礎數據一起刪除了，依然不影響歷史數據查詢。我們醫院就有“結算方式”被禁用，導致有些系統無法讀不出來歷史數據了，原因是原來在開發軟件時候，由于沒有冗余存儲，直接關聯了基礎表，導致讀不出來。

對于“基礎字典數據”表結構設計，盡可能接近3NF（盡量最小的修改代價原則）

范式級別越高，信息冗余就越小，但一般到3NF就可以，盡可能符合“盡量最小的修改代價原則”。基礎數據由于記錄少，多表查詢一般不會有執行效率問題，數據冗余越小我們維護越方便，出錯概率就越小，系統穩定性高。

基礎表與業務表設計之間耦合問題，上文已經提及解決方案，在此不再贅述。關鍵字段沒有索引問題，有時候需要專業的DBA來維護，在此不再贅述。

三

回顧理論知識

為了考慮廣大讀者的實際情況，更好理解文中所描述的內容，我還是把重要的理論知識跟大家簡單回顧一下。

第一范式（1NF）指字段是否為復合字段，不可拆分，這個要看業務情況，比如姓名字段，你要不要拆分成“姓”、“名”2個字段。“地址”要不要拆分省市、縣、街道。

第二范式（2NF）在1NF基礎上，非主屬性字段之間消除部分或完全依賴（另一種說法非主屬性完成依賴于主鍵）。舉例說明，非主屬性數量、單價、金額之間存在依賴關系。

部分依賴

第三范式（3NF）在2NF基礎上，非主屬性字段之間消除傳遞依賴。舉例說明，出生日期、年齡、是否成人，存在傳遞依賴。

傳遞依賴

"一對多"關系，一般在“多”的一方，維護一個“外鍵”表達這種關系。

一對多

"多對多"關系，使用中間表表達這種關系。

多對多

四

患者主索引設計實戰（含視頻）

設計好數據庫表應該具備2種能力，第一，對業務的理解程度；第二，數據庫專業知識與實際設計經驗。我曾經寫過一篇文章，“如何全面構建患者主索引新思路”（https://www.jianshu.com/p/af0b84a46fd8），大家有興趣可以去看看，可能對你有所幫助。假設現在要你設計一個“患者主索引”滿足新時代各種要求，你會怎么做？可能文字表達不清楚，我錄了一個小視頻來表達這個意思。你可以帶著這個問題，來看我的視頻。

視頻地址如下：https://www.iqiyi.com/v_19ryciu2y4.html 密碼：chima

（作者：廣東省羅定市人民醫院信息科馮火）