問個關於資料庫的笨問題

[ross_tt]

依照理論來說，如果說我現在要建的資料裡面已經有可識別用的編號，如身分證字號，那我是應該把身份證字號直接拿來當 primary key 還是說另外設一個欄位讓他自動編號的欄位來當作 primary key 呢？

小弟剛開始學習用資料庫，問了笨問題請多包涵。

[fddi1]

依照理論來說，如果說我現在要建的資料裡面已經有可識別用的編號，如身分證字號，那我是應該把身份證字號直接拿來當 primary key 還是說另外設一個欄位讓他自動編號的欄位來當作 primary key 呢？

小弟剛開始學習用資料庫，問了笨問題請多包涵。

如果你確定身分證字號在所建立的table中是獨特不會重複的資料，那就使用身分證字號當作primary key，如果用sequential serial當作primary key，首先也沒有什麼特別的優點，其次增加整個tablespace的數量，如果table很大，而且經常被使用,整個IDUC的速度會降低。

[悲]

視乎需求，另設一自動編號的欄位作 primary key 較佳；可迴避 primary key 是 不可重複 及 不可空白。

例：警務資料庫，一個表格，欄位如下

報案人身份證字號
報案日期時間
受理警員編號
案件陳述
案件參考編號

如使用身分證字號作 primary key，則；

設想昨天某甲被盜，報了案；有了一個記錄。禍不單行，某甲今天被汽車撞傷、能再報案否？

另，未申領身分證的小童 或 沒有身分證的遊客；可報案乎？

[ross_tt]

以悲兄的例子來說，這個警務資料表，自動編號的欄位就可以當作受理案件流水號的編號嘍！對不對呢？

我原先的意思是說，比如我現在要建立的是員工人事基本資料表，這種情形下身分證字號應該就是屬於哪種獨一無二的的資料型態，哪這種情形還須不需要編一個自動編號的欄位？

不過看了 fddi1 兄與悲兄的說明，大概有個譜了，謝謝。

[ulysses]

我傾向使用 auto increment 的 key，好處是

1. 你在其他 Table 中只要有一個 UserID int 的欄位就可以 Reference 到這個人。如果用身分證字號的話，就要用 ID char(10) 這樣的 Reference。

2. 你可以建立一個 UserID=0 的會員作為容錯之用，當你在作 Table Join 查詢時，若是資料有問題，這一筆資料不至於查不出來。

3. 在瀏覽器的 URL 欄位只要用 xxx?UserID=123 這樣就好，如果是用身分證字號的話，就要用 xxx?ID=x123456789，搞不好會洩漏會員身分。

4. 會員登入後你要用 Session 來存，只要存一個 Integer（4byte）就好，身分證字號的話至少要 10byte。

5. 如果你的會員有外國人的話，他們會用護照號碼。登記時的檢查碼都不相同，這點也必須考量。

建立兩個 key 的缺點是更新時速度較慢。因為資料庫必須維護兩個 index。所以你在建立 key 的時候必須考量幾件事：

1. 這個 table 是 UPDATE/INSERT 的機會比較多（例如 Log）還是被 SELECT 的機會多。還有在 SELECT 的時候會顯示哪些欄位，會用哪些欄位當成 WHERE 限制條件和 GROUP BY 查詢條件。

2. table 資料量會有多大。一般來說，在上千筆資料時 index 的效果會比較明顯，在十萬筆以下的話你都不用太顧慮更新時的效能問題。

舉例來說，P4-2G+512M+80G IDE 的機器，安裝 Win2K 和 MS-SQL 2K，處理一個複雜的訂單資料表，在超過30萬筆時更新一筆資料需要的時間是少一個 key 的狀況的兩倍，大約是 150ms。而查詢時有設定關鍵 key 的速度則是沒有設定關鍵 key 的狀況的十倍。所以要不要這個 key 答案就很明顯。

[fddi1]

我傾向使用 auto increment 的 key，好處是

1. 你在其他 Table 中只要有一個 UserID int 的欄位就可以 Reference 到這個人。如果用身分證字號的話，就要用 ID char(10) 這樣的 Reference。

...

如果其它table要用foreign key來reference這個自動產生的sequential data，那表示那個table要特別建立一個欄位當作foreign key，而且要能和原先的table的primary key做mapping，在原本primary key是由sequential serial所組成的情況下，要有效的維持其它的table變成一件很瑣碎的事，而且也可能影響到data normalization。

[ulysses]

如果其它table要用foreign key來reference這個自動產生的sequential data，那表示那個table要特別建立一個欄位當作foreign key，而且要能和原先的table的primary key做mapping，在原本primary key是由sequential serial所組成的情況下，要有效的維持其它的table變成一件很瑣碎的事，而且也可能影響到data normalization。

剛好相反吧？你認為主 table 的 Primary key 難道是不會變的嗎？你今天建立了一個員工資料表，兩個月後有一個人跑來找你說他的身分證字號打錯了要你更正，那請問你到底要更正多少個 table？除非你用以下的子句宣告 Foreign Key

