資料模型實戰：從 MSSQL 設計到 .NET 8 WebAPI 實作（以刀具管理為例）

前言

在現代製造業或工廠數位化轉型中，「刀具管理」是一個具代表性的應用場景。透過資料庫模型設計、Entity Framework Core 整合與 API 建置，我們能快速打造一套具備 CRUD、保養紀錄與歷史查詢的完整系統。

本書將以 30 分鐘教學課程為藍本，帶領讀者從 MSSQL 資料庫設計開始，一步一步進入 .NET 8 的 WebAPI 開發世界，使用 EF Core Power Tools 自動產生模型，並實作實用的 API。

---

第 1 章：資料模型設計概論

1.1 資料模型的角色

資料模型是連結業務需求與資料結構的橋樑。它幫助我們：

• 定義資料如何儲存（資料表設計）

• 理解資料之間的關係（ERD 設計）

• 支援系統的資料存取與驗證需求

1.2 資料模型分類

• 概念性模型（Conceptual Model）：業務角度的抽象定義實體與關聯，例如「一把刀具可有多筆保養紀錄」。

• 邏輯模型（Logical Model）：資料表結構、欄位類型、關聯關係（ERD）。

• 實體模型（Physical Model）：實際在資料庫中的實作，例如實際的資料表定義、索引、資料型別。

[使用者需求訪談]
        ↓
[概念性模型]（Entities: 刀具、入庫、保養、使用紀錄）
        ↓
[邏輯模型 ERD]
（關聯關係：一對多、多對多）
        ↓
[實體模型]
（欄位設計、資料型別、主外鍵）
        ↓
[MSSQL 資料表實作]
（CREATE TABLE）
        ↓
[.NET 實體類別]
（EF Core Entity / DbContext）
        ↓
[DTO + 驗證 + API + 測試 + 觀察性]

1.3 資料模型設計原則

設計資料模型時應遵守以下原則：

• 正規化（Normalization）：消除資料冗餘，確保一致性（如第三正規化）

• 關聯一致性（Referential Integrity）：透過外鍵約束維持資料關聯的正確性

• 可擴展性（Scalability）：模型設計應容許未來擴充欄位或新關聯

• 命名一致性（Naming Consistency）：欄位名稱具描述性，資料型別合理

1.4 資料模型設計流程（步驟）

1. 需求蒐集：訪談使用者或主管，瞭解業務流程與資料項目

2. 定義實體與屬性：找出資料實體（如 Tool）、每個實體的屬性（如 Name, PurchaseDate）

3. 設計關聯：定義實體之間的關聯類型（如一對多）

4. 畫出 ERD：使用圖形工具（如 dbdiagram.io）呈現模型

5. 驗證與調整：與業務方對焦確認，是否符合實際應用

6. 轉換為資料表設計：將 ERD 對應至 MSSQL 資料表與欄位型別

1.5 刀具管理系統概念性模型

實體：

• Tool（刀具）：刀具的基本資料。

• ToolInbound（刀具入庫）：刀具何時入庫、來源、數量。

• ToolUsage（刀具使用紀錄）：紀錄使用時間、機台、人員。

• ToolMaintenance（刀具保養紀錄）：紀錄保養項目、保養人員與時間。

關聯：

• 一個 Tool 對應多個 Inbound、Usage、Maintenance

ERD 示意：

Tool
├── ToolInbound
├── ToolUsage
└── ToolMaintenance

關聯設計：

• 一把 Tool 可有多筆 ToolUsage → 一對多

• 一把 Tool 可有多筆 ToolMaintenance → 一對多

• 使用 ToolId 作為外鍵連結

ERD 示意圖（文字版）：

Tool (1) -------- (∞) ToolUsage
Tool (1) -------- (∞) ToolMaintenance

透過這樣的設計，我們可以：

• 快速查詢某把刀具的使用與保養紀錄

• 保證每筆紀錄都有對應的刀具資料（外鍵保護）

• 容許未來擴充屬性，例如刀具狀態、壽命指標等

1.6 工具與設計建議

• ERD 設計：使用 dbdiagram.io、draw.io、SSMS Diagram Tool

• 命名建議：實體用單數命名（Tool）、主鍵為實體名 + Id

• 優先定義核心資料表，次要資料表視需求擴充

---

第 2 章：MSSQL 資料表設計與關聯

2.1 資料表設計

CREATE TABLE Tool (
    ToolId INT PRIMARY KEY IDENTITY,
    ToolCode NVARCHAR(50) NOT NULL,
    ToolName NVARCHAR(100) NOT NULL,
    Spec NVARCHAR(200),
    CreatedAt DATETIME NOT NULL DEFAULT GETDATE()
);

