機房收費系統數據庫設計實戰（二）：從 ER 圖到 3NF 規範表結構落地

機房收費系統數據庫設計實戰（二）：從 ER 圖到 3NF 規範表結構落地

接上篇需求分析與 ER 建模，本文詳細記錄如何將 10 個關係模型轉換為 9 張符合第三範式 (3NF) 的物理表，重點講解數據類型選型原則、冗余消除策略、主外鍵設計及最終表結構展示。

一、關係模型到物理表的映射決策

原 10 個關係模型：用戶、學生、卡、基本數據、電腦、賬單、工作記錄、充值、退卡、上機。

• 電腦關係（電腦名、系統時間、系統日期）僅作為客戶端上下文信息，**不建表**，由應用層動態獲取。
• 其餘 9 個關係**全部建表**，並通過主外鍵建立關聯。

二、三大範式回顧與遵循策略

1. 1NF（原子性）：每列不可再分，無重復列、無多值列。所有字段嚴格拆分為單一屬性。
2. 2NF（完全依賴）：非主屬性完全依賴主鍵（針對聯合主鍵表）。本系統多為單列主鍵，天然滿足。
3. 3NF（無傳遞依賴）：非主屬性不依賴其他非主屬性。**核心手段：拆表、建外鍵**。

三、舊表問題剖析與重構

1. OnLine_Info 表冗余

主鍵 CardNo，但包含 CardType、StudentName、Department、Sex 等學生/卡屬性。完全重復 Student_Info 信息。重構：刪除冗余列，保留 CardNo (FK) 關聯 Student_Info、Card_Info。

2. Student_Info 表「大而全」反模式

原表融合學生基本信息、卡屬性、上機狀態、計費參數。違背「單一職責」原則，導致：

• 修改卡信息誤傷學生信息。
• 查詢學生信息、卡信息、計費、結賬全掃描同一大表，性能差、鎖衝突嚴重。

重構：拆分為 Student_Info（學生核心屬性）、Card_Info（卡屬性）、Online_Info（上機狀態）、Recharge_Info（充值記錄） 等獨立表。

四、數據類型選型實戰總結

關鍵經驗：密碼字段可能包含空格，**禁用 NCHAR/CHAR**，用 VARCHAR/NVARCHAR 避免 Trim 截斷空格導致驗證失敗。

五、最終 9 張表結構展示

數據庫：Restructurecharge

1. User_Info（操作員/管理員）

UserID (PK, CHAR(10))
UserName (NVARCHAR(20))
Password (VARCHAR(20))  -- 允許空格
Role (TINYINT)          -- 0:管理員 1:操作員
Status (BIT)            -- 是否啟用

2. Card_Info（IC 卡信息）

CardNo (PK, CHAR(15))
CardType (TINYINT)      -- 0:月卡 1:次卡 2:儲值卡
Balance (MONEY)         -- 餘額
Status (TINYINT)        -- 0:正常 1:掛失 2:註銷
CreateTime (DATETIME)

3. Student_Info（學生核心信息）

StudentID (PK, CHAR(12))
StudentNo (VARCHAR(20)) -- 學號
Name (NVARCHAR(20))
Sex (NCHAR(2))
Nation (NVARCHAR(20))
BirthPlace (NVARCHAR(50))
HomeAddress (NVARCHAR(100))
CellPhone (VARCHAR(20))
Email (VARCHAR(50))
PoliticalStatus (NVARCHAR(10))
EntryTime (DATETIME)
ClassID (FK, CHAR(8))   -- 關聯 BasicClassEntities
RoomID (FK, CHAR(8))    -- 關聯 BasicRoomEntities
DirectionID (FK, CHAR(8)) -- 關聯 BasicDirectionEntities
CardNo (FK, CHAR(15))   -- 關聯 Card_Info，一對一
IsEnabled (BIT)

4. Recharge_Info（充值記錄）

RechargeID (PK, INT IDENTITY)
CardNo (FK, CHAR(15))
Amount (MONEY)
RechargeTime (DATETIME)
OperatorID (FK, CHAR(10))
Note (NVARCHAR(200))

5. Online_Info（上機記錄/當前在線）

OnlineID (PK, INT IDENTITY)
CardNo (FK, CHAR(15))
LoginTime (DATETIME)
LogoutTime (DATETIME)   -- NULL 表示在線中
Duration (INT)          -- 秒數，下機時計算
Fee (MONEY)             -- 本次消費
Status (TINYINT)        -- 0:在線 1:已下機 2:異常

6. LogoffCard_Info（退卡記錄）

LogoffID (PK, INT IDENTITY)
CardNo (FK, CHAR(15))
RefundAmount (MONEY)
LogoffTime (DATETIME)
OperatorID (FK, CHAR(10))
Reason (NVARCHAR(200))

7. Worklog_Info（操作員工作日誌）

LogID (PK, INT IDENTITY)
OperatorID (FK, CHAR(10))
Action (NVARCHAR(50))   -- 充值、退卡、開機、關機等
ActionTime (DATETIME)
Detail (NVARCHAR(500))

8. BasicDate_Info（基本費率/時段配置）

ConfigID (PK, INT IDENTITY)
ConfigName (NVARCHAR(50))
RatePerHour (MONEY)
StartTime (TIME)
EndTime (TIME)
IsActive (BIT)

9. Bill_Info（賬單/結算記錄）

BillID (PK, INT IDENTITY)
CardNo (FK, CHAR(15))
BillDate (DATE)
TotalFee (MONEY)
TotalDuration (INT)
Status (TINYINT)        -- 0:未結算 1:已結算
SettleTime (DATETIME)
OperatorID (FK, CHAR(10))

六、關鍵外鍵關係圖

Student_Info (ClassID)  --> BasicClassEntities (ClassID)
Student_Info (RoomID)   --> BasicRoomEntities (RoomID)
Student_Info (DirectionID) --> BasicDirectionEntities (DirectionID)
Student_Info (CardNo)   --> Card_Info (CardNo)  [1:1]
Card_Info (CardNo)      <-- Online_Info (CardNo) [1:N]
Card_Info (CardNo)      <-- Recharge_Info (CardNo) [1:N]
Card_Info (CardNo)      <-- LogoffCard_Info (CardNo) [1:N]
Card_Info (CardNo)      <-- Bill_Info (CardNo) [1:N]
User_Info (UserID)      <-- Worklog_Info (OperatorID) [1:N]
User_Info (UserID)      <-- Recharge_Info/LogoffCard_Info/Bill_Info (OperatorID) [1:N]

七、總結

通過嚴格遵循 3NF 拆分大表、根據業務語義選型數據類型、建立清晰的主外鍵關係，最終獲得 9 張結構清晰、無冗余、可擴展的核心業務表。這不僅是「建表」的過程，更是**將業務邏輯精準映射為數據結構**的建模訓練。下一步將基於此結構設計存儲過程、觸發器、視圖及應用層 DAL，構建完整數據訪問層。