ID chr(10) FOREIGN KEY REFERENCES employee_table (ID) ON DELETE CASCADE ON UPDATE CASCADE

不過這只有在 MSSQL 2000 以後才有，MySQL 的 FOREIGN KEY 根本沒有作用。同時別忘了 FOREIGN KEY 本身也是一個 INDEX KEY。當你要更新主 table 的 Primary Key 的時候會多曠日耗時應該不難想像。

不要認為這種事發生機率不大。我被不守規矩的台灣使用者整得眼冒金星。你能想像一家零售商的商品代碼每天至少都會調整兩三次，就是為了看另一個部門的同仁不順眼嗎？你能想像在倉庫工廠打單的人員會把所有的 0 看成 O、把所有的 I 看成 1，把所有的 P 看成 9，搞到最後只好把所有的代碼重新編過避免容易誤會的英數字嗎？

任何的容錯、任何的限制條件，在台灣的軟體市場中都會變成給自己找麻煩的痛苦根源。最好就是不要用 Foreign Key 限制條件，用程式、Stored Procedure 和 Trigger 來維持資料的一致性。沒錯這樣是比較麻煩，寫的程式也要比較多，但是當使用者要求你把資料弄得不一致時，才不會去撞牆。另外，你如果試過一個十萬筆資料的資料表和一個三千筆資料的資料表作關連查詢的話，你會發現在 SELECT 的時候有沒有設定 FOREIGN KEY 根本沒差。唯一會有影響的，是同時選取的 Column 是否在同一個 INDEX 中，以及對 Clustered Index 的限制條件。

況且，FOREIGN KEY 就是 FOREIGN KEY，用 Integer 還是 char 干 normalization 啥事？何況當你需要作統計報表的時候，normalization 的程度太高反而是給自己找麻煩。

[bryanchang]

大家純粹就事論事，還請 ulysses 在言辭上多加保留，不要傷了和氣。

[ulysses]

抱歉。
反省中。
我只要一想到過去兩年我伺候的那個客戶，就會失去冷靜。
我之前也是直接用 UNIQUE 的代碼作 Primary Key，遵守 normalization 原則。直到發生我上面說的那兩件事。為了這件事他們公司發生激烈內鬥，他們的老闆給我極大壓力，要我負責解決造成他們員工不合的『技術』問題，把我整到要去跳樓。我花了很大的工夫把所有 key 通通改成 Sequential 形態，把 normalized 完成分出去的 table 合併，又花了更大的工夫把近五百隻程式一個個改寫，讓我去年的春節整個泡湯。
一朝被蛇咬十年怕井繩，我不希望有任何人重蹈我的覆轍。抱歉語氣激烈。

[ross_tt]

對不起各位前輩

我原先以為這只是個小問題，沒想到.....

請各位前輩息怒

[fddi1]

抱歉。
反省中。
我只要一想到過去兩年我伺候的那個客戶，就會失去冷靜。
我之前也是直接用 UNIQUE 的代碼作 Primary Key，遵守 normalization 原則。直到發生我上面說的那兩件事。為了這件事他們公司發生激烈內鬥，他們的老闆給我極大壓力，要我負責解決造成他們員工不合的『技術』問題，把我整到要去跳樓。我花了很大的工夫把所有 key 通通改成 Sequential 形態，把 normalized 完成分出去的 table 合併，又花了更大的工夫把近五百隻程式一個個改寫，讓我去年的春節整個泡湯。
一朝被蛇咬十年怕井繩，我不希望有任何人重蹈我的覆轍。抱歉語氣激烈。

其實如果碰到這種情形，要檢討的並不是資料庫本身的設計問題，而是資料輸入正確性的流程，但是如果因為要屈就於資料輸入流程而犧牲資料庫的設計而使用sequential serial當作primary key，尤其當有一個龐大的資料庫時就會出現問題，簡單的來說，integer就像是當時W2K的bug，它有一定的限制，舉例來說，我現在手上的幾個在SUN 15k上跑的Oracle資料庫，每個每天大概分別有5百萬筆獨特的資料輸入，其中還不包含de-duplication，唯一的差別是我的資料全部是自動輸入，但是如果我使用sequential serial當作primary key，在沒有normalization的狀態下，我的sequential serial大概沒幾天就到底了，而且也沒有辦法做有效的de-duplication，所以整個流程當初在設計時，就有一個Identifier field當作Primary Key。

在資料庫的設計上，當然還是可以用sequential serial當作primary key，但是前提是資料庫的大小不大，以及在tablespace中沒有辦法找到獨特不重複的欄位。

[ulysses]

對不起各位前輩
我原先以為這只是個小問題，沒想到.....
請各位前輩息怒

抱歉，不是針對您或 fddi 兄或是其他任何人。只是想起不愉快的往事。
不過資料庫的 KEY 可不是小問題。如何正確的設置 KEY 關係到整個系統的分析與資料輸入的方式，和程式寫作更是密切相關。你如果用 MySQL 的話其實感覺不大出來 KEY 的影響，可是如果你用 MSSQL 的話就有差了。我一般都是先把會用到的 SQL 句子列出來，然後塞入大量資料循序測試不同的 Index 的效果，才能決定最佳的組合。有些時候會發現理論和實際狀況相差還真遠。

