多级商品分类系统设计方案

CREATE TABLE `category_level_1` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` VARCHAR(100) NOT NULL COMMENT '分类名称',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COMMENT='一级分类表';
CREATE TABLE `category_level_2` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` VARCHAR(200) NOT NULL COMMENT '分类名称',
`parent_id` BIGINT(20) NOT NULL COMMENT '父级分类ID',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志',
PRIMARY KEY (`id`),
FOREIGN KEY (`parent_id`) REFERENCES `category_level_1`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COMMENT='二级分类表';
CREATE TABLE `category_level_3` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` VARCHAR(200) NOT NULL COMMENT '分类名称',
`parent_id` BIGINT(20) NOT NULL COMMENT '父级分类ID',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志',
PRIMARY KEY (`id`),
FOREIGN KEY (`parent_id`) REFERENCES `category_level_2`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1114 DEFAULT CHARSET=utf8mb4 COMMENT='三级分类表';


CREATE TABLE `attribute_info` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` VARCHAR(100) NOT NULL COMMENT '属性名称',
`category_id` BIGINT(20) NOT NULL COMMENT '所属分类ID',
`category_level` INT(11) NOT NULL COMMENT '分类级别',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志',
PRIMARY KEY (`id`),
FOREIGN KEY (`category_id`) REFERENCES `category_level_3`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COMMENT='属性表';
CREATE TABLE `attribute_value` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`value` VARCHAR(100) NOT NULL COMMENT '属性值',
`attribute_id` BIGINT(20) NOT NULL COMMENT '属性ID',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志',
PRIMARY KEY (`id`),
FOREIGN KEY (`attribute_id`) REFERENCES `attribute_info`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8mb4 COMMENT='属性值表';