您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

學生宿舍管理系統資料庫設計實戰：從概念模型到完整 DDL 腳本

編輯：關於SqlServer

專案背景

暑期實作專案，團隊開發學生宿舍管理系統。資料庫設計雖非核心難點，但完整性、約束正確性、效能考量直接決定後端開發順暢度。本文整理完整 DDL 並解析關鍵設計決策。

核心實體關係 (ER 精簡)

宿舍 (Dormitory) 1:N 房間 (Room) → 1:N 床位 (Bed)
學生 (Student) N:1 班級 (Class) N:1 學院 (College)
學生 1:1 床位 (入住關係)
違紀 (Violation)、訪客 (Visitor)、拜訪記錄 (VisitLog)、衛生評比 (HygieneScore)、報修 (Repair)、水電費 (UtilityBill)、宿管員 (DormAdmin) 等業務實體

關鍵 DDL 片段與設計說明

1. 宿舍/房間/床位層級結構

CREATE TABLE Dormitory (
    DormID      VARCHAR(8)  PRIMARY KEY,  -- 建議改為樓號+房號+床號複合鍵或代理鍵
    BuildingNo  VARCHAR(5)  NOT NULL,
    RoomNo      CHAR(3)     NOT NULL,
    Facilities  VARCHAR(50) NOT NULL,
    Spec        VARCHAR(20) NOT NULL,
    Capacity    INT         NOT NULL CHECK (Capacity > 0),
    Occupied    INT         NOT NULL DEFAULT 0 CHECK (Occupied >= 0),
    Gender      CHAR(2)     NOT NULL CHECK (Gender IN ('男','女')),
    College     VARCHAR(20) NOT NULL,
    Status      VARCHAR(10) NOT NULL,     -- 應加 CHECK (Status IN ('空閒','部分滿','已滿','維修中'))
    Phone       VARCHAR(15) NOT NULL,
    CONSTRAINT CHK_Occupied_Capacity CHECK (Occupied <= Capacity)
);

改進點：原設計以 DormID 為單一主鍵，語意模糊。建議拆分樓棟、房間、床位三層表，或採用代理鍵 + 複合唯一鍵 (BuildingNo, RoomNo, BedNo)。

2. 學生表與關聯約束

CREATE TABLE Student (
    StudentID   CHAR(11)    PRIMARY KEY CHECK (StudentID LIKE 'N[0-9]%'),
    DormID      VARCHAR(8)  NOT NULL REFERENCES Dormitory(DormID),
    Name        NVARCHAR(20) NOT NULL,
    Gender      CHAR(2)     NOT NULL CHECK (Gender IN ('男','女')),
    BedNo       INT         NOT NULL DEFAULT 0 CHECK (BedNo >= 0),
    ClassNo     VARCHAR(4)  NOT NULL REFERENCES Class(ClassNo),
    CollegeNo   VARCHAR(4)  NOT NULL REFERENCES College(CollegeNo),
    Address     NVARCHAR(40) NOT NULL,
    Phone       VARCHAR(15) NOT NULL CHECK (Phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]%'),
    Remark      NVARCHAR(100)
);

3. 歷程/記錄類表：使用 IDENTITY 代理鍵 + 業務時間欄位

CREATE TABLE Violation (
    ViolationID INT IDENTITY PRIMARY KEY,
    DormID      VARCHAR(8)  NOT NULL REFERENCES Dormitory(DormID),
    StudentID   CHAR(11)    NOT NULL REFERENCES Student(StudentID),
    Detail      NVARCHAR(50) NOT NULL,
    OccurTime   DATETIME    NOT NULL,          -- 原設計用 VARCHAR，建議 DATETIME
    Action      NVARCHAR(50) NOT NULL,
    ActionTime  DATETIME    NOT NULL,
    Remark      NVARCHAR(100),
    CONSTRAINT CHK_Action_After_Occur CHECK (ActionTime >= OccurTime)
);

4. 水電費表：複合主鍵 + 合理約束

CREATE TABLE UtilityBill (
    ReadDate    DATETIME    NOT NULL,
    DormID      VARCHAR(8)  NOT NULL REFERENCES Dormitory(DormID),
    WaterQuota  INT         NOT NULL CHECK (WaterQuota >= 0),
    WaterStart  INT         NOT NULL CHECK (WaterStart >= 0),
    WaterUsed   INT         NOT NULL CHECK (WaterUsed >= 0),
    WaterRate   SMALLMONEY  NOT NULL CHECK (WaterRate >= 0),
    ElecQuota   INT         NOT NULL CHECK (ElecQuota >= 0),
    ElecStart   INT         NOT NULL CHECK (ElecStart >= 0),
    ElecUsed    INT         NOT NULL CHECK (ElecUsed >= 0),
    ElecRate    SMALLMONEY  NOT NULL CHECK (ElecRate >= 0),
    Remark      NVARCHAR(100),
    CONSTRAINT PK_UtilityBill PRIMARY KEY (ReadDate, DormID),
    CONSTRAINT CHK_Water_Used CHECK (WaterUsed >= WaterStart),
    CONSTRAINT CHK_Elec_Used CHECK (ElecUsed >= ElecStart)
);

進階物件：檢視、預存程序、預設值

檢視：V_RepairLog (報修+房間資訊)、V_StudentResidence (學生+房間+班級+學院)、V_ResidenceChange (異動歷程) — 簡化前端查詢、支援報表。
登入驗證預存程序：CheckLoginGetFlag_SP 回傳權限旗標 (0/1/管理員等)。
預設值綁定：sp_bindefault 為數值欄位設 0、字串欄位設 '0-0-000'，減少應用層判斷。

設計反思與改進建議

原設計問題	影響	改進方向
主鍵語意不清 (DormID 單欄)	難以擴充多校區、多樓棟	採用代理鍵 + 複合業務鍵 (Building, Floor, Room, Bed)
時間欄位用 VARCHAR	無法索引、排序、日期運算錯誤	全面改用 DATETIME/DATETIME2
CHECK 約束缺失 (如 Status 無限制值)	髒資料風險	補齊所有枚舉型欄位 CHECK
外鍵命名混亂 (不存在此類型編號...)	維護困難、錯誤訊息不可讀	統一命名：FK_ChildTable_ParentTable_Column
缺乏索引策略	查詢效能隱憂	針對 FK、常用 WHERE/ORDER BY 欄位建立非叢集索引