優秀資料庫設計的藝術就像遊泳。入手相對容易，精通則很困難。如果你想學習設計資料庫，一定得有一些理論背景，比如關于資料庫設計範式和事務隔離級别的知識。但你還應該盡可能地多加練習，因為可悲的事實就是，我們在犯錯中學習得更多。
本文中，通過展示在設計資料庫時常犯的一些錯誤，我們嘗試把學習資料庫設計變得容易一點。
注意，我們假定讀者了解資料庫範式并知道一點關系資料庫的基礎知識，因而不會去讨論資料庫規範化。隻要有可能，文中所涵蓋的主題都将使用 vertabelo 模組化和執行個體來說明。
本文涵蓋了設計資料庫的各個方面，但着重于web應用，是以有些例子可能是特定于web應用程式的。
假設我們想要為一個線上書城設計資料庫。該系統應當允許使用者執行以下活動：
通過書名、描述和作者資訊浏覽與搜尋書籍，
閱讀後對書籍添加評論和評級，
定購書籍，
檢視訂單處理的狀态。
那麼最開始的資料庫模型可能如下所示：
該資料庫有8張表，其中沒有資料。我們已經往裡面填充了一些人工生成的測試資料。現在資料庫裡包含了一些示範資料，準備好開始模型檢查了，包括識别那些現在不可見但将來在真實使用者使用時會出現的潛在問題。
你可以在上面的模型中看到我們用“order”命名了一張表。不過，或許你還記得，“order”在sql中是保留字! 是以如果你試圖發起一個sql查詢：
mysql
1
select * from order order by id
資料庫管理系統将會抗議。很幸運，在postgresql中用雙引号把表名包裹起來就行了，語句仍可以執行：
select
*
from
"order"
order by
id
等等，可是這裡的“order”是小寫！
沒錯，這值得深究。如果你在sql中用雙引号把什麼包了起來，它就變成分隔辨別符，大多數資料庫将以區分大小寫的方式解釋它。由于“order” 是sql中的保留字，vertabelo生成sql會自動把order用雙引号包起來：
2
3
4
5
6
create table "order" (
id int not null,
customer_id int not null,
order_status_id int not null,
constraint order_pk primary key (id)
);
但是由于辨別符被雙引号包裹且是小寫，表名仍然是小寫。現在如果你希望事情變得更複雜，我可以建立另一個表，這次把它名為order（大寫），postgresql不會檢測到命名沖突：
create
table
(
id
int not
null,
customer_id
order_status_id
constraint
order_pk2
primary key
(id)
如果一個辨別符沒有被雙引号包裹，它就被稱作“普通辨別符”，在被使用前自動被轉成大寫——這是sql 92标準所要求的。但是辨別符如果被雙引号包裹
——就被稱作“分隔辨別符”——要求被保持原樣。
底線就是——不要使用關鍵字來當做對象名稱。永遠不要。
你知道oracle中名稱長度上限是30個字元嗎？
關于給表以及資料庫其他元素命好名——這裡命好名的意思不僅是“不與sql關鍵字沖突”，還包括是自解釋的且容易記住——這一點常常被嚴重低估。在一個小型資料庫中，比如我們這個，命名其實并不是件非常重要的事。但是當你的資料庫增長到100、200或者500張表，你就會知道在項目的生命周期中為保證模型的可維護性，一緻和直覺的命名至關重要。
記住你不光是給表和列命名，還包括索引、限制和外鍵。你應當建立命名約定來給這些資料庫對象命名。記住名字的長度也是有限制的。如果你給索引命名太長，資料庫也會抗議。
提示：
讓你的資料庫中的名字：
盡可能短，
直覺，盡可能正确和具有描述性，
保持一緻性；
避免使用sql和資料庫引擎特定的關鍵字作為名字；
建立命名約定；
以下是把order表重命名為purchase後的模型：
模型中的改變如下：
讓我們進一步來看這個模型。如我們所看到的，在book_comment表中，comment列的類型是1000個以内的字元。這意味着什麼？
假設這個字段将是gui（使用者隻能輸入非格式化的評論）中的純文字，那麼它簡單地意味着該字段可以存儲最多1000個文本字元。如果是這樣的話——這裡沒有錯誤。
但是如果這個字段允許一些格式化的動作，比如bbcode或者html，那麼使用者實際上輸入進去的字元數量是未知的。假如他們輸入一個簡單的評論，如下：
xhtml
i like that book!
那麼它會隻占用17個字元。然而如果他們使用粗體格式化它，像這樣：
i
<b>like</b>
that
book!
這就需要24個字元的存儲空間，而使用者在gui上隻會看到17個。
是以如果書城的使用者可以使用某種像所見即所得的編輯器來格式化評論内容，那麼限制”comment”字段的大小是存在潛在危險的。因為當使用者超過了最大評論長度（1000個原始html字元），他們在gui上所看到的仍然會低于1000。這種情況下就應當修改類型為text而不要在資料庫中限制長度了。
然而，當設定了文本字段的限制，你應當始終謹記文本的編碼方式。
varchar(100)類型在postgresql中代表100個字元，而在oracle中代表100位元組。
避免籠統地解釋，我們來看一個例子。在oracle中，varchar類型被限制到4000個位元組，那麼這就是一個強限制——沒有任何方法可以超過它。是以如果你定義了一個列是varchar(3000
char)，那它意味着你可以存儲3000個字元，但隻有在它不會使用到磁盤上超過4000個位元組的情況下。為何一個3000個字元的文本在磁盤上會超過4000個位元組呢？英文字元的情況下是不會發生的，但是其它語言中就可能出現。舉個例子，如果你嘗試用中文的方式存儲”mother”——母親，且資料庫使用utf-8的方式編碼，那麼這個字元串會占用磁盤上2個字元但是6個位元組。
bmp（basic multilingual plane，基本多語言平面，unicode零号平面）是一個字元集，支援用utf-16讓每個字元用2個位元組進行編碼。幸運地是，它覆寫了世界上大多數使用的字元。
注意，不同資料庫對于可變長的字元和文本字段會有不同的限制。舉些例子：
前面提到過，oracle對varchar類型的列有4000個位元組限制。
oracle将低于4kb的clob直接存儲到表中，這種資料通路起來如同任何varchar列一樣快。但大些的clob讀取時就會耗時變長，因為它們存在表的外面。
postgresql允許一個未限制長度的varchar列存儲甚至是千兆位元組的字元串，且是默默地把字元串存到背景表，不會降低整個表的性能。
一般而言，考慮到安全和性能，資料庫中限制文本列的長度是好的，但有時這個做法可能沒有必要或者不友善；
不同的資料庫對待文本限制可能會有差異；
使用英語以外的語言時永遠記住編碼。
下面是把book_comment的評論類型修改為text後的模型：
模型中修改的地方如下圖:
有一個說法是“偉大是實作的，而不是被贈與的”。這個說法同樣可以用在性能上——通過精心設計資料庫模型，優化資料庫參數以及優化資料庫應用查詢來實作。當然這裡我們關注的是模型設計。
在例子中，我們假定書城的gui設計者決定在首頁顯示最新的30條評論。為了查詢這些評論，我們将使用如下的語句：
select comment, send_ts from book_comment order by send_ts desc limit 30;
這個查詢運作起來有多快？在我的筆記本上花費不到70毫秒。但是如果我們希望應用能夠按比例變化（在高負載下快速運作），需要在更大的資料上檢測。是以我在book_comment表中插入了更多的記錄。為此我将使用一個很長的單詞清單，然後使用一個簡單的perl指令将其轉成sql。
現在我要把這個sql導入到postgresql資料庫。一旦導入開始，我就會檢測之前那個查詢的執行時間。統計結果在如下的表格中：
如你所見，随着 book_comment 中行數的增加，要擷取最新30行所花費的查詢時間也在成比例地增加。為何耗費時間增長？我們看看這個查詢計劃：
7
db=#
explain select comment, send_ts from book_comment order by send_ts desc limit 30;
query
plan
-------------------------------------------------------------------
limit (cost=28244.01..28244.09
rows=30
width=17)
-> sort (cost=28244.01..29751.62
rows=603044
sort
key:
send_ts
-> seq
scan
on
book_comment (cost=0.00..10433.44
這個查詢計劃告訴我們資料庫如何處理查詢及計算結果的大緻時間成本。這裡postgresql告訴我們将進行“seq scan on book_comment”，這意味着它将逐個檢查 book_comment 表的所有記錄，以此對send_ts列的值進行排序。貌似postgresql還沒有聰明到在不去對所有的600,000條進行排序的條件下查詢30個最新記錄。
幸運地是，我們可以通過告知postgresql根據send_ts進行排序并儲存結果來幫助它。為此，我們先在該列上建立一個索引：
create index book_comment_send_ts_idx on book_comment(send_ts);
現在我們的查詢語句從600,000條記錄中查詢出最新30條所花費的時間又是67毫秒了。查詢計劃差别非常大：
query
plan
--------------------------------------------------------------------
limit (cost=0.42..1.43
-> index
backward
using
book_comment_send_ts_idx
book_comment (cost=0.42..20465.77
rows=610667
“index scan”指不是逐行掃描book_comment表，而是資料庫會掃描我們剛剛建立的索引。估計查詢成本小于1.43,低于之前的2.8萬倍。
你遇到了性能問題？第一次嘗試解決就應當是找到運作時間最長的查詢，讓你的資料庫來解釋它們，并且尋找全表掃描。如果你找到了，也許增加一些索引可以快速提升速度。
不過，資料庫性能設計是一個龐大的主題，超出了本文的範圍。
我們在如下提示中列出一些重要的方面。
經常檢查運作時間長的查詢，或許可以用上explain功能；大多數現代資料庫都有該功能；
在建立索引時：
記住它們不會一直被用到；資料庫如果計算出使用索引所耗費的時間長于全表掃描或其它操作時，将不會使用索引；
記住使用索引帶來的代價是——在被索引的表上insert和delete會變慢
如果需要索引請考慮非預設類型的索引；如果你的索引工作得不是很好，請查閱資料庫手冊；
有時候你需要優化查詢，而不是模型；
不是每一個性能問題都可以通過建立一個索引來解決；有很多其它解決性能問題的方式；
各個應用層的緩存，
調優資料庫參數和緩沖區大小，
調優資料庫連接配接池大小或者線程池大小，
調整資料庫事務隔離級别，
在夜間安排批量删除，避免不必要的鎖表，
其它等等。
在book_comment.send_ts列上帶有索引的模型如下：
4 ——沒有考慮到可能的資料量或流量
通常你可以得到有關可能的資料量的附加資訊。如果你正在建構的系統是另一個已存在項目的疊代，你可以通過檢視老系統的資料量來計算出系統資料的預期大小。
如果你的書城非常成功，purchase表的資料量可能會非常大。你賣得越多，purchase表裡的資料行數增加越多。假如你事先知道這一點，你可以把目前已處理的訂單與完成的訂單分開。你可以用兩個表：purchase表記錄目前的訂單，archived_purchase表記錄完成的訂單，而不是用一張單一的purchase表。因為目前的訂單一直在被檢索：它們的狀态在被更新，由于客戶經常檢視訂單的資訊。另一方面，完成的訂單隻會被作為曆史資料儲存。它們很少被更新或者檢索，是以可以在這張表上安排更長的通路時間。訂單分離之後，經常使用的表能保持比較小，但我們仍然儲存着所有資料。
類似地，你應當優化頻繁更新的資料。想象一個系統的部分使用者資訊經常由另一個外部系統（例如，該外部系統計算同一類的獎勵積分）更新。在我們的user表中也有其它資訊，如他們的登陸賬号、密碼和全名。這些基本資訊也經常被檢索。頻繁更新降低了擷取使用者基本資訊的速度。最簡單的解決方案就是把這些資料分離到兩個表裡面：一個記錄基本資訊（經常被讀取），另一個記錄獎勵積分相關的資訊（頻繁被更新）。這樣更新操作不會減緩讀的操作。
分離頻繁和不頻繁使用的資料到多個表中不是處理大資料量的唯一方法。例如，如果你希望書的描述（description字段）非常長，你可以使用應用級别的緩存，這樣你不用經常檢索這個重量級的資料。書的描述很可能保持不變，是以這是一個很好的可被緩存的候選對象。
你的客戶必須使用業務、領域特定的知識，預估預期你将處理的資料庫中的資料量。
分離頻繁更新和頻繁讀取的資料。
對重量級、更新少的資料考慮使用應用級别的緩存。
以下是修改後的書城模型：
如果書城是面向全世界的呢？客戶來自世界各地并且使用不同的時區。管理時區的date和datetime字段算是跨國系統中一個重要的問題。
系統必須始終為使用者呈現準确的日期和時間，最好是以他們自己的時區。
舉例，特殊供應的過期時間（這是任何商城中最重要的功能）必須讓所有使用者了解一緻。如果你隻是說“促銷于12月24日結束”，他們會假定是在自己時區的12月24日半夜12點結束。如果你是指自己所在時區的聖誕前夜午夜12點，你必須說“12月24日,23.59 utc”(即無論你的時區是什麼)。對于某些使用者，它将是“december 24, 19.59”，對另外一些使用者則是“december 25, 4.49”。使用者必須看到以他們所在時區為準的促銷時間。
在一個跨時區系統中日期列類型是不會有效存在的。它應當一直是一個timestamp類型。
當登入事件在跨時區系統中發生時，可以采取類似的方式。事件的時間應該總是以某個選中的時區為準的标準化方式記錄的，例如utc，是以你能夠毫無疑問地将時間從老到新排序。
檢查你的資料庫中日期和時間資料類型的細節。sql server中timestamp與postgresql的timestamp完全不同。
用utc的方式存儲日期與時間。
處理好時區問題需要資料庫和應用代碼直接的合作。確定你了解了資料庫驅動的細節。這裡有相當多的陷阱。
如果有人删除或者修改了我們書城中的一些重要資料，可我們在3個月之後才發現，發生了什麼事情？我認為我們遇到了嚴重的問題。
也許我們有3個月前的一個備份，是以可以恢複備份到一些新的資料庫以通路到資料。此後我們将有一個契機來恢複這些資料避免損失。但是為完成這個過程，必須滿足許多因素
我們需要擁有那個合适的備份——哪一個才是合适的？
我們必須成功尋找到資料，
我們必須能不費太大力氣就恢複資料。
當我們最終恢複了資料（但确定這就是最正确的版本嗎？），就面臨第二個問題——誰幹的？誰在三個月前毀掉了資料？他們的ip/使用者名是多少？我們如何核實？為了确定這一點，我們需要：
至少儲存3個月的系統通路日志——這不太有希望，它們或許可能已經被輪轉替換了。
可以把删除資料的情況與通路日志中的某些url關聯起來。
這無疑會花費大量時間，而且沒有多大成功的勝算。
我們的模型所缺失的，就是某種意義上的審計跟蹤。有許多方式來達到這個目标：
資料庫中的表可以有建立和更新時間戳，及所建立/修改行的使用者标示。 完整的審計日志可以用觸發器或者其它對正在使用的資料庫管理系統有效的機制來實作。一些審計日志可以存儲在單獨的資料庫以確定無法修改和删除，
資料能夠防止資料丢失，通過：
不删除它，而是打上一個被删除的标記，
版本化修改。
按照慣例，保持黃金分割是最好的方式。你應當在資料安全和模型簡易性中找到平衡。儲存版本和記錄事件使得資料庫更複雜。忽略資料安全可能導緻意外的資料丢失或者恢複丢失資料的高成本。
考慮哪個資料重要到需要跟蹤修改/版本化，
考慮風險和成本之間的平衡；記住帕雷托定律指出大約80%的影響來自20%的原因；不要在不太可能的事故場景中保護你的資料，關注那些可能發生的場景。
這是對purchase和archived_purchase表加了基本審計跟蹤功能的書城模型。
模型中的修改如下（以purchase表為例）：
最後的錯誤是一個棘手的問題，因為它隻出現在一些系統中，主要是在多語種系統裡。将它添加在這裡，是因為我們經常遇到它，但它似乎并不廣為人知。
通常來說，根據字母在字母表中的順序，我們假定在一種語言中對單詞排序與逐字排序一樣容易。但是這裡有兩種陷阱：
首先，哪個字母表？如果我們的内容隻有一種語言，那很顯然，但是如果内容中有15到30種語言，該由哪一個字母表來決定順序？
其次，當重音起作用時，逐字排序有時會有錯誤。
我們将在這個法文的簡單sql查詢中舉例說明：
db=# select title from book where id between 1 and 4 order by title collate "posix";
title
-------
cote
coté
côte
côté
這是逐字排序的結果，從左到右。
但是這些單詞是法語，是以這才是正确的：
select title from book where id between 1 and 4 order by title collate "en_gb";
這兩個結果不同，因為正确的單詞順序由排序規則決定——法語中的排序規則是在給定的單詞中最後一個重音決定順序。這是該特殊語言的一個特點。是以—— 語言的内容可以影響排序結果，而忽略語言會導緻意想不到的排序結果。
在單一語言的應用中，初始化資料庫總是要用合适的區域設定，
在多語言應用中，用預設的區域設定初始化資料庫，在每一個需要排序的地方決定在sql查詢中該使用哪種排序規則：
也許你應當使用針對目前使用者的排序規則，
有時你可能希望使用特定于被浏覽資料的語言。
這是我們的書城最終的版本：