CREATE TABLE ToolInbound (
    InboundId INT PRIMARY KEY IDENTITY,
    ToolId INT FOREIGN KEY REFERENCES Tool(ToolId),
    Quantity INT NOT NULL,
    InboundDate DATETIME NOT NULL,
    Source NVARCHAR(100)
);

CREATE TABLE ToolUsage (
    UsageId INT PRIMARY KEY IDENTITY,
    ToolId INT FOREIGN KEY REFERENCES Tool(ToolId),
    Machine NVARCHAR(50),
    Operator NVARCHAR(50),
    UsedHours DECIMAL(5,2),
    UsageDate DATETIME NOT NULL
);

CREATE TABLE ToolMaintenance (
    MaintenanceId INT PRIMARY KEY IDENTITY,
    ToolId INT FOREIGN KEY REFERENCES Tool(ToolId),
    MaintenanceDate DATETIME NOT NULL,
    Description NVARCHAR(200),
    Maintainer NVARCHAR(50)
);

2.2 關聯解釋

• 一把刀具 Tool 會有多筆使用紀錄與保養紀錄 → 一對多關係

• 使用外鍵約束確保資料完整性

• 可為 ToolUsage.Date、Tool.Name 建立索引加速查詢

2.3 資料表命名原則（Table Naming）

✅ 原則說明

原則	說明
使用單數名詞	如：Tool 而非 Tools，對應物件導向設計（Entity）概念
使用 PascalCase 或 Snake_Case	根據團隊慣例選擇一致風格，例如 ToolMaintenance 或 tool_maintenance
命名具備業務語意	使用業務語言描述（如 PurchaseOrder、ToolUsage）
不使用縮寫或語焉不詳詞	避免 TMP, VAL1, DATA2 等無意義命名

🔍 範例命名

業務對象	表名建議
刀具主檔	Tool
使用紀錄	ToolUsage
保養紀錄	ToolMaintenance
採購訂單	PurchaseOrder
工作單	WorkOrder

---

2.4 資料欄位命名原則（Column Naming）

✅ 原則說明

原則	說明
使用 PascalCase（C#）或 snake_case（SQL）	保持一致性，例如：ToolId / tool_id
主鍵命名為 TableName + Id	如 ToolId、OrderId，避免單一 Id 帶來混淆
外鍵應具備來源實體語意	如：ToolId（來自 Tool）
避免使用保留字	如 Date, User, Name 等，應改為具體如 PurchaseDate
布林欄位建議用 Is/Has 開頭	例如：IsActive, HasWarranty
時間類欄位建議用 Time/Date 結尾	例如：CreatedDate, LastModifiedTime

🔍 範例命名

欄位目的	命名建議
主鍵	ToolId
外鍵	ToolId, OrderId
名稱	Name
狀態	IsActive, Status
建立時間	CreatedDate
負責人員	MaintainedBy, Operator
數值欄位	UsageHours, Price, Quantity

---

2.6 命名一致性對 EF Core 的重要性

類型	建議命名格式	EF Core 行為
主鍵	EntityNameId	自動辨識為主鍵
導覽屬性	複數命名如 ToolUsages	對應一對多集合
外鍵欄位	EntityNameId	自動建立關聯與 FK

---

第 3 章：使用 EF Core Power Tools 映射資料模型

3.1 安裝 EF Core Power Tools

• Visual Studio → Extensions → Manage Extensions

• 搜尋 "EF Core Power Tools" 並安裝

• 重啟 Visual Studio

3.2 使用 Reverse Engineer 功能

1. 右鍵專案 → EF Core Power Tools → Reverse Engineer

2. 選擇資料庫連線 → 選擇 Tool, ToolUsage, ToolMaintenance

3. 設定輸出目錄（如 /Models/Entities）

4. 命名 DbContext 為 ToolDbContext

5. 選擇適合的選項（如使用 Fluent API、自動包含導覽屬性）

3.3 產出結果說明

• 自動產生三個 Entity 類別與一個 DbContext：

public partial class Tool
{
    public int ToolId { get; set; }
    public string Name { get; set; }
    public string ToolType { get; set; }
    public DateTime? PurchaseDate { get; set; }
    public bool IsActive { get; set; }

    public virtual ICollection<ToolUsage> ToolUsages { get; set; }
    public virtual ICollection<ToolMaintenance> ToolMaintenances { get; set; }
}

3.4 測試查詢

using var db = new ToolDbContext();
var tool = db.Tools.Include(t => t.ToolUsages).FirstOrDefault(t => t.ToolId == 1);

