Java 圖書銷售系統：數據庫結構設計與建表腳本全解析

本文詳細解讀圖書銷售系統的 MySQL 數據庫設計，包含 5 張核心表結構、外鍵約束、索引策略、初始化數據與擴展性考量，為系統穩健運行提供數據層基石。

ER 圖核心關聯

User (1) ─────< (N) Order (1) ─────< (N) OrderBook (N) ─────> (1) Book
                    │
                    └─────< (N) BookStockHistory
Book (1) ─────< (N) BookStockHistory

五張核心表結構詳解

1. t_user - 用戶表

CREATE TABLE `t_user` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水號',
  `username` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '用戶名',
  `password` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT '密碼(BCrypt雜湊)',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

設計要點：用戶名唯一索引、密碼存儲雜湊值 (長度 60+ 適配 BCrypt)、InnoDB 支持事務。

2. t_book - 圖書主表

CREATE TABLE `t_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水號',
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT '書名',
  `price` decimal(10,2) NOT NULL COMMENT '售價',
  `quantity` int(11) NOT NULL COMMENT '當前庫存',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

設計要點：書名唯一防重複、價格 decimal(10,2) 避免浮點誤差、庫存冗餘字段提升查詢性能 (由觸發器/應用層同步)。

3. t_order - 訂單主表

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水號',
  `UserId` int(11) NOT NULL COMMENT '會員編號',
  `Amount` decimal(10,0) NOT NULL COMMENT '總金額(分)',
  `Datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '下單時間',
  `Note` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '備註',
  PRIMARY KEY (`id`),
  KEY `UserId` (`UserId`),
  CONSTRAINT `t_order_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `t_user` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

設計要點：金額存「分」(decimal(10,0)) 避免精度問題、外鍵保證引用完整性、時間戳自動更新。

4. t_orderbook - 訂單明細表

CREATE TABLE `t_orderbook` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水號',
  `OrderId` int(11) NOT NULL COMMENT '訂單號',
  `BookId` int(11) NOT NULL COMMENT '圖書編號',
  `Quantity` int(11) NOT NULL COMMENT '購買數量',
  PRIMARY KEY (`Id`),
  KEY `OrderId` (`OrderId`),
  KEY `BookId` (`BookId`),
  CONSTRAINT `t_orderbook_ibfk_1` FOREIGN KEY (`OrderId`) REFERENCES `t_order` (`id`),
  CONSTRAINT `t_orderbook_ibfk_2` FOREIGN KEY (`BookId`) REFERENCES `t_book` (`id`)

設計要點：雙外鍵維護關聯完整性、雙索引支援正反向查詢、無冗餘價格字段 (歷史價格可從 BookStockHistory 獲取)。

5. t_bookstockhistory - 庫存變動歷史表

CREATE TABLE `t_bookstockhistory` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水號',
  `bookId` int(11) NOT NULL COMMENT '圖書編號',
  `quantity` int(11) NOT NULL COMMENT '本次變動數量(正入負出)',
  `quantityOutstanding` int(11) NOT NULL COMMENT '變動後結存',
  `price` decimal(10,2) NOT NULL COMMENT '當時單價',
  `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '操作時間',
  `note` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '備註(入庫/銷售/調整)',
  PRIMARY KEY (`id`),
  KEY `bookId` (`bookId`),
  CONSTRAINT `t_bookstockhistory_ibfk_1` FOREIGN KEY (`bookId`) REFERENCES `t_book` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

設計亮點：

• 完整審計軌跡：每次庫存變動留痕 (數量、結存、單價、時間、備註)
• 結存冗餘：quantityOutstanding 避免彙總計算、支持時間點查詢
• 價格快照：price 記錄操作時單價，支援成本核算、利潤分析
• 業務語義化：note 區分入庫/銷售/盘虧/調撥等業務類型

初始化測試數據

-- 圖書樣本
INSERT INTO `t_book` VALUES 
(1, 'Java基礎12', 25.01, 0),
(2, 'Java高級編程', 89.23, 2),
(3, '跟老紫竹學Java', 50.45, 3),
(4, 'Java大全', 111.00, 4);

-- 用戶樣本 (密碼為明文僅供測試，生產必須加密)
INSERT INTO `t_user` VALUES 
(1, 'admin', 'admin'),
(2, 'user', 'user'),
(3, 'test', 'test');

-- 訂單樣本
INSERT INTO `t_order` VALUES 
(7, 2, 2238, '2008-07-25 11:33:27', NULL),
(8, 2, 165, '2008-07-25 11:52:38', NULL),
... (共 6 筆訂單)

-- 訂單明細
INSERT INTO `t_orderbook` VALUES 
(5, 7, 1, 11), (6, 7, 2, 22), ... (共 10 筆明細)

-- 庫存歷史
INSERT INTO `t_bookstockhistory` VALUES 
(1, 3, 1, 1, 50.45, '2008-07-25 12:27:21', NULL),
(2, 2, 1, 99, 89.23, '2008-07-25 12:29:18', NULL),
... (共 4 筆記錄)

索引與性能優化策略

擴展性設計預留

• 分表策略：t_order / t_orderbook 按月分表 (order_202401)、t_bookstockhistory 按季度分表
• 讀寫分離：主庫寫入、從庫讀取 (訂單列表、報表查詢)
• 緩存層：Redis 緩存熱門圖書、用戶會話、庫存餘量
• 數據歸檔：完成狀態訂單 > 1 年歸檔至冷存儲
• 審計增強：引入 Canal 監聽 Binlog 同步至 Elasticsearch/ClickHouse 做全文搜索/OLAP

範式與反範式權衡

當前設計遵循 3NF，優點：數據一致性強、存儲效率高、更新無異常。缺點：多表關聯查詢複雜。關鍵反範式點：

• t_book.quantity 冗餘當前庫存 (避免每次 SUM 歷史表)
• t_order.Amount 冗餘總金額 (避免每次 JOIN 明細求和)
• t_bookstockhistory.quantityOutstanding 冗餘結存 (O(1) 時間點查詢)

此為經典「讀多寫少」電商場景的合理反範式優化。

嚴謹的數據庫設計是系統穩定性的基石。本結構在滿足核心業務的同時，通過審計表、冗餘字段、索引策略為性能、擴展、合規預留充足空間。