如何在PostgreSQL中管理电商场景的多选项与订单多产品存储

一、先修正基础数据类型问题 #

当前表结构里products的stock、price用VARCHAR完全不合理，SQL里这类数值型字段得用对应类型：

• stock是库存数量，改用INT或SMALLINT
• price是价格，改用DECIMAL(10,2)（保证小数精度，适合金额计算）

二、产品多颜色、多尺寸的规范化设计 #

MongoDB用数组存多属性的思路，在SQL里得靠关联表实现——这是关系型数据库遵循第三范式（3NF）的核心，能避免数据冗余和维护混乱。

1. 拆分产品主信息与SKU #

电商里“产品”和“SKU”是两个概念：比如“纯棉T恤”是产品，“红色M码纯棉T恤”是SKU（库存、价格都是绑定到SKU的）。所以要拆成4张表：

-- 产品主表：存所有变体共享的公共信息
CREATE TABLE products (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(75) NOT NULL,
    main_img VARCHAR(125) NOT NULL
);

-- 颜色表：可复用，多个产品能共用同一种颜色
CREATE TABLE product_colors (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    color_name VARCHAR(20) NOT NULL UNIQUE
);

-- 尺寸表：同理，可复用
CREATE TABLE product_sizes (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    size_name VARCHAR(10) NOT NULL UNIQUE
);

-- SKU表：具体的产品变体，库存、价格绑定到这里
CREATE TABLE product_skus (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES products(id),
    color_id BIGINT NOT NULL REFERENCES product_colors(id),
    size_id BIGINT NOT NULL REFERENCES product_sizes(id),
    stock INT NOT NULL DEFAULT 0,
    price DECIMAL(10,2) NOT NULL,
    img VARCHAR(125) NOT NULL,
    -- 确保同一个产品的颜色+尺寸组合不重复
    UNIQUE(product_id, color_id, size_id)
);

这么设计的好处：

• 颜色、尺寸可以复用，不用重复输入相同内容
• 每个SKU单独管理库存和价格，符合实际电商场景（不同颜色/尺寸可能定价不同）
• 方便查询某个产品的所有可选颜色、尺寸，以及对应库存状态

三、订单多产品的规范化设计 #

之前用VARCHAR存订单里的产品列表，没法关联产品详情，也不利于后续统计查询。得用订单主表+订单商品关联表实现一对多关系：

-- 订单主表：存订单的整体信息
CREATE TABLE orders (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    status VARCHAR(25) DEFAULT 'pending',
    user_id BIGINT NOT NULL REFERENCES users(id)
);

-- 订单商品表：存订单里的每一项商品
CREATE TABLE order_items (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    sku_id BIGINT NOT NULL REFERENCES product_skus(id),
    quantity INT NOT NULL DEFAULT 1,
    -- 记录下单时的单价，避免后续SKU价格变动影响订单历史数据
    unit_price DECIMAL(10,2) NOT NULL
);

这么设计的好处：

• 清晰记录每个订单包含的具体SKU和购买数量
• 能快速统计订单总金额、某个SKU的销售数据
• 靠外键维护订单和商品的关联，保证数据一致性

四、用户表的小优化 #

users表的password字段建议改成VARCHAR(255)，兼容bcrypt这类哈希算法生成的较长密码哈希值（通常60字符左右）：

ALTER TABLE users ALTER COLUMN password TYPE VARCHAR(255);

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