PHP项目商品模块如何设计数据表

PHP项目商品模块数据表设计的核心策略与最佳实践

目录导读

1. 商品模块数据表设计的重要性
2. 核心商品表设计原则与范式
3. 多商品类型下的表结构分层方案
4. 属性系统：EAV模式与JSON方案的权衡
5. 库存、价格与SKU关联模型
6. 分类与标签的多对多映射
7. 实战SQL示例与索引优化
8. 常见问题FAQ

---

商品模块数据表设计的重要性

在PHP电商项目或CMS系统中,商品模块是整个业务的核心，一个设计糟糕的商品数据表会导致：

• 查询性能低下（尤其是属性筛选时）
• 数据冗余与更新异常
• 扩展困难（如新增商品类型需改表结构）

根据搜索引擎收录的主流电商框架（如Magento、Shopware、WooCommerce）和PHP项目实践，优秀的商品表设计应当遵循：高内聚、低耦合、可扩展、查询友好四大原则。

---

核心商品表设计原则与范式

基础表命名规范

建议采用prd_前缀（product缩写），

• prd_product（商品主表）
• prd_sku（SKU表）
• prd_category（分类表）

最重要的设计决策：SPU vs SKU

• SPU（Standard Product Unit）：商品抽象层，如“iPhone 15 256GB”
• SKU（Stock Keeping Unit）：最小库存单位，如“iPhone 15 256GB 蓝色”

实战经验：绝大多数PHP项目必须分离SPU与SKU为两张表，理由如下：

• 商品基础信息（描述、图片、品牌）属于SPU
• 具体规格、价格、库存属于SKU
• 避免数据冗余（例如同一SPU下多个颜色，只需要一份描述信息）

主键类型选择

• 推荐使用自增INT/BIGINT（查询效率最优）
• 若需分布式ID,可采用雪花算法生成的BIGINT
• 避免VARCHAR UUID作主键（索引过大，写入慢）

---

多商品类型下的表结构分层方案

在真实PHP项目中,商品类型可能包含：实体商品、虚拟商品、服务、数字商品等，可采用单表继承或类表继承模式：

方案A：单表+type字段（适合类型少、差异小）

CREATE TABLE prd_product (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    type TINYINT COMMENT '1=实体 2=虚拟 3=数字',
    name VARCHAR(255),
    description TEXT,
    ...公共字段
);

缺点：不同商品的自定义字段会形成大量NULL列。

方案B：主表+扩展表（推荐用于复杂项目）

• prd_product：存放所有商品公共字段
• prd_product_physical：存放实体商品特有字段（weight, length等）
• prd_product_virtual：存放虚拟商品字段（download_url, expiry_days）

引用自某知名PHP电商框架的核心设计：“当商品类型超过3种且每个类型的专有字段超过5个时，必须使用垂直分表。”

---

属性系统：EAV模式与JSON方案的权衡

商品属性（如颜色、尺寸、材质）是设计难点，对比两种主流方案：

对比项	EAV（实体-属性-值）	JSON字段
灵活性	极高（随时新增属性）	中等
查询性能	差（需要JOIN多表）	好（MySQL 5.7+支持JSON索引）
数据完整性	好（可约束值类型）	差（无法约束JSON内部结构）
典型适用	京东、亚马逊类复杂属性	中小型项目、属性固定

推荐实践

• 核心属性（如品牌、颜色）：
  • 创建单独的属性定义表 prd_attribute
  • 创建产品-属性关联表 prd_product_attribute
• 可变或非核心属性：存储为 prd_product 表的attributes JSON字段
• 注意：若开启MySQL JSON字段，必须建立虚拟索引以优化筛选

示例：混合方案SQL

-- 属性定义
CREATE TABLE prd_attribute (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),         -- 'color'
    type VARCHAR(20)          -- 'select', 'input', 'multi'
);
-- 核心属性关联
CREATE TABLE prd_product_attribute (
    product_id BIGINT,
    attribute_id INT,
    value VARCHAR(255),
    FOREIGN KEY (product_id) REFERENCES prd_product(id),
    FOREIGN KEY (attribute_id) REFERENCES prd_attribute(id)
);

---

库存、价格与SKU关联模型

典型的库存与SKU表设计：

