电商平台商品模块数据库设计
一个完善的电商商品数据库设计需要考虑商品基本信息、分类体系、库存管理、价格体系、营销属性等多个方面。以下是电商平台商品模块的核心数据库表设计。
核心表结构
1. 商品基础表 (products)
CREATE TABLE products (
product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_code VARCHAR(50) UNIQUE NOT NULL,
product_name VARCHAR(100) NOT NULL,
short_description VARCHAR(255),
full_description TEXT,
brand_id BIGINT,
category_id BIGINT NOT NULL,
supplier_id BIGINT,
tax_class_id INT,
weight DECIMAL(10,2),
length DECIMAL(10,2),
width DECIMAL(10,2),
height DECIMAL(10,2),
is_active BOOLEAN DEFAULT TRUE,
is_featured BOOLEAN DEFAULT FALSE,
is_bestseller BOOLEAN DEFAULT FALSE,
is_new BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (brand_id) REFERENCES brands(brand_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id),
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);
2. 商品分类表 (categories)
CREATE TABLE categories (
category_id BIGINT PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT,
category_name VARCHAR(50) NOT NULL,
description TEXT,
image_url VARCHAR(255),
sort_order INT DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);
3. 品牌表 (brands)
CREATE TABLE brands (
brand_id BIGINT PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(50) NOT NULL,
brand_logo VARCHAR(255),
description TEXT,
is_featured BOOLEAN DEFAULT FALSE,
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
4. 商品属性表 (product_attributes)
CREATE TABLE product_attributes (
attribute_id BIGINT PRIMARY KEY AUTO_INCREMENT,
attribute_name VARCHAR(50) NOT NULL,
attribute_code VARCHAR(50) UNIQUE NOT NULL,
attribute_type ENUM('text', 'select', 'multiselect', 'boolean', 'date', 'decimal') NOT NULL,
is_filterable BOOLEAN DEFAULT FALSE,
is_visible BOOLEAN DEFAULT TRUE,
is_required BOOLEAN DEFAULT FALSE,
sort_order INT DEFAULT 0
);
5. 商品属性值表 (product_attribute_values)
CREATE TABLE product_attribute_values