SQL中如何最佳表示互相关联的产品与门店数据？

这是典型的多对多关联关系，最佳实践是用三张表建模：独立存储产品、门店的基础信息表，再加一张关联表记录两者的对应关系，既能避免数据冗余，又能保证数据一致性。

1. 产品表（products） #

存储产品的核心信息，每个产品仅保留一条唯一记录。

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT, -- 产品唯一标识主键
    product_name VARCHAR(50) NOT NULL UNIQUE -- 产品名称，确保不重复
);

对应示例数据的表内容：

2. 门店表（stores） #

存储门店的核心信息，每个门店仅保留一条唯一记录。

CREATE TABLE stores (
    store_id INT PRIMARY KEY AUTO_INCREMENT, -- 门店唯一标识主键
    store_name VARCHAR(50) NOT NULL UNIQUE -- 门店名称，确保不重复
);

对应示例数据的表内容：

3. 关联表（product_store） #

专门记录产品与门店的售卖关联，每条记录代表一个产品在某门店上架。通过外键关联产品表和门店表，同时设置联合主键避免重复记录。

CREATE TABLE product_store (
    product_id INT NOT NULL,
    store_id INT NOT NULL,
    PRIMARY KEY (product_id, store_id), -- 联合主键，防止同一产品-门店组合重复
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (store_id) REFERENCES stores(store_id) ON DELETE CASCADE
);

对应示例数据的部分记录：

常用查询示例 #

• 查询某产品（如Apples）的售卖门店：

SELECT s.store_name
FROM products p
JOIN product_store ps ON p.product_id = ps.product_id
JOIN stores s ON ps.store_id = s.store_id
WHERE p.product_name = 'Apples';

• 查询某门店（如StoreF）的在售产品：

SELECT p.product_name
FROM stores s
JOIN product_store ps ON s.store_id = ps.store_id
JOIN products p ON ps.product_id = p.product_id
WHERE s.store_name = 'StoreF';

设计优势 #

• 无冗余：产品、门店的基础信息仅存储一次，避免了把门店列表塞进产品字段的重复存储问题。
• 易维护：修改产品或门店信息时，仅需更新对应表的单条记录，关联关系不受影响。
• 高灵活：新增产品、门店或关联关系时，直接插入新记录即可，无需修改表结构。

内容的提问来源于stack exchange，提问作者On The Net Again