---

第 4 章：建立 WebAPI 並整合資料模型

4.1 建立 ASP.NET Core Web API 專案

dotnet new webapi -n ToolManagement.Api

4.2 設定 DI 與資料庫連線

在 Program.cs 註冊 DbContext：

builder.Services.AddDbContext<ToolDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

4.3 實作 Controller

以 ToolController 為例：

[ApiController]
[Route("api/[controller]")]
public class ToolController : ControllerBase
{
    private readonly ToolDbContext _context;
    public ToolController(ToolDbContext context) => _context = context;

    [HttpGet]
    public async Task<IEnumerable<Tool>> GetAll() => await _context.Tools.ToListAsync();

    [HttpPost]
    public async Task<IActionResult> Create(Tool tool)
    {
        _context.Tools.Add(tool);
        await _context.SaveChangesAsync();
        return Ok(tool);
    }
}

4.4 測試 API

• 使用 Swagger 自動產生測試介面

• 或用 Postman 測試 GET/POST 呼叫

---

第 5 章：進階開發實務補充

5.1 使用 DTO 與 AutoMapper 抽離資料模型

在實際開發中，我們不建議直接將 EF Core 的實體類別（Entities）公開給前端使用，原因包括資料洩漏風險、耦合性過高、不易維護等。

Data Transfer Object (DTO) 是一種介於資料層與展示層之間的中介結構，用於：

• 隱藏不必要欄位

• 控制資料流向（防止 over-posting）

• 提供多樣化輸出格式（如列表頁與詳情頁不同格式）

AutoMapper 可自動將 Entity 與 DTO 相互對應，簡化轉換程式碼：

public class ToolDto
{
    public string Name { get; set; }
    public string ToolType { get; set; }
    public bool IsActive { get; set; }
}

public class MappingProfile : Profile
{
    public MappingProfile()
    {
        CreateMap<Tool, ToolDto>();
        CreateMap<ToolDto, Tool>();
    }
}

在 Program.cs 中註冊 AutoMapper：

builder.Services.AddAutoMapper(typeof(Program));

5.2 驗證機制：FluentValidation 整合

FluentValidation 是一套用於建構強型別驗證規則的 .NET 套件，相較於 Data Annotations 更具彈性與可讀性。

安裝方式：

dotnet add package FluentValidation.AspNetCore

定義驗證規則：

public class ToolDtoValidator : AbstractValidator<ToolDto>
{
    public ToolDtoValidator()
    {
        RuleFor(x => x.Name).NotEmpty().WithMessage("名稱不可空白");
        RuleFor(x => x.ToolType).MaximumLength(50);
    }
}

在 Program.cs 註冊：

builder.Services.AddValidatorsFromAssemblyContaining<ToolDtoValidator>();

5.3 單元測試與整合測試（xUnit）

良好的系統需具備測試能力，包括：

• 單元測試（Unit Test）：測試個別邏輯，例如驗證規則、資料轉換

• 整合測試（Integration Test）：模擬 API 呼叫流程，測試資料庫互動

安裝必要套件：

dotnet add package xunit

範例測試類別：

public class ToolTests
{
    [Fact]
    public void Should_Create_Valid_Tool()
    {
        var tool = new Tool { Name = "Cutter", ToolType = "Drill", IsActive = true };
        Assert.Equal("Cutter", tool.Name);
    }
}

整合測試可使用 WebApplicationFactory 或 TestServer 建立 API 測試情境。

5.4 加入觀察性：OpenTelemetry + Serilog

現代應用應具備觀察性能力，包括追蹤、日誌與指標收集。

Serilog 紀錄日誌

dotnet add package Serilog.AspNetCore

設定 Serilog：

Log.Logger = new LoggerConfiguration()
    .WriteTo.Console()
    .CreateLogger();

builder.Host.UseSerilog();

OpenTelemetry 分散式追蹤

dotnet add package OpenTelemetry.Exporter.Zipkin

設定追蹤：

builder.Services.AddOpenTelemetryTracing(builder => builder
    .AddAspNetCoreInstrumentation()
    .AddEntityFrameworkCoreInstrumentation()
    .AddZipkinExporter(opt => opt.Endpoint = new Uri("http://localhost:9411/api/v2/spans")));

搭配 Zipkin/Grafana/Jaeger 可視覺化整個請求鏈路，有助除錯與效能監控。

---

結語

透過本書範例，我們實作了 MSSQL → EF Core → ASP.NET Core API 的資料模型整合流程。這不僅可套用於刀具管理，也適用於大多數的商業資料管理系統。