复合键表的一对多关系设置及运费价格录入方案咨询

一、为Table1的Zone+Weight组合添加对应价格的方法 #

1. 数据库层面关联操作 #

利用Table1的UID作为唯一标识，将其与Table2的UID字段关联，即可实现价格与组合的绑定：

• 单条插入示例：

INSERT INTO Table2 (UID, `UPS Price`, `USPS Price`, `FEDEX Price`)
VALUES ('t1_uid_001', 12.5, 10.2, 13.0);

• 批量更新/导入示例（从临时价格表同步）：

UPDATE Table2 t2
JOIN Table1 t1 ON t2.UID = t1.UID
SET t2.`UPS Price` = temp.ups_price,
    t2.`USPS Price` = temp.usps_price,
    t2.`FEDEX Price` = temp.fedex_price
FROM temp_price_list temp
WHERE t1.Zone = temp.zone AND t1.Weight = temp.weight;

2. 业务系统层面流程 #

在录入Zone和Weight生成Table1记录后，直接在同一流程中录入三家承运商价格，系统自动将Table1的UID同步到Table2完成插入，确保数据一致性。

二、复合键表的一对多关系建立 #

1. 基于UID的一对多（推荐） #

直接将Table2的UID设为外键，关联Table1的UID字段，实现一个Zone+Weight组合对应多条价格记录（支持价格版本迭代）：

ALTER TABLE Table2
ADD CONSTRAINT fk_table2_table1_uid
FOREIGN KEY (UID) REFERENCES Table1(UID);

2. 基于复合主键的一对多 #

若不想依赖UID，直接用Zone和Weight作为外键关联，贴合业务逻辑：

ALTER TABLE Table2
ADD COLUMN Zone INT,
ADD COLUMN Weight DECIMAL(10,2),
ADD CONSTRAINT fk_table2_table1_compound
FOREIGN KEY (Zone, Weight) REFERENCES Table1(Zone, Weight);

三、更优实现方案 #

方案1：单表存储（适合无价格版本需求的场景） #

将价格字段直接合并到Table1，简化结构，避免跨表关联：

CREATE TABLE ShippingRates (
    Zone INT NOT NULL,
    Weight DECIMAL(10,2) NOT NULL,
    UPS_Price DECIMAL(10,2),
    USPS_Price DECIMAL(10,2),
    FEDEX_Price DECIMAL(10,2),
    PRIMARY KEY (Zone, Weight)
);

优势：查询效率高，维护成本低，结构直观。

方案2：拆分式存储（适合需扩展承运商或记录价格历史的场景） #

拆分为三张表，提升扩展性：

1. ShippingCombinations：存储唯一的Zone+Weight组合

CREATE TABLE ShippingCombinations (
    Zone INT NOT NULL,
    Weight DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (Zone, Weight)
);

2. Carriers：存储承运商信息，方便新增

CREATE TABLE Carriers (
    CarrierID INT AUTO_INCREMENT PRIMARY KEY,
    CarrierName VARCHAR(50) NOT NULL UNIQUE
);

3. ShippingPrices：存储组合-承运商的对应价格，支持生效日期

CREATE TABLE ShippingPrices (
    Zone INT NOT NULL,
    Weight DECIMAL(10,2) NOT NULL,
    CarrierID INT NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    EffectiveDate DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (Zone, Weight, CarrierID, EffectiveDate),
    FOREIGN KEY (Zone, Weight) REFERENCES ShippingCombinations(Zone, Weight),
    FOREIGN KEY (CarrierID) REFERENCES Carriers(CarrierID)
);

优势：新增承运商无需修改表结构，可轻松记录价格历史版本，适配长期业务扩展。

内容的提问来源于stack exchange，提问作者user4831894