MySQL-21.多表设计-案例-关系分析-表结构
一.参考页面原型及需求,设计合理的表结构
二.创建category表
create table catagory
(id int unsigned auto_increment comment '主键ID'primary key,name varchar(20) not null comment '分类名称',type tinyint unsigned not null comment '类型:1.菜品分类,2.tao''cuo',sort tinyint unsigned not null comment '排序字段',status tinyint unsigned not null comment '状态字段:0.停用。1.启用',create_time datetime not null comment '创建时间',update_time datetime not null comment '更新时间',constraint catagory_pk2unique (name)
)comment '分类表';
三.创建dish表
create table dish
(id int unsigned auto_incrementprimary key,name varchar(20) not null comment '菜品名称',category_id int unsigned not null,price decimal(8, 2) not null,image varchar(300) not null comment '图片url',description varchar(200) null comment '描述信息',stutus tinyint unsigned default '0' not null comment '状态.0:停售,1:起售',create_time datetime not null comment '创建时间',update_time datetime not null comment '修改时间',constraint dish_pk2unique (name)
)comment '菜品表';
三.套餐表-setmeal
create table setmeal
(id int unsigned auto_increment comment '主键ID'primary key,name varchar(20) not null comment '套餐名称',category_id int unsigned not null comment '套餐分类ID',price decimal(8, 2) not null comment '价格',image varchar(300) not null comment '图片url',description varchar(200) null comment '描述信息',status tinyint unsigned default '0' not null comment '状态,0:停售,1:起售',create_time datetime not null comment '创建时间',update_time datetime not null comment '修改时间',constraint setmeal_pk2unique (name)
)comment '套餐表';
四.套餐菜品关系表 setmeal_dish
create table setmeal_dish
(id int unsigned auto_increment comment '主键id',setmeal_id int unsigned not null comment '套餐id',dish_id int unsigned not null comment '菜品ID',copies tinyint unsigned not null comment '菜品的份数',constraint setmeal_dish_pkprimary key (id)
)comment '套餐菜品关系表';
总结