但是如果因為要屈就於資料輸入流程而犧牲資料庫的設計而使用sequential serial當作primary key，尤其當有一個龐大的資料庫時就會出現問題，簡單的來說，integer就像是當時W2K的bug，它有一定的限制，舉例來說，我現在手上的幾個在SUN 15k上跑的Oracle資料庫，每個每天大概分別有5百萬筆獨特的資料輸入，其中還不包含de-duplication，唯一的差別是我的資料全部是自動輸入，但是如果我使用sequential serial當作primary key，在沒有normalization的狀態下，我的sequential serial大概沒幾天就到底了，而且也沒有辦法做有效的de-duplication，所以整個流程當初在設計時，就有一個Identifier field當作Primary Key。在資料庫的設計上，當然還是可以用sequential serial當作primary key，但是前提是資料庫的大小不大，以及在tablespace中沒有辦法找到獨特不重複的欄位。

一般我會建議用 Sequential Number 的場合，大多也都是 Reference 性質的資料表，例如分類、商品型錄、員工、客戶名單等等。這些 Reference 大多不會超過五千筆資料，而使用到這些 Reference 的資料表則可能會有數十萬到數百萬筆資料，因此使用 Intger 格式的 Sequential Number 是比較省事的做法；因為這些 Reference 經常會要更新，同時以人工方式編列的 ID 實在無法預期他的正確性。

像我碰到的狀況是中型企業的進銷存，每天大約有兩千筆資料由人工方式輸入，查詢動作是系統的主要動作，而 INSERT 與 UPDATE 的比例接近一比一，查詢時大多是 FULL TABLE SCAN，平均每個查詢要關連六到八個資料表。因為是 Web AP，查詢與統計必須在幾秒鐘內完成。因此我的 Primary Key 幾乎都是宣告 IDENTITY NONCLUSTERED，而且所有的 Table 都是以單據的“日期”當作是 CLUSTERED INDEX。要不這麼作的話，查詢時一定會 Timeout。

[fddi1]

無論如何，我建議在實際設計SQL queries之前，先把ER Diagram畫出來，檢查一下每個table及資料庫的屬性和關聯性，有時候短期的迅速解決方案已長遠的角度看來，在未來會產生很多潛在的問題，這時候，還不如在剛開始的時候下點苦工，把資料庫的特性和需求定義清楚，然後在設計階段完成後做風險分析，在考慮設計需不需要因為所評估的風險做調整，等到這些程序都完成後，才移到production。

[bryanchang]

無論如何，我建議在實際設計SQL queries之前，先把ER Diagram畫出來，檢查一下每個table及資料庫的屬性和關聯性，有時候短期的迅速解決方案已長遠的角度看來，在未來會產生很多潛在的問題，這時候，還不如在剛開始的時候下點苦工，把資料庫的特性和需求定義清楚，然後在設計階段完成後做風險分析，在考慮設計需不需要因為所評估的風險做調整，等到這些程序都完成後，才移到production。

好吧～看到這裡我也來插個嘴：我覺得 fddi1 的做法對於有規模點的（也就是有 IT 部門的）公司來講可能會比較適合，ulysses 講的則可能是他在小公司裡做單兵 IT 的苦水經驗談。

我自己的經驗，要是這類的資料庫是在中小型公司裡使用，用戶們對於應用程式的遊戲規則多半是很隨意的朝令夕改，而且馬上就要改好，因此像 fddi1 所提那種按步就班的「學院派」搞法多半是玩不動的，換句話說命苦的單兵 IT 朋友們還是多喝點雞精加油吧！

[fddi1]

好吧～看到這裡我也來插個嘴：我覺得 fddi1 的做法對於有規模點的（也就是有 IT 部門的）公司來講可能會比較適合，ulysses 講的則可能是他在小公司裡做單兵 IT 的苦水經驗談。

我自己的經驗，要是這類的資料庫是在中小型公司裡使用，用戶們對於應用程式的遊戲規則多半是很隨意的朝令夕改，而且馬上就要改好，因此像 fddi1 所提那種按步就班的「學院派」搞法多半是玩不動的，換句話說命苦的單兵 IT 朋友們還是多喝點雞精加油吧！

可是不要忘了，大公司多半也是由小公司轉型的，一般的小型公司的機動力較強，處理流程較短，但是在基礎設計上也不該馬虎，我碰過好幾個小型的公司就認為碰到問題馬上就叫負責IT的那個人改，幾個小時就搞定了，結果等到那個人離職後，接手的人完全沒有documentation可以參考，也沒有process可以依循，最後所花費的時間和金錢反而比詳細規劃的流程要多出好幾倍，以經濟效益來說，實在不划算。

[bryanchang]

最後所花費的時間和金錢反而比詳細規劃的流程要多出好幾倍，以經濟效益來說，實在不划算。

呵呵～這就是問題的關鍵啦！中小型企業的老闆是不會懂這點的，而他們又多半是鄧小平同志黑貓白貓論的忠實信徒…