實體關聯的設計是最需要謹慎的，這裡一旦與規格差太多，程式可能就要重寫。
欄位名稱建議在系統中是唯一的，這樣可以減少 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 的欄位順序
- PK
- FK [主檔]
- FK [選項]
- AK
- 其餘不重要的資料欄位
- CreatedBy
- CreatedDate
- ModifiedBy
- 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 統一完成，雖然也會有遺漏的情況，但至少是可以由程式掌控，如果出現只有某些不區分，而大部分還是區分的時候，還是由程式掌控會比較好。
0 回應:
張貼留言