在新建表和資料倉儲時，往往需要做出許多決定。一些在當時看起來似乎是無關緊要的決定，卻最終會導致你和你的客戶在使用資料庫的整個過程中飽嘗痛苦。
我們已經和數千人以及他們的資料庫打過交道了，在經歷了無數個小時的讀寫查詢之後，可以說我們幾乎見過所有的情況了。下面是我們總結出的有助於建立無痛模式（Schema）的10條規則。
1. 只使用小寫字母，數字和下劃線
不要在資料庫、模式、表格或者列名上使用點、空格或者破折號。因為點號是用來識別物件的，通常只在 database.schema.table.column 這種情況下使用。
在物件的名字中也包含點號會帶來困擾。同樣地，在物件名中使用空格將迫使你給查詢加入一堆不必要的引號：
|
1 2 3 4 5 |
select "user name" from events -- vs select user_name from events |
另外，一旦在表格或者列名上使用了大寫字母，查詢就會變得更加難寫。因為如果全部都是小寫字母的話，人們就沒有必要去特別記憶使用者表到底是Users還是users。
不僅如此，當你最終變更資料庫或者將表複製到資料倉儲中時，除了一些資料庫以外，你無須記住哪個資料庫是大小寫敏感的。
2. 使用簡單且具有描述性的列名
如果users表需要定義一個引用了packages表的外來鍵，那麼將其命名為package_id是個不錯的選擇。我們應該避免像是pkg_fk這樣的又短又含糊的列名，因為其他人很難知道那是什麼意思。具有描述性的名字能夠使得其他人更容易理解模式，而且當團隊擴大時這一點對於保持工作效率也是很重要的。
不要使用模稜兩可的名字命名可能有多種解釋方法的資料。如果你發現自己正在以item_type或item_value這樣的命名風格建立列時，那麼可能就說明你應該使用更多的帶有具體名字的列了，像是photo_count、view_count和transaction_price。
因為這樣做的話，列中儲存了什麼樣的資料總是可以由模式得知的，而並不需要由行中的其他值推匯出來。
|
1 2 3 4 5 |
select sum(item_value) as photo_countfrom itemswhere item_type = 'Photo Count' -- vs select sum(photo_count) from items |
不要使用表名作為列名的字首。因為一般來說在users表中定義諸如user_birthday、user_created_at或者user_name這樣的列名起不到什麼輔助作用。
最後，還要避免將諸如column、tag或user這樣的保留關鍵字用作列名。因為一旦使用了保留關鍵字，就意味著不得不在查詢語句中使用額外的引用符，而當有人忘記這麼做的時候，資料庫就會產生非常令人困惑的錯誤資訊。而且如果在本該是列名出現地方使用了關鍵字，那麼資料庫就無法理解查詢語句。
3.使用簡單且具有描述性的表名
如果表名是由多個單片語成的，那麼請使用下劃線分割它們。因為package_deliveries要比packagedeliveries更容易讀。
如果可能的話，總是使用一個單詞而不是兩個，因為deliveries要更加容易閱讀。
|
1 2 3 4 5 |
select * from packagedeliveries -- vs select * from deliveries |
不要用模式的名字作為表名的字首。如果你需要將一些表劃入一個範圍，那麼只需將這些表放入到一個模式中即可。和有字首的列名一樣，諸如store_items、store_transactions或者store_coupons這樣的表名，通常都是不需要額外的字首的。
我們推薦使用複數形式的名字為表命名（例如packages），並且對聯合表的表名中的兩個單詞也都使用複數形式。單數形式的表名更可能意外地與關鍵字衝突並且一般在查詢中其可讀性也不高。
4. 將整形作為主鍵
無論你是正在使用各種UUID（通用唯一識別碼）型別的列作為主鍵，還是你認為加入帶有自增長整型序列的主鍵根本沒有意義（比如對於聯合表），我們都建議你新增一個帶有自增長整型序列的標準id列。這種型別的主鍵會使得特定的分析變得更加容易，比如從一組資料中只選出第一行。
並且當匯入資料的工作導致了資料的重複時，主鍵也會成為靈丹妙藥，因為我們可以通過主鍵輕鬆刪除特定的行：
|
1 2 |
delete from my_table where id in (select ...) as duplicated_ids |
避免多列主鍵。當努力編寫高效的查詢時，多列主鍵將會導致查詢語句很難理解，並且很難修改。我們可以使用一個整型的主鍵，或者一個多列的唯一約束，再或者一些單列的索引來取代多列主鍵。
5. 與外來鍵一致
命名主鍵和外來鍵有許多種風格。我們建議諸位使用的是最為普遍的風格，即對於任意的表格foo，將foo中的主鍵命名為id，將所有的外來鍵命名為foo_id。
另一種風格是使用全域性統一的主鍵名。在這種風格下，表foo的主鍵稱為foo_id，而所有的外來鍵也稱為foo_id。不過無論使用哪種風格，使用縮寫的話（比如將users表縮寫為uid)，總是會造成困擾或名稱衝突，所以應該避免使用縮寫。
而且，無論你選用了什麼風格，都要堅持下去。不要在某些地方使用uid，而又在其他地方使用user_id或者users_fk。
|
1 2 3 4 5 6 7 8 9 |
select _from packages join users on users.user_id = packages.uid -- vs select _from packages join users on users.id = packages.user_id -- or select *from packages join users using (user_id) |
除此之外還要留意外來鍵並不顯式匹配一張表的情況。一個名為owner_id的列可能是users表的一個外來鍵，當然也可能不是。因此如果有必要的話，請將作為外來鍵的列命名為user_id或者owner_user_id。
6. 將日期時間儲存為各種日期時間型別
不要使用Unix的時間戳或者字串來儲存日期，而是要將它們轉換為各種日期時間型別。雖然SQL的日期計算函式並不是最棒的，但是呼叫這些函式來處理時間戳總比自己來處理要簡單。在查詢時，我們需要為每一個涉及到從timestamp到datetime型別的轉換的查詢呼叫SQL的日期函式
|
1 2 3 4 5 |
select date(from_unixtime(created_at)) from packages -- vs select date(created_at) from packages |
不要將年、月、日分別儲存到不同的列中。因為這樣會導致每個有關時間序列的查詢都更加難寫，而且也會在使用這張表的日期資訊時給大多數的SQL初學者造成障礙。
|
1 2 3 4 5 |
select date(created_year || '-' || created_month || '-' || created_day) -- vs select date(created_at) |
7. 總是使用UTC
使用時區而不是UTC將導致無窮無盡的問題。好的工具（包括我們的Periscope）擁有你所需要的從UTC轉換為你所在時區資料的所有功能。在Periscope中，簡單地加個:pst就可以將UTC轉換為太平洋時間。
|
1 |
select [created_at:pst], email_addressfrom users |
應該將資料庫的時區設為UTC，並且所有datetime的列都應該是剝離時區後的型別（如，無時區的timestamp）。
如果你的資料庫的時區不是UTC，或者你的資料庫混合了UTC和非UTC時間日期，那麼時間序列的分析查詢將會變得更加困難。
8.單一的真相源
一塊資料應該只有單一的真相源（Source of Truth）。檢視和彙總（Rollup）本身應該有所標示。這樣做的話，資料的消費者就會知道他們使用的資料和原生真相之間的區別。
|
1 |
select *from daily_usage_rollup |
另一方面，將諸如user_id、user_id_old或者user_id_v2的遺留列都保留的話，只會帶來無盡的困擾。因此請確保在日常維護中會進行刪除廢棄的表格和不再使用的欄位的工作。
9.優先使用沒有JSON列的表格
請不要使用列過多的表。如果一張表有超過幾十個列並且其中一些是以序列命名（例如，answer1、answer2、answer3）的話，那麼馬上你就會感到不好過了。
正確的做法是將這樣的錶轉化為不包含重複列的模式，因為這樣的模式將很容易查詢。例如，在一個查詢中計算某個調查表中已完成的題目的數目：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
select sum( (case when answer1 is not null then 1 else 0 end) + (case when answer2 is not null then 1 else 0 end) + (case when answer3 is not null then 1 else 0 end) ) as num_answers from surveys where id = 123 -- vs select count(response) from answers where survey_id = 123 |
對於查詢分析而言，從JSON列抽出資料的操作將大幅降低查詢的效能。雖然有很多很棒的理由支援我們在產品中使用JSON列，但是對於查詢分析來說並不是這樣。大膽得將JSON列拆解為更簡單資料型別，可以使查詢分析變得更快更容易。
10.不要過度規範化
日期，郵編和國家不需要使用帶有外來鍵查詢的表單獨存放。過度地規範化將會導致每個查詢後面都要帶上一些相同的表連線操作。這樣不但建立了許多重複的SQL，而且資料庫為此還要做很多額外的工作。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select dates.d, count(1) from users join dates on users.created_date_id = dates.id group by 1 -- vs select date(created_at), count(1) from users group by 1 |
表是資料庫中的一等物件，擁有很多屬於自己的資料。其餘的任何資料都可以作為另一個更重要物件的附加列。
更好的模式在等著你！ 如你所期待的，對於你和團隊的新成員來說，遵循這些規則將有助於下一張表或者資料倉儲變得更容易查詢。如果你不認可或者有更多的規則建議，請聯絡我們吧hello@periscope.io。我們很期待聽到你的聲音！