多计量单位（UOM）产品的数据库表设计方法咨询

Got it, let's work through this UOM conversion database design problem—your puff pastry example with two different pcs-to-carton ratios is a great case of why a flexible schema matters. Here's a practical, maintainable setup that handles multiple conversion pairs per product without breaking a sweat.

1. 基础表：产品表 (products) #

First, we need a table to store core product details. This keeps product data centralized and avoids redundancy.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Example Insert:

INSERT INTO products (product_name, description) 
VALUES ('泡芙', '奶油夹心泡芙，多种包装规格');

2. 基础表：计量单位表 (uoms) #

Next, a centralized table for all possible units of measure. This ensures consistency across your system (no typos like "pc" vs "pcs" or "carton" vs "box").

CREATE TABLE uoms (
    uom_id INT PRIMARY KEY AUTO_INCREMENT,
    uom_code VARCHAR(50) UNIQUE NOT NULL, -- Short code for easy reference (e.g., 'pcs', 'carton')
    uom_name VARCHAR(255) NOT NULL, -- Full name (e.g., '个', '箱')
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Example Inserts:

INSERT INTO uoms (uom_code, uom_name) VALUES ('pcs', '个');
INSERT INTO uoms (uom_code, uom_name) VALUES ('carton', '箱');

3. 核心表：产品计量单位换算表 (product_uom_conversions) #

This is the key table that handles multiple conversion relationships per product—including your scenario where a single product has two different pcs-to-carton ratios. Each row represents a specific conversion between two units for a product.

CREATE TABLE product_uom_conversions (
    conversion_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    from_uom_id INT NOT NULL,
    from_quantity DECIMAL(18,6) NOT NULL, -- Quantity of the "from" unit
    to_uom_id INT NOT NULL,
    to_quantity DECIMAL(18,6) NOT NULL, -- Equivalent quantity of the "to" unit
    description VARCHAR(255), -- Optional: explain the context (e.g., "常规整箱", "促销迷你箱")
    is_default BOOLEAN DEFAULT FALSE, -- Optional: mark a default conversion for the product
    valid_from DATE, -- Optional: for time-bound conversions (e.g., limited-time promotions)
    valid_to DATE, -- Optional: end date for time-bound conversions
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (from_uom_id) REFERENCES uoms(uom_id),
    FOREIGN KEY (to_uom_id) REFERENCES uoms(uom_id)
);

Example Inserts for Your Puff Pastry:
Assuming product_id=1 (泡芙), uom_id=1 (pcs/个), uom_id=2 (carton/箱):

-- 常规装箱：150个 = 1箱
INSERT INTO product_uom_conversions (product_id, from_uom_id, from_quantity, to_uom_id, to_quantity, description, is_default)
VALUES (1, 1, 150, 2, 1, '常规整箱包装', TRUE);

-- 促销迷你箱：8个 = 1箱
INSERT INTO product_uom_conversions (product_id, from_uom_id, from_quantity, to_uom_id, to_quantity, description)
VALUES (1, 1, 8, 2, 1, '促销迷你箱包装');

为什么这样设计？ #

• Flexibility: 完美支持同一产品的多组换算关系（比如你的泡芙两种装箱规格），甚至同一单位对的不同转换比例。
• Normalization: 分离基础数据（产品、UOM）和关系数据（换算），避免冗余，符合数据库设计范式。
• Scalability: 新增产品、UOM或换算关系只需要插入新记录—无需修改表结构。
• Clarity: 可选的description和is_default字段让业务逻辑更清晰，系统可以轻松识别默认换算或特定场景的转换。

额外优化建议 #

• Add a unique constraint if you want to prevent duplicate conversion pairs for a product (e.g., UNIQUE(product_id, from_uom_id, from_quantity, to_uom_id, to_quantity)—adjust based on your business rules).
• Use DECIMAL instead of INT for quantities to support fractional conversions (e.g., 1 kg = 2.20462 lbs).
• Add indexes on product_id, from_uom_id, and to_uom_id to speed up query performance when fetching conversions for a product.

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