【MySQL】多表设计

---

一、一对多

员工表 - 部门表之间的关系：

一对多关系实现：在数据库表中多的一方，添加字段，来关联属于一这方的主键。

二、物理外键和逻辑外键

外键约束：让两张表的数据建立连接，保证数据的一致性和完整性。
对应的关键字：foreign key

物理外键和逻辑外键

• 物理外键

  • 概念：使用foreign key定义外键关联另外一张表。
  • 缺点：
    • 影响增、删、改的效率（需要检查外键关系）。
    • 仅用于单节点数据库，不适用与分布式、集群场景。
    • 容易引发数据库的死锁问题，消耗性能。

• 逻辑外键

  • 概念：在业务层逻辑中，解决外键关联。
  • 通过逻辑外键，就可以很方便的解决上述问题。

在现在的企业开发中，很少会使用物理外键，都是使用逻辑外键。 甚至在一些数据库开发规范中，会明确指出禁止使用物理外键 foreign key

三、案例

需求：

• 根据 实际项目_后台管理_页面原型 设计分类管理、菜品管理、套餐管理模块的表结构。

分析：

• 页面原型-分类管理

分类的信息：分类名称、分类类型[菜品/套餐]、分类排序、分类状态[禁用/启用]、分类的操作时间(修改时间)。

• 页面原型-菜品管理

菜品的信息：菜品名称、菜品图片、菜品分类、菜品售价、菜品售卖状态、菜品的操作时间(修改时间)。

思考：分类与菜品之间是什么关系？

• 思考逻辑：一个分类下可以有多个菜品吗？反过来再想一想，一个菜品会对应多个分类吗？

答案：一对多关系。一个分类下会有多个菜品，而一个菜品只能归属一个分类。

设计表原则：在多的一方，添加字段，关联属于一这方的主键。

• 页面原型-套餐管理

套餐的信息：套餐名称、套餐图片、套餐分类、套餐价格、套餐售卖状态、套餐的操作时间。

思考：套餐与菜品之间是什么关系？

• 思考逻辑：一个套餐下可以有多个菜品吗？反过来再想一想，一个菜品可以出现在多个套餐中吗？

答案：多对多关系。一个套餐下会有多个菜品，而一个菜品也可以出现在多个套餐中。

设计表原则：创建第三张中间表，建立两个字段分别关联菜品表的主键和套餐表的主键。

分析页面原型及需求文档后，我们获得：

• 分类表
  • 业务字段：分类名称、分类类型、分类排序、分类状态
  • 基础字段：id(主键)、分类的创建时间、分类的修改时间
• 菜品表
  • 业务字段：菜品名称、菜品图片、菜品分类、菜品售价、菜品售卖状态
  • 基础字段：id(主键)、分类的创建时间、分类的修改时间
• 套餐表
  • 业务字段：套餐名称、套餐图片、套餐分类、套餐价格、套餐售卖状态
  • 基础字段：id(主键)、分类的创建时间、分类的修改时间

表结构之间的关系：

• 分类表 - 菜品表 ： 一对多
  • 在菜品表中添加字段(菜品分类)，关联分类表
• 菜品表 - 套餐表 ： 多对多
  • 创建第三张中间表(套餐菜品关联表)，在中间表上添加两个字段(菜品id、套餐id)，分别关联菜品表和分类表

表结构

分类表：category

• 业务字段：分类名称、分类类型、分类排序、分类状态
• 基础字段：id(主键)、创建时间、修改时间

-- 分类表
create table category
(
    id          int unsigned primary key auto_increment comment '主键ID',
    name        varchar(20)      not null unique comment '分类名称',
    type        tinyint unsigned not null comment '类型 1 菜品分类 2 套餐分类',
    sort        tinyint unsigned not null comment '顺序',
    status      tinyint unsigned not null default 0 comment '状态 0 禁用，1 启用',
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '更新时间'
) comment '菜品及套餐分类';

菜品表：dish

• 业务字段：菜品名称、菜品图片、菜品分类、菜品售价、菜品售卖状态
• 基础字段：id(主键)、分类的创建时间、分类的修改时间

-- 菜品表
create table dish
(
    id          int unsigned primary key auto_increment comment '主键ID',
    name        varchar(20)      not null unique comment '菜品名称',
    category_id int unsigned     not null comment '菜品分类ID',   -- 逻辑外键
    price       decimal(8, 2)    not null comment '菜品价格',
    image       varchar(300)     not null comment '菜品图片',
    description varchar(200) comment '描述信息',
    status      tinyint unsigned not null default 0 comment '状态, 0 停售 1 起售',
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '更新时间'
) comment '菜品';

套餐表：setmeal

• 业务字段：套餐名称、套餐图片、套餐分类、套餐价格、套餐售卖状态
• 基础字段：id(主键)、分类的创建时间、分类的修改时间

-- 套餐表
create table setmeal
(
    id          int unsigned primary key auto_increment comment '主键ID',
    name        varchar(20)      not null unique comment '套餐名称',
    category_id int unsigned     not null comment '分类id',       -- 逻辑外键
    price       decimal(8, 2)    not null comment '套餐价格',
    image       varchar(300)     not null comment '图片',
    description varchar(200) comment '描述信息',
    status      tinyint unsigned not null default 0 comment '状态 0:停用 1:启用',
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '更新时间'
) comment '套餐';

套餐菜品关联表：setmeal_dish

-- 套餐菜品关联表
create table setmeal_dish
(
    id         int unsigned primary key 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 '份数'
) comment '套餐菜品关联表';

---

总结