如何规范化无主键的Table2，解决与Table1的关联查询问题？

现有问题分析 #

当前Table2存在两个核心问题：

1. 数据冗余：同一Device对应的Type被重复存储（一个设备只会属于一种类型），违反数据库第一范式；
2. 无主键导致数据一致性差：没有唯一标识记录的字段，无法避免重复的Device+color组合，关联查询时会因一对多关系返回大量重复数据，既无法精准定位单条颜色信息，还拖慢查询速度。

具体规范化步骤 #

1. 拆分出设备主表（DeviceMaster）

把设备的唯一属性抽离出来，创建独立主表，结构如下：

CREATE TABLE DeviceMaster (
    Device VARCHAR(64) PRIMARY KEY, -- 与Table1的Device字段关联
    Type VARCHAR(32) NOT NULL -- 设备类型，比如手机、平板
);

将原Table2中每个Device对应的唯一Type值插入到这个表中，确保每个Device只存一次Type，消除冗余。

2. 创建设备颜色价格子表（DeviceColorPrice）

专门存储设备的颜色与价格信息，结构如下：

CREATE TABLE DeviceColorPrice (
    Id INT AUTO_INCREMENT PRIMARY KEY, -- 唯一主键，确保每条记录可唯一标识
    Device VARCHAR(64) NOT NULL,
    Color VARCHAR(32) NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (Device) REFERENCES DeviceMaster(Device), -- 关联设备主表
    UNIQUE KEY idx_device_color (Device, Color) -- 避免同一设备的同一颜色重复存储
);

将原Table2中所有Device+Color+Price的组合插入到这个表中，通过UNIQUE KEY约束确保同一设备的同一颜色不会重复录入。

3. 优化查询索引

• 给Table1的Device字段添加索引：

  CREATE INDEX idx_table1_device ON Table1(Device);
  

• 设备颜色价格子表的Device字段已通过外键自动建立索引，若经常按颜色查询，可额外添加Color字段的索引：

  CREATE INDEX idx_color ON DeviceColorPrice(Color);
  

关联查询示例 #

如果需要获取用户对应的设备所有颜色信息，可使用以下SQL：

SELECT t1.Id, t1.Name, dcp.Color, dcp.Price
FROM Table1 t1
JOIN DeviceMaster dm ON t1.Device = dm.Device
JOIN DeviceColorPrice dcp ON dm.Device = dcp.Device;

如果需要获取设备的默认颜色，可在DeviceMaster中新增DefaultColor字段，或者在DeviceColorPrice中添加IsDefault标识字段，查询时过滤该条件即可精准获取单一颜色信息。

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