设计品牌和分类

-- 商品分类表
DROP TABLE IF EXISTS `t_category`;
CREATE TABLE `t_category` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类编码',
  `categoryname` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '分类名称',
  `parentid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '父分类编码，0表示为顶层类别',
  `haschild` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否含有子分类 0：否；1：是',
  `sort` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '排名指数',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_parentid`(`parentid`) USING BTREE,
  INDEX `idx_sort`(`sort`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '商品分类表';

-- 品牌表
DROP TABLE IF EXISTS `t_brand`;
CREATE TABLE `t_brand` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '品牌编码',
  `brandname` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '品牌名称',
  `image` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '图片网址',
  `letter` CHAR(1) NOT NULL DEFAULT 'A' COMMENT '品牌首字母',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_brandname`(`brandname`) USING BTREE,
  INDEX `idx_letter`(`letter`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '品牌表';

-- 品牌历史表
DROP TABLE IF EXISTS `t_brand`;
CREATE TABLE `t_brand` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '流水主键',
  `brandid` BIGINT(20) UNSIGNED NOT NULL COMMENT '品牌编码',
  `brandname` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '品牌名称',
  `image` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '图片网址',
  `letter` CHAR(1) NOT NULL DEFAULT 'A' COMMENT '品牌首字母',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_brandname`(`brandname`) USING BTREE,
  INDEX `idx_letter`(`letter`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '品牌历史表';

-- 品牌与分类关联表
DROP TABLE IF EXISTS `t_category_brand`;
CREATE TABLE `t_category_brand` (
  `categoryid` INT(11) UNSIGNED NOT NULL COMMENT '分类编码',
  `brandid` BIGINT(20) UNSIGNED NOT NULL COMMENT '品牌编码',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`categoryid`, `brandid`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '品牌与分类关联表';

---

感谢支持

更多内容，请移步《超级个体》。