SQL 多對多關係建表：管理員與倉庫關聯表設計

需求

一個管理員可管理多個倉庫，一個倉庫可由多個管理員管理（多對多）。

標準三表設計

-- 管理員表
CREATE TABLE admin (
    admin_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(100) NOT NULL,
    real_name VARCHAR(50),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 倉庫表
CREATE TABLE warehouse (
    warehouse_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200),
    manager_id INT, -- 可選：主要負責人
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 關聯表（核心）
CREATE TABLE admin_warehouse (
    admin_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by INT, -- 誰分配的
    PRIMARY KEY (admin_id, warehouse_id), -- 聯合主鍵，防止重複關聯
    FOREIGN KEY (admin_id) REFERENCES admin(admin_id) ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouse(warehouse_id) ON DELETE CASCADE
);

關鍵點

• 聯合主鍵：(admin_id, warehouse_id) 確保同一對關係唯一。
• 外鍵約束：級聯刪除保證引用完整性（或設置 SET NULL 視業務而定）。
• 擴展字段：assigned_at, assigned_by 記錄授權審計信息。
• 查詢示例：
  • 某管理員管轄倉庫：SELECT w.* FROM warehouse w JOIN admin_warehouse aw ON w.warehouse_id=aw.warehouse_id WHERE aw.admin_id=?
  • 某倉庫的管理員：SELECT a.* FROM admin a JOIN admin_warehouse aw ON a.admin_id=aw.admin_id WHERE aw.warehouse_id=?

避免錯誤

• 不要在 admin 或 warehouse 表中加數組/逗號分隔字符串存儲關係（違反 1NF，查詢維護極難）。
• 不要用單獨自增 ID 作為關聯表主鍵（除非需要單獨引用關係記錄），聯合主鍵更自然高效。