PostgreSQL数据库设计咨询：关联表VS单表及触发器使用疑问

一、选规范化关联数据库，别用单表 #

绝对不能用单表，原因很明确：

• 数据冗余严重：同一个品牌、材质会在多条产品记录里重复存储，修改品牌名称时要更新所有相关行，极易出错。
• 维护成本高：单表结构会随着业务扩展变得臃肿，后续新增属性或调整关系时，改动量极大。
• 查询效率低：数据量上来后，单表的过滤、聚合查询性能会急剧下降。

二、具体表结构设计 #

根据你的业务关系，设计5张核心表（如果产品仅对应单一材质，可合并ProductMaterials到Products表）：

1. Materials（材质表） #

存储所有材质类型，避免重复定义：

CREATE TABLE Materials (
    MaterialId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50) NOT NULL UNIQUE, -- 如"钢材"、"碳纤维"
    Description NVARCHAR(200) NULL -- 可选，材质描述
)

2. Brands（品牌表） #

存储品牌基础信息：

CREATE TABLE Brands (
    BrandId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL UNIQUE, -- 品牌名称
    EstablishmentDate DATE NULL -- 可选，品牌成立时间
)

3. Series（系列表） #

每个系列属于一个品牌，且唯一对应一个产品：

CREATE TABLE Series (
    SeriesId INT PRIMARY KEY IDENTITY(1,1),
    BrandId INT NOT NULL FOREIGN KEY REFERENCES Brands(BrandId),
    Name NVARCHAR(100) NOT NULL, -- 系列名称
    CONSTRAINT UQ_Series_Brand_Name UNIQUE(BrandId, Name) -- 同一品牌下系列名称唯一
)

4. Products（产品表） #

产品属于唯一系列，包含核心属性：

CREATE TABLE Products (
    ProductId INT PRIMARY KEY IDENTITY(1,1),
    SeriesId INT NOT NULL FOREIGN KEY REFERENCES Series(SeriesId) UNIQUE, -- 一个系列对应一个产品
    Length DECIMAL(10,2) NULL, -- 长度
    Color NVARCHAR(50) NULL, -- 颜色
    Price DECIMAL(18,2) NOT NULL, -- 价格
    CONSTRAINT UQ_Product_Series UNIQUE(SeriesId) -- 确保系列与产品一一对应
)

5. ProductMaterials（产品-材质关联表，可选） #

如果单个产品可能使用多种材质，用这张表建立多对多关系：

CREATE TABLE ProductMaterials (
    ProductId INT NOT NULL FOREIGN KEY REFERENCES Products(ProductId),
    MaterialId INT NOT NULL FOREIGN KEY REFERENCES Materials(MaterialId),
    PRIMARY KEY(ProductId, MaterialId)
)

三、级联操作的处理方案 #

你的级联需求涉及“删除后检查父级是否无关联数据再删除”，单纯依赖数据库的ON DELETE CASCADE无法完全满足，推荐两种方案：

1. 优先在C#应用层处理（推荐） #

小型项目里，代码逻辑直观，容易调试和维护，步骤示例：

• 删除品牌：
  1. 删除该品牌下所有产品关联的ProductMaterials记录
  2. 删除该品牌下所有产品
  3. 删除该品牌下所有系列
  4. 最后删除品牌本身
     （也可以给外键加上ON DELETE CASCADE，让数据库自动级联删除子表，简化代码）
• 删除产品：
  1. 删除该产品对应的ProductMaterials记录
  2. 删除产品
  3. 删除产品所属的系列
  4. 查询该品牌下是否还有剩余的系列或产品，若无则删除品牌
• 删除系列：
  1. 删除系列对应的产品及ProductMaterials记录
  2. 查询该品牌下是否还有剩余的系列或产品，若无则删除品牌
• 新增操作：
  新增产品前，需确保对应的系列已存在；新增系列前，需确保对应的品牌已存在，避免外键约束报错。

2. 数据库层用触发器处理（不推荐） #

如果一定要在数据库层实现，可通过INSTEAD OF DELETE触发器来处理复杂逻辑，但缺点是逻辑藏在数据库中，调试困难，且与C#应用耦合度变高。示例（以删除产品为例）：

CREATE TRIGGER trg_DeleteProduct_Cascade
ON Products
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 删除产品关联的材质记录
    DELETE pm FROM ProductMaterials pm
    JOIN deleted d ON pm.ProductId = d.ProductId;

    -- 删除产品
    DELETE p FROM Products p
    JOIN deleted d ON p.ProductId = d.ProductId;

    -- 获取要删除的系列ID
    DECLARE @SeriesIds TABLE(SeriesId INT);
    INSERT INTO @SeriesIds
    SELECT d.SeriesId FROM deleted d;

    -- 删除系列
    DELETE s FROM Series s
    JOIN @SeriesIds si ON s.SeriesId = si.SeriesId;

    -- 检查品牌是否还有其他系列或产品
    DECLARE @BrandIds TABLE(BrandId INT);
    INSERT INTO @BrandIds
    SELECT s.BrandId FROM Series s
    JOIN @SeriesIds si ON s.SeriesId = si.SeriesId;

    DELETE b FROM Brands b
    JOIN @BrandIds bi ON b.BrandId = bi.BrandId
    WHERE NOT EXISTS(SELECT 1 FROM Series s WHERE s.BrandId = b.BrandId)
      AND NOT EXISTS(SELECT 1 FROM Products p JOIN Series s ON p.SeriesId = s.SeriesId WHERE s.BrandId = b.BrandId);
END

四、总结 #

1. 必须采用规范化的关联数据库设计，单表设计是短视行为，会给后续维护埋下巨大隐患。
2. 级联逻辑优先在C#应用层实现，代码可控性强，适合小型项目。
3. 不建议使用触发器/函数，除非有特殊的性能或权限需求，否则会增加系统复杂度。

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