PostgreSQL+Sequelize+Node.js电商平台：产品变体与定价表结构设计求助

1. 数据库表结构设计 #

主产品表 products #

存储产品通用基础信息，不包含变体专属的定价、库存等字段：

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  main_image_url VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

变体属性表 variant_attributes #

定义变体的属性类型（比如颜色、设计风格）：

CREATE TABLE variant_attributes (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL, -- 示例："颜色"、"设计印花"
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

变体属性值表 variant_attribute_values #

存储属性对应的具体值（比如红色、海贼王印花）：

CREATE TABLE variant_attribute_values (
  id SERIAL PRIMARY KEY,
  attribute_id INTEGER REFERENCES variant_attributes(id) ON DELETE CASCADE,
  value VARCHAR(100) NOT NULL, -- 示例："红色"、"海贼王路飞印花"
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

产品变体表 product_variants #

每个条目对应一个具体产品变体，存储该变体的定价、库存等专属信息：

CREATE TABLE product_variants (
  id SERIAL PRIMARY KEY,
  product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
  sku VARCHAR(100) UNIQUE NOT NULL, -- 唯一库存单元编码
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INTEGER DEFAULT 0,
  image_url VARCHAR(255), -- 变体专属图片（可选）
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

变体-属性值关联表 variant_attribute_value_links #

建立变体与对应属性值的关联，一个变体可对应多个属性值（比如红色+路飞印花）：

CREATE TABLE variant_attribute_value_links (
  variant_id INTEGER REFERENCES product_variants(id) ON DELETE CASCADE,
  attribute_value_id INTEGER REFERENCES variant_attribute_values(id) ON DELETE CASCADE,
  PRIMARY KEY (variant_id, attribute_value_id)
);

2. Sequelize 模型定义与关联 #

Products 模型 #

// models/Product.js
module.exports = (sequelize, DataTypes) => {
  const Product = sequelize.define('Product', {
    name: { type: DataTypes.STRING, allowNull: false },
    description: DataTypes.TEXT,
    mainImageUrl: DataTypes.STRING
  }, {
    timestamps: true,
    tableName: 'products'
  });

  Product.associate = models => {
    Product.hasMany(models.ProductVariant, { foreignKey: 'productId', onDelete: 'CASCADE' });
  };

  return Product;
};

VariantAttribute 模型 #

// models/VariantAttribute.js
module.exports = (sequelize, DataTypes) => {
  const VariantAttribute = sequelize.define('VariantAttribute', {
    name: { type: DataTypes.STRING, allowNull: false }
  }, {
    timestamps: true,
    tableName: 'variant_attributes'
  });

  VariantAttribute.associate = models => {
    VariantAttribute.hasMany(models.VariantAttributeValue, { foreignKey: 'attributeId', onDelete: 'CASCADE' });
  };

  return VariantAttribute;
};

VariantAttributeValue 模型 #

// models/VariantAttributeValue.js
module.exports = (sequelize, DataTypes) => {
  const VariantAttributeValue = sequelize.define('VariantAttributeValue', {
    value: { type: DataTypes.STRING, allowNull: false }
  }, {
    timestamps: true,
    tableName: 'variant_attribute_values'
  });

  VariantAttributeValue.associate = models => {
    VariantAttributeValue.belongsTo(models.VariantAttribute, { foreignKey: 'attributeId' });
    VariantAttributeValue.belongsToMany(models.ProductVariant, {
      through: 'variant_attribute_value_links',
      foreignKey: 'attributeValueId',
      otherKey: 'variantId',
      onDelete: 'CASCADE'
    });
  };

  return VariantAttributeValue;
};

ProductVariant 模型 #

// models/ProductVariant.js
module.exports = (sequelize, DataTypes) => {
  const ProductVariant = sequelize.define('ProductVariant', {
    sku: { type: DataTypes.STRING, allowNull: false, unique: true },
    price: { type: DataTypes.DECIMAL(10,2), allowNull: false },
    stockQuantity: { type: DataTypes.INTEGER, defaultValue: 0 },
    imageUrl: DataTypes.STRING
  }, {
    timestamps: true,
    tableName: 'product_variants'
  });

  ProductVariant.associate = models => {
    ProductVariant.belongsTo(models.Product, { foreignKey: 'productId' });
    ProductVariant.belongsToMany(models.VariantAttributeValue, {
      through: 'variant_attribute_value_links',
      foreignKey: 'variantId',
      otherKey: 'attributeValueId',
      onDelete: 'CASCADE'
    });
  };

  return ProductVariant;
};

3. 实际使用示例 #

以创建动漫T恤变体为例：

1. 创建主产品

const product = await Product.create({
  name: '动漫主题T恤',
  description: '纯棉材质，多款动漫印花可选',
  mainImageUrl: '/images/tshirt-main.jpg'
});

2. 创建属性类型

const colorAttr = await VariantAttribute.create({ name: '颜色' });
const designAttr = await VariantAttribute.create({ name: '设计印花' });

3. 创建属性值

const red = await VariantAttributeValue.create({ attributeId: colorAttr.id, value: '红色' });
const blue = await VariantAttributeValue.create({ attributeId: colorAttr.id, value: '蓝色' });
const luffyDesign = await VariantAttributeValue.create({ attributeId: designAttr.id, value: '海贼王路飞印花' });
const narutoDesign = await VariantAttributeValue.create({ attributeId: designAttr.id, value: '火影忍者鸣人印花' });

4. 创建变体并关联属性值

// 红色路飞印花T恤
const redLuffyVariant = await ProductVariant.create({
  productId: product.id,
  sku: 'TSHIRT-RED-LUFFY',
  price: 79.99,
  stockQuantity: 100,
  imageUrl: '/images/tshirt-red-luffy.jpg'
});
await redLuffyVariant.addVariantAttributeValues([red, luffyDesign]);

// 蓝色鸣人印花T恤
const blueNarutoVariant = await ProductVariant.create({
  productId: product.id,
  sku: 'TSHIRT-BLUE-NARUTO',
  price: 84.99,
  stockQuantity: 80,
  imageUrl: '/images/tshirt-blue-naruto.jpg'
});
await blueNarutoVariant.addVariantAttributeValues([blue, narutoDesign]);

5. 查询产品及所有变体（含属性值）

const productWithVariants = await Product.findByPk(product.id, {
  include: [{
    model: ProductVariant,
    include: [{ model: VariantAttributeValue, include: [VariantAttribute] }]
  }]
});

4. 方案优势 #

• 扩展性强：新增属性类型（如尺码）只需在variant_attributes中添加条目，无需修改表结构
• 数据冗余低：属性值复用，避免重复存储相同的颜色、设计值
• 符合电商SKU规范：每个变体对应唯一SKU，便于库存和订单管理

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