資料庫設計原則

以前跟同事一起訂下的資料庫設計原則，來減少一開始設計不好造成事後要進行大改的風險。

實體關聯的設計是最需要謹慎的，這裡一旦與規格差太多，程式可能就要重寫。

欄位名稱建議在系統中是唯一的，這樣可以減少 JOIN 時還需要換名稱，FK 使用跟 PK 一樣的名稱也可以增加維護性。

---

資料庫設計流程

(Instance 層級)

• 依照 Instance 建立 ER 圖，須清楚描述關聯與實體
• 審視並釐清實體對於規格的含蓋範圍

(Table 層級)

• 依照釐清後的含蓋範圍，修正 ER 圖
• 審視並確認完整 ER圖

(Column 層級)

• 定義各資料表欄位，建立 OrgTableSchema.sql
• 審視並確認完整 Schema
• 有問題重複上述步驟

(Develop 層級)

• 維護 OrgTableSchema.sql
• 使用 DbSchemaTool 工具產生 Schema.sql
• 建立資料庫
• 修正 Dbml
• 更新 DB 專案

資料表與欄位定義

• Id 是 identity 去尾的縮寫，所以 d 要小寫
• No 改成 Num 會比較好，因為會跟 Yes, No 混淆
• Id 是流水號，其他的建議用 Num 或 Code 表示
• 避免用單單字做欄位名，盡量用多單字 ( Ex. IssueType, CompanyCode, SettingId, CompanyName )
• 盡量用 流水號 或 Guid 這類無意義的 id 做 PK
• 除了多對多的中間表，PK 都必須是單一欄位
• 資料來源為 OptionSetting, 值為 OptionId 者，欄位名稱命名須加尾贅詞 Id，型態為 INT
• 資料來源為 Enum, 欄位型態則為 Nvarchar(N)
• 盡量避免資料來源為 bool 對應 bit，因為擴充性太低，資料來源改用 Enum 對應 Nvarchar(N)
• 歷史檔與主檔的差異
• 主檔的 ModifiedBy ModifierdDate 為歷史檔的 CreatedBy CreatedDate，歷史檔無 ModifiedBy ModifierdDate
• 新增歷史檔的 PK , 主檔的 PK 設定為 FK
• 其餘欄位應該與主檔相同
• 每一個 FK 需預設建立 IX，其餘調整於開發完成後，依照使用者回報進行調整
• 多對多的中間表如果超過 3 個附加欄位，必須用一般的方式設計

PK 必須使用 Guid 的情況

• 如果只保留短期資料，但會有大量新增或刪除(每天一萬筆新增)
• 如果有多系統都可以新增資料，最後要合流的狀況

設計 Table 的欄位順序

1. PK
2. FK [主檔]
3. FK [選項]
4. AK
5. 其餘不重要的資料欄位
6. CreatedBy
7. CreatedDate
8. ModifiedBy
9. ModifierdDate

資料型態

• 文字 : NVARCHAR 長度為預定輸入的兩倍
• 日期 : DATETIMEOFFSET 具有時區紀錄(MSDN 建議)
• 時間 : TIME
• 整數 : INT
• Enum : NVARCHAR (32)
• Guid : UNIQUEIDENTIFIER
• 金錢 : MONEY
• 精確浮點數 : DECIMAL (18, 4)

鍵值規則

• Table 規則 {ProjectName}_{TableName}
   Ex: WMS_Carrier
• Foreign Key 規則 FK_{ProjectName}_{TableName}_{Columns}
   Ex: FK_WMS_CarrierMaterial_CarrierId
• Unique 規則 AK_{ProjectName}_{TableName}_{Columns}
   Ex: FK_WMS_CarrierMaterial_CarrierId
• Index 規則 IX_{ProjectName}_{TableName}_{Columns}
   Ex: IX_WMS_CarrierMaterial_CarrierId

縮寫解釋

• PK: Primary Key
• FK: Foreign Key
• AK: Alternate Key
• IX: IndeX
• CK: ChecK
• DF: DeFault

MSSQL 定序設定

定序 Chinese_Taiwan_Stroke_CS_AI

_CS 區分大小寫
_CI 不區分大小寫

_AS 區分腔調 a != á
_AI 不區分腔調

_KS 區分日文假名字元
_WS 區分全形與半形字元

定序會影響查詢與唯一值的判定，例如不區分大小的定序在 WHERE 'abc' = 'ABC' 會是 true。

定序選擇並沒有標準答案，我個人是採用區分大小的定序，如果規格是不區分的時候，再用程式轉大寫或小寫，這部分可以在 DAO 統一完成，雖然也會有遺漏的情況，但至少是可以由程式掌控，如果出現只有某些不區分，而大部分還是區分的時候，還是由程式掌控會比較好。