CREATE TABLE prd_sku (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT,              -- 关联SPU
    sku_code VARCHAR(50) UNIQUE,    -- 唯一SKU编码
    price DECIMAL(10,2),            -- 销售价
    cost_price DECIMAL(10,2),       -- 成本价
    stock INT DEFAULT 0,            -- 库存量
    weight DECIMAL(8,2),
    spec_json TEXT,                  -- 规格组合: {"color":"blue","size":"L"}
    status TINYINT DEFAULT 1,
    created_at DATETIME,
    FOREIGN KEY (product_id) REFERENCES prd_product(id) ON DELETE CASCADE
);

价格变体设计

• 若需多个价格策略（等级价、会员价、促销价），单独创建 prd_price_tier 表
• 保留 price 字段作为默认显示价，减少JOIN查询

库存更新核心点

• 使用乐观锁（version字段）防止超卖
• 创建库存日志表 prd_stock_log 记录变动（order_id、type、change）

---

分类与标签的多对多映射

分类和标签与商品是多对多关系,需创建中间表：

分类表

CREATE TABLE prd_category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parent_id INT DEFAULT 0,     -- 自关联支持无限层级
    name VARCHAR(100),
    path VARCHAR(500),           -- 路径：0-12-34-56
    level TINYINT,
    sort_order INT DEFAULT 0
);

• 优点：path 字段配合LIKE查询可实现快速子分类检索
• 避免：递归查询所有父级分类（性能极差）

商品-分类关联

CREATE TABLE prd_product_category (
    product_id BIGINT,
    category_id INT,
    is_main TINYINT DEFAULT 0,   -- 是否主分类
    PRIMARY KEY (product_id, category_id)
);

---

实战SQL示例与索引优化

典型查询：按属性筛选商品

SELECT p.id, p.name, s.price
FROM prd_product p
JOIN prd_sku s ON p.id = s.product_id
JOIN prd_product_attribute pa ON p.id = pa.product_id
WHERE pa.attribute_id = 10 AND pa.value = '蓝色'
  AND s.price BETWEEN 100 AND 500
  AND s.status = 1
GROUP BY p.id
ORDER BY s.price ASC
LIMIT 20;

索引策略

1. prd_product(id)：主键索引
2. prd_sku(product_id)：外键索引
3. prd_product_attribute(product_id, attribute_id, value)：联合索引，加速属性筛选
4. prd_category(path)：前缀索引（如path(20)）
5. prd_sku(price, status)：复合索引，加速价格排序和状态过滤

常见隐患：对JSON字段内的值进行JSON_EXTRACT()筛选时，若不建立虚拟生成列并加索引，会导致全表扫描。

---

常见问题FAQ

Q1：什么时候必须使用分区表？

当商品表数据量超过500万行,且查询有明显的时间范围模式（如“最近30天上架”），可考虑按月或季度进行RANGE分区。

CREATE TABLE prd_product (...) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

Q2：商品图片怎么存储？

建议采用路径存储模式：数据库中只存图片路径（如/uploads/product/202408/1.jpg），不存BLOB，另外建立 prd_product_image 表关联商品与多张图片，并记录排序、是否是主图。

Q3：如何处理商品软删除？

在 prd_product 中添加 deleted_at 字段（DATETIME类型，默认NULL），所有查询加上 WHERE deleted_at IS NULL，优点：

• 可恢复数据
• 不影响UNIQUE约束（指向同一名字的已删除商品可写入）

Q4：表设计中最常见的错误

• 没有分离SPU与SKU（导致价格与库存冗余）
• 使用VARCHAR存储价格（应该用DECIMAL(10,2)）
• 缺乏 status 字段（导致上下架逻辑混乱）
• 没有创建 updated_at 索引（影响增量同步）

---

总结要点

设计PHP项目的商品数据表,需要回答三个关键问题：

1. 是否要分离SPU和SKU？ —— 除非是极简项目，否则必须分离
2. 属性如何处理？ —— 混合使用EAV与JSON，核心属性用EAV，非核心用JSON
3. 查询性能瓶颈在哪？ —— 预判筛选、排序、分页场景，提前设计联合索引

通过本文提供的分层设计思路与实战SQL,您将能够构建出一个既能支撑复杂业务、又容易扩展的商品数据模型，若需针对特定框架（如Laravel、ThinkPHP）的ORM映射代码示例，欢迎继续探讨。