上一篇認識了 Table、Primary Key 與 Foreign Key，接著要把 Project Management 的需求真正轉成資料表。這一步不能只看畫面有哪些輸入框。畫面會改版，資料的生命週期與業務關係通常更長久；如果直接照著畫面建表，很容易把暫存狀態也存進資料庫，最後留下不少用途不明的欄位。
以工作項目清單為例，checkbox 只用來選取這次要批次更新的資料，重新整理後就會消失，所以它不是 TaskItems 的欄位。相反地，「不再顯示批次確認視窗」需要跟著使用者保留，適合放在 UserPreferences。兩者看起來都出現在前端，生命週期卻完全不同。
我會先從 User Story 找出 Entity（實體）、Attribute（屬性）與 Relationship（關係），而不是先打開資料庫工具新增欄位。
這個專案的主要實體包括帳號、專案、專案成員、工作項目、留言與工作歷程。它們之間的關係如下：
ProjectMembers 保存兩者的多對多關係。ProjectMemberRoles 連接成員與角色。多對多關係通常需要中介表。ProjectMembers 以 (ProjectId, AccountId) 作為複合主鍵，資料庫因此能阻止同一個帳號重複加入同一個專案。這比在 Service 裡先查一次「是否已存在」更可靠，因為兩個請求同時送進來時，程式檢查仍可能一起通過，資料庫約束才是最後一道防線。
正規化的目的，是減少重複資料與更新異常。這個專案先以 2NF 作為主要目標，再依一致性與查詢情境判斷是否需要繼續拆分，不會為了追求更高正規化而製造一長串難以理解的 JOIN。
| 階段 | 要檢查的事情 | 專案例子 |
|---|---|---|
| 1NF | 每個欄位只保存一個值，不把清單塞進字串 | 不在 Projects 內存放逗號分隔的成員 ID，而是建立 ProjectMembers |
| 2NF | 非鍵欄位必須依賴完整主鍵，不能只依賴複合主鍵的一部分 | ProjectMembers 不重複保存帳號名稱；名稱只依賴 AccountId，應由 Accounts 取得 |
| 3NF | 非鍵欄位不應依賴另一個非鍵欄位 | 若每個角色代碼都有固定名稱，名稱放在 ProjectRoles，中介表只保存角色 ID |
不過，重複不一定都是錯。例如 TaskItemHistories 用 Snapshot 保存當時的工作項目內容，目的是保留歷史，而不是拿來取代目前資料。這種有明確業務原因、寫入時機與讀取方式的重複可以接受；真正要避免的是同一份現況散落多處，修改時卻不知道哪一份才算數。
資料表結構確定後，還要把重要規則寫成資料庫能執行的限制。
| 設計 | 在本專案的用途 |
|---|---|
| Primary Key | 以 Id 或複合鍵唯一識別資料 |
| Foreign Key | 防止工作項目指向不存在的專案或帳號 |
| NOT NULL | 要求標題、狀態等必要資料一定要有值 |
| UNIQUE | 保證角色代碼、權限代碼或 Token Hash 不重複 |
| CHECK | 限制商業編號計數器的類型與數值範圍 |
| 最大長度與資料型別 | 日期用日期型別、狀態限制長度，避免所有欄位都變成無上限字串 |
Projects 與 TaskItems 採軟刪除，程式以 DeletedAt 判斷資料是否仍有效。它們的 Code 使用 filtered unique index，只要求「尚未刪除」的資料不可重複。這個選擇要配合業務規則：如果歷史編號永遠不得重用，就不該排除軟刪除資料。
刪除行為也不能全部設成 Cascade Delete。專案成員屬於專案的一部分，可以在專案被實體刪除時連帶處理；工作項目、留言、歷程與稽核紀錄則可能需要保留，因此目前多採 Restrict 或 NoAction。否則刪掉一個帳號，相關操作紀錄也一起消失，日後很難追查發生過什麼事。
索引可以加快查詢，但會占用空間，也會增加新增與更新資料的成本，所以不是每個欄位都要加。工作項目列表經常依專案、狀態與交付期限篩選，TaskItems 因此建立 (ProjectId, Status, Deadline) 複合索引；查詢指派對象時則使用 AssignedAccountId 索引。留言常依工作項目與建立時間讀取，對應的索引是 (TaskItemId, CreatedAt)。
這些索引來自實際查詢，而不是憑感覺建立。功能上線後仍要觀察執行計畫與慢查詢，再決定要新增、調整或移除索引。
多人協作系統很常出現兩位使用者同時編輯同一筆 Task 的情況。TaskItems、Projects 與留言都有 rowversion 欄位；每次資料異動，SQL Server 會產生新的版本值。更新時若版本已經不同，後端便能回報資料曾被其他人修改，避免後送出的請求默默蓋掉前一個人的結果。
要注意，rowversion 是版本用的二進位數字，不是修改日期。若畫面需要顯示「最後更新時間」，仍應另外保存 UpdatedAt。
把前面的判斷放進 Project Management Web 後，目前的資料庫共有 21 張資料表。Day8 先用帳號、專案、專案成員與工作項目說明主要關係，實際開發時還得補上登入授權、專案角色、留言、歷程與系統紀錄。為了讓結構比較容易閱讀，我把 E-R Diagram 分成三個區塊：
| 區塊 | 主要資料表 | 負責的事情 |
|---|---|---|
| Identity 與 RBAC | Accounts、Roles、AccountRoles、Functions、RoleFunctions、RefreshTokens、UserPreferences |
保存帳號、系統角色、功能權限、登入 Token 與使用者偏好 |
| 專案、成員與 Task | Projects、ProjectMembers、ProjectRoles、ProjectMemberRoles、TaskItems、TaskItemComments、TaskItemHistories |
保存專案的主要業務資料，以及成員、工作項目、留言和異動歷程 |
| 系統紀錄 | BusinessCodeCounters、AuditLogs、EmailMessages |
產生 Project／Task 編號，記錄資料異動與 Email 寄送結果 |
第一個區塊處理「這個人是誰，以及他能做什麼」。Accounts 與 Roles 透過 AccountRoles 連接，再由 RoleFunctions 決定角色具有哪些功能權限。UserPreferences 則是一對零或一的關係，因為帳號可以還沒有建立偏好設定；這也呼應前言提到的批次確認選項，它屬於使用者設定，不是 Task 本身的資料。
第二個區塊是專案功能的主體。Projects 透過 ProjectMembers 連接參與專案的帳號，成員再經由 ProjectMemberRoles 取得一個或多個專案角色。這裡刻意把「系統角色」與「專案角色」分開：某個帳號在系統中可能只是一般使用者，但在特定專案裡可以同時擔任後端工程師與系統分析師。
TaskItems 一定屬於一個 Project，也會記錄建立者與被指派的帳號。每個 Task 可以有多筆 TaskItemComments 與 TaskItemHistories；留言保存討論內容，歷程則保存操作者、動作與當時的 JSON 快照。即使 Task 後來又被修改，系統仍能回頭查到先前發生過的事情。
最後一個區塊沒有硬塞進專案主線。BusinessCodeCounters 用 (CodeType, BusinessDate) 複合主鍵管理每天的 Project／Task 流水號；AuditLogs 可以記錄帳號操作，也允許 ActorAccountId 為空，保留系統自動執行的紀錄。EmailMessages 只保存收件者與寄送狀態，目前沒有對 Accounts 建立 Foreign Key，因為寄信紀錄的生命週期不必依附帳號資料。
E-R Diagram 適合先看全貌，確認資料表之間是一對一、一對多，還是透過中介表形成多對多；Table Schema 則用來核對欄位型別、Null、PK、FK、索引與刪除規則。兩份文件要一起看，才不會只知道資料表有連線，卻不知道那條線受到哪些約束。正式 Schema 仍以 EF Core migrations 為準，文件的用途是讓設計更容易閱讀，也方便前後端討論資料契約。
走到這裡，Day8 提到的關聯式資料庫概念已經變成可以實作的 Schema。下一步就是把資料與商業規則包在後端 API 裡，再交給 Vue 前端使用，這也正好接到 Day10 的前後端分離。
好的資料庫設計不是資料表越多越好，而是每張表的責任清楚，關係能表達業務，重要規則也有約束保護。Project Management 的 Schema 從 User Story 出發，把短暫的畫面狀態留在前端，把需要長期保存的專案、成員、工作項目、偏好與歷程交給資料庫。之後畫面或 API 改版時，核心資料不必跟著全部重做，程式才有調整的空間。
下一篇會沿著這個基礎，說明 Vue 前端與 ASP.NET Core 後端如何透過 API 分工，又該怎麼守住彼此的資料契約。
iThome鐵人賽
看影片追技術