回答

设计一个商品表以及其相关的商品规格、颜色、款式表，需要考虑到数据库的规范化和灵活性，以便于后期的扩展和维护。以下是一个基本的数据库设计示例，包括商品表、规格表、颜色表、款式表以及它们之间的关系。

1. 商品表 (products)

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

2. 规格表 (specifications)

CREATE TABLE specifications (
    spec_id INT AUTO_INCREMENT PRIMARY KEY,
    spec_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

3. 颜色表 (colors)

CREATE TABLE colors (
    color_id INT AUTO_INCREMENT PRIMARY KEY,
    color_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

4. 款式表 (styles)

CREATE TABLE styles (
    style_id INT AUTO_INCREMENT PRIMARY KEY,
    style_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

5. 商品规格、颜色、款式的关系表 (product_variants)

CREATE TABLE product_variants (
    variant_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    spec_id INT NOT NULL,
    color_id INT NOT NULL,
    style_id INT NOT NULL,
    stock INT DEFAULT 0,
    price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (spec_id) REFERENCES specifications(spec_id),
    FOREIGN KEY (color_id) REFERENCES colors(color_id),
    FOREIGN KEY (style_id) REFERENCES styles(style_id)
);

示例数据

-- 插入一些示例数据
INSERT INTO products (product_name, description, price) VALUES
('T-shirt', 'A cool T-shirt', 19.99),
('Jeans', 'Comfortable jeans', 49.99);

INSERT INTO specifications (spec_name) VALUES
('Small'),
('Medium'),
('Large');

INSERT INTO colors (color_name) VALUES
('Red'),
('Green'),
('Blue');

INSERT INTO styles (style_name) VALUES
('Casual'),
('Sport'),
('Formal');

INSERT INTO product_variants (product_id, spec_id, color_id, style_id, stock, price) VALUES
(1, 1, 1, 1, 100, 19.99), -- T-shirt, Small, Red, Casual
(1, 2, 2, 2, 50, 21.99),  -- T-shirt, Medium, Green, Sport
(2, 3, 3, 3, 75, 49.99);  -- Jeans, Large, Blue, Formal

查询示例获取所有商品及其变体信息：

SELECT 
    p.product_id, 
    p.product_name, 
    pv.variant_id, 
    s.spec_name, 
    c.color_name, 
    st.style_name, 
    pv.stock, 
    pv.price
FROM 
    products p
JOIN 
    product_variants pv ON p.product_id = pv.product_id
JOIN 
    specifications s ON pv.spec_id = s.spec_id
JOIN 
    colors c ON pv.color_id = c.color_id
JOIN 
    styles st ON pv.style_id = st.style_id;

获取特定商品的所有颜色选项：

SELECT 
    DISTINCT c.color_name
FROM 
    product_variants pv
JOIN 
    colors c ON pv.color_id = c.color_id
WHERE 
    pv.product_id = 1; -- 替换为具体的 product_id

设计说明products 表存储商品的基本信息。specifications 表存储规格（如大小）。colors 表存储颜色选项。styles 表存储款式信息。product_variants 表存储商品与其规格、颜色、款式之间的关系，并包含库存和价格等信息。

这种设计方式确保了数据的规范化，避免了数据冗余，并且可以方便地扩展，例如增加新的规格、颜色或款式。