新零售mysql设计（品牌表 分类表 品牌分类表）深度解析

CREATE TABLE  t_brand(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
    `name`varchar(200)  NOT null  COMMENT "名称",
    image varchar(500) COMMENT "图片网址",
    letter CHAR(1) not null COMMENT "品牌首字母",
    UNIQUE unq_name(`name`),
    INDEX idx_letter(letter)
    
) COMMENT="品牌表";

CREATE TABLE t_category(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
    `name` varchar(200) not null COMMENT "分类名称",
    parent_id int unsigned COMMENT "上级分类id",
    if_parent boolean not null COMMENT "是否含有下级分类",
    sort int unsigned not null COMMENT "排名指数",
    INDEX idx_parent_id(parent_id),
    INDEX idx_sort(sort)
) COMMENT="商品分类表";

CREATE TABLE t_category_brand(
category_id int unsigned COMMENT "分类id",
brand_id int unsigned comment "品牌id",
    PRIMARY KEY(category_id,brand_id)
) COMMENT="分类与品牌关联表";

CREATE TABLE t_category(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
    `name` varchar(200) not null COMMENT "分类名称",
    parent_id int unsigned COMMENT "上级分类id",
    if_parent boolean not null COMMENT "是否含有下级分类",
    sort int unsigned not null COMMENT "排名指数",
    INDEX idx_parent_id(parent_id),
    INDEX idx_sort(sort)
) COMMENT="商品分类表";