mysql梳理笔记---数据结构优化(实例分析)

需求说明

按照下面的需求设计一个电子商务网站的数据库结构

  • 1.本网站只销售图书类商品
  • 2.需要具有以下功能:
    • 用户登录
    • 商品展示
    • 供应商管理
    • 用户管理
    • 商品管理
    • 在线销售

用户登录及用户管理功能

  • 用户必须登录系统才能进行网上交易:用户名做用户信息的业务主键(唯一)
  • 同一时间,统一用户只能在同一个地方登录
  • 用户信息:用户名/密码/手机号/姓名/注册日期/在线状态/出生日期
  • 只有一个业务主键,一定符合第二范式
  • 没有属性和业务主键存在传递依赖关系,符合第三范式

商品展示

  • 商品信息:商品名称/分类名称/出版社名称/图书价格/图书描述/作者。商品信息和分类名称同时作为业务主键。

    分类名称不符合第三范式。如果添加一个分类,但是没有商品信息,数据会有问题。所以需要拆分表。

    • 商品信息:商品名称/出版社名称/图书价格/图书描述/作者,商品名称做主键。
    • 分类信息:分类名称/分类描述,分类名称做主键。
    • 商品分类(对应关系表):分类名称和商品名称联合做业务主键

供应商管理

  • 供应商信息:出版社名称/地址/电话/联系人/银行账号。出版社名称做主键。
  • 这个表只有一个主键,不涉及引用关系,所以符合范式要求。

在线销售功能

  • 在线销售:订单编号/下单用户名称/下单日期/订单金额/订单商品分类/订单商品名/订单商品单价/订单商品数量/支付金额/物流单号。订单编号做主键
  • 只有一个业务主键,符合第二范式;订单商品单价/订单商品数量/订单编号/ 存在传递依赖关系,不符合第三范式
  • 数据冗余:订单商品信息和商品信息表中的数据
  • 拆分

    • 订单表:订单编号/下单用户名称/下单日期/支付金额/物流单号
    • 订单商品关联表:订单编号/订单商品分类/订单商品名称/商品数量。主键是订单编号/订单商品分类/订单商品名称

查询分析

编写SQL查询出每一个用户的订单总金额

    select 下单用户名,sum(d.商品价格*b.商品数量)
    from 订单表 a join 订单商品关联表 b on a.订单编号 = b.订单编号
                join 商品分类关联表 c on c.商品名称 = b.商品名称
                                    and c.分类名称 = b.订单商品分类
                join 商品信息表 d on d.商品名称 = c.商品名称
    group by 下单用户名

表结构分析

  • 符合范式话要求
  • 关联表结构过多
  • 商品价格变动时,无法查看到当时的价格
  • 不能得到良好的SQL查询功能

反范式话设计

反范式化是针对范式化而言的,在前面介绍了数据库设计的范式,所谓的反范式化就是为了性能和读取效率的考虑,而适当的对数据库设计范式的要求进行违反,而 允许存在少量的数据冗余,换句话来说反范式化使用空间来换取时间

商品信息/分类信息/商品分类关系表合并

  • 商品信息:商品名称/分类名称/出版社名称/图书价格/图书描述/作者
  • 分类信息:分类名称/分类描述

订单表/订单商品关联表冗余

  • 订单表:订单编号/下单用户名称/下单日期/支付金额/物流单号/手机号/订单金额。对手机号和订单金额进行冗余。首先是这两项查询频繁。同一个订单手机号 理论上页应该一致。对于订单金额,这里要保留下来,使之不随着之后订单单价对变化而变化。
  • 订单商品关联表:订单编号/订单商品分类/订单商品名/商品数量/商品单价。这里同样将商品单价和商品数量进行冗余。

编写SQL查询出每一个用户的订单总金额

    select 下单用户名,sum(订单金额)
    from 订单表 
    group by 下单用户名

总结

  • 不能完全按照范式化的要求进行设计
  • 考虑以后如何使用表

随机浏览