您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 程式設計綜合問答

資料庫設計模式：一對多關聯實現購物訂單與明細表結構

編輯：程式設計綜合問答

業務場景分析

一筆購物記錄（訂單）包含多種商品，商品資訊獨立維護於商品表。這是經典的一對多（1:N）關聯：一個訂單對應多筆明細，每筆明細引用一個商品。

標準三表設計

1. 訂單主表

CREATE TABLE Orders (
    OrderID     INT PRIMARY KEY AUTO_INCREMENT,
    UserID      INT NOT NULL,
    OrderDate   DATETIME DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(10,2),
    Status      ENUM('pending','paid','shipped','completed','cancelled') DEFAULT 'pending',
    ShippingAddr VARCHAR(255),
    Remark      TEXT
);

2. 訂單明細表

CREATE TABLE OrderDetails (
    DetailID    INT PRIMARY KEY AUTO_INCREMENT,
    OrderID     INT NOT NULL,
    ProductID   INT NOT NULL,
    Quantity    INT NOT NULL DEFAULT 1,
    UnitPrice   DECIMAL(10,2) NOT NULL,  -- 下單時價格快照
    Subtotal    DECIMAL(10,2) GENERATED ALWAYS AS (Quantity * UnitPrice) STORED,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

3. 商品表

CREATE TABLE Products (
    ProductID   INT PRIMARY KEY AUTO_INCREMENT,
    Name        VARCHAR(100) NOT NULL,
    Description TEXT,
    Price       DECIMAL(10,2) NOT NULL,
    Stock       INT DEFAULT 0,
    CategoryID  INT,
    IsActive    BOOLEAN DEFAULT TRUE
);

關鍵設計原則

原則	實作方式	效益
價格快照	OrderDetails 存 UnitPrice	商品漲跌不影響歷史訂單金額
級聯刪除	ON DELETE CASCADE	刪除訂單自動清理明細，避免孤兒資料
冗餘小計	GENERATED COLUMN 或觸發器	查詢效能優化，避免重複計算
索引優化	OrderDetails(OrderID), OrderDetails(ProductID)	加速關聯查詢與統計

常見查詢範例

查詢完整訂單明細

SELECT o.OrderID, o.OrderDate, p.Name, od.Quantity, od.UnitPrice, od.Subtotal
FROM Orders o
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderID = 12345;

統計商品銷售排行

SELECT p.Name, SUM(od.Quantity) AS TotalSold, SUM(od.Subtotal) AS Revenue
FROM OrderDetails od
JOIN Products p ON od.ProductID = p.ProductID
JOIN Orders o ON od.OrderID = o.OrderID
WHERE o.Status IN ('paid','shipped','completed')
GROUP BY p.ProductID, p.Name
ORDER BY TotalSold DESC
LIMIT 10;

擴展場景：購物車轉訂單

-- 交易確保原子性
START TRANSACTION;

INSERT INTO Orders (UserID, TotalAmount, ShippingAddr) 
VALUES (1001, 0, '台北市信義區...');  -- 先建訂單取得 OrderID

SET @order_id = LAST_INSERT_ID();

INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
SELECT @order_id, ProductID, Quantity, Price  -- 從購物車/商品表取價
FROM CartItems WHERE UserID = 1001;

UPDATE Orders SET TotalAmount = (
    SELECT SUM(Subtotal) FROM OrderDetails WHERE OrderID = @order_id
) WHERE OrderID = @order_id;

DELETE FROM CartItems WHERE UserID = 1001;

COMMIT;