主题
数据模型 · ER 实体关系图
来源：mall_data_dictionary.md, docs/sql/mall.sql 适用：理解各业务域的实体关系和数据基数 说明：每张 ER 图仅展示关键属性和关系，完整字段请参考 mall_data_dictionary.md
1. PMS 商品域
商品域是系统最核心的数据域，覆盖 SPU/SKU 多规格模型。
mermaid
erDiagram
Product["商品 SPU"] {
bigint id PK
varchar name "商品名称"
decimal price "价格"
int sale "销量"
int status "状态: 0下架 1上架"
}
SkuStock["SKU 库存"] {
bigint id PK
bigint product_id FK
varchar sku_code "SKU编码"
decimal price "SKU价格"
int stock "库存"
varchar sp_data "规格JSON"
}
Brand["品牌"] {
bigint id PK
varchar name "品牌名称"
varchar logo "品牌Logo"
int brand_story "品牌故事"
}
ProductCategory["商品分类"] {
bigint id PK
bigint parent_id FK "父分类ID"
varchar name "分类名称"
int level "层级: 1/2"
int show_status "显示状态"
}
ProductAttributeCategory["属性分类"] {
bigint id PK
varchar name "分类名称"
int attribute_count "属性数量"
}
ProductAttribute["商品属性"] {
bigint id PK
bigint product_attribute_category_id FK
varchar name "属性名称"
int type "类型: 0参数 1规格"
int input_type "录入方式"
varchar value_list "可选值列表"
}
ProductAttributeValue["属性值"] {
bigint id PK
bigint product_id FK
bigint product_attribute_id FK
varchar value "属性值"
}
FeightTemplate["运费模板"] {
bigint id PK
varchar name "模板名称"
int charge_type "计费方式"
decimal first_weight "首重"
decimal first_fee "首费"
}
PmsComment["商品评论"] {
bigint id PK
bigint product_id FK
bigint member_id FK
int star "评分"
varchar content "评论内容"
int show_status "是否显示"
}
MemberPrice["会员价"] {
bigint id PK
bigint product_id FK
bigint member_level_id FK
decimal member_price "会员价"
}
Product ||--o{ SkuStock : "包含SKU"
Product }o--|| Brand : "归属品牌"
Product }o--|| ProductCategory : "所属分类"
Product }o--|| FeightTemplate : "运费模板"
Product ||--o{ ProductAttributeValue : "拥有属性值"
Product ||--o{ PmsComment : "收到评论"
Product ||--o{ MemberPrice : "设置会员价"
ProductCategory }o--|| ProductAttributeCategory : "关联属性模板"
ProductAttributeCategory ||--o{ ProductAttribute : "定义属性"
ProductCategory ||--o{ ProductCategory : "父子分类"
2. OMS 订单域
订单域覆盖交易全生命周期，从购物车到售后。
mermaid
erDiagram
Order["订单"] {
bigint id PK
bigint member_id FK
varchar order_sn "订单编号"
int status "订单状态"
decimal total_amount "商品总额"
decimal freight_amount "运费"
decimal promotion_amount "促销优惠"
decimal coupon_amount "优惠券抵扣"
decimal integration_amount "积分抵扣"
decimal pay_amount "实付金额"
int pay_type "支付方式"
}
OrderItem["订单商品明细"] {
bigint id PK
bigint order_id FK
bigint product_id FK
varchar product_pic "商品图片"
varchar product_name "商品名称"
varchar sp_data "规格快照"
decimal product_price "下单时单价"
int product_quantity "购买数量"
}
OrderOperateHistory["操作记录"] {
bigint id PK
bigint order_id FK
varchar operate_man "操作人"
datetime create_time "操作时间"
int order_status "操作后状态"
varchar note "备注"
}
OrderReturnApply["退货申请"] {
bigint id PK
bigint order_id FK
bigint product_id FK
int status "退货状态"
decimal return_amount "退款金额"
varchar reason "退货原因"
varchar description "问题描述"
}
OrderReturnReason["退货原因"] {
bigint id PK
varchar name "原因内容"
int sort "排序"
int status "启用状态"
}
CartItem["购物车项"] {
bigint id PK
bigint member_id FK
bigint product_id FK
bigint product_sku_id FK
int quantity "数量"
decimal price "加入时价格"
int product_attr "规格快照"
}
OrderSetting["订单设置"] {
bigint id PK
int normal_order_overtime "普通超时分钟"
int flash_order_overtime "秒杀超时分钟"
int confirm_overtime "自动收货天数"
}
Order ||--o{ OrderItem : "包含明细"
Order ||--o{ OrderOperateHistory : "操作记录"
Order ||--o{ OrderReturnApply : "退货申请"
OrderReturnApply }o--|| OrderReturnReason : "退货原因"
CartItem }o--|| Order : "转化为订单"
3. UMS 用户权限域
用户权限域包含前台会员体系和后台 RBAC 权限体系两部分。
mermaid
erDiagram
Member["会员"] {
bigint id PK
bigint member_level_id FK
varchar username "用户名"
varchar phone "手机号"
int status "状态: 0禁用 1启用"
int integration "积分"
int growth "成长值"
}
MemberLevel["会员等级"] {
bigint id PK
varchar name "等级名称"
int growth_point "成长值门槛"
int free_freight_point "免运费门槛"
int comment_growth_point "评论赠成长值"
}
MemberReceiveAddress["收货地址"] {
bigint id PK
bigint member_id FK
varchar name "收货人"
varchar phone "电话"
varchar province "省"
varchar city "市"
varchar detail_address "详细地址"
int default_status "是否默认"
}
IntegrationChangeHistory["积分变动"] {
bigint id PK
bigint member_id FK
int change_type "变动类型"
int integration "变动数量"
}
GrowthChangeHistory["成长值变动"] {
bigint id PK
bigint member_id FK
int change_type "变动类型"
int growth "变动数量"
}
Admin["管理员"] {
bigint id PK
varchar username "用户名"
varchar icon "头像"
int status "状态"
}
Role["角色"] {
bigint id PK
varchar name "角色名称"
varchar description "描述"
int status "启用状态"
}
Menu["菜单"] {
bigint id PK
bigint parent_id FK "父菜单"
varchar title "菜单名称"
int level "层级"
varchar icon "图标"
}
Resource["资源"] {
bigint id PK
bigint category_id FK
varchar name "资源名称"
varchar url "接口路径"
}
Member }o--|| MemberLevel : "所属等级"
Member ||--o{ MemberReceiveAddress : "收货地址"
Member ||--o{ IntegrationChangeHistory : "积分记录"
Member ||--o{ GrowthChangeHistory : "成长值记录"
Admin }o--o{ Role : "分配角色"
Role }o--o{ Menu : "可访问菜单"
Role }o--o{ Resource : "可访问资源"
Menu ||--o{ Menu : "父子菜单"
Resource }o--|| ResourceCategory : "资源分类"
ResourceCategory["资源分类"] {
bigint id PK
varchar name "分类名称"
int sort "排序"
}
4. SMS 营销域
营销域覆盖广告、推荐、促销、优惠券等运营工具。
mermaid
erDiagram
HomeAdvertise["轮播广告"] {
bigint id PK
varchar name "广告名称"
varchar pic "图片"
int type "类型"
varchar url "链接"
int status "状态"
}
FlashPromotion["秒杀活动"] {
bigint id PK
varchar title "活动标题"
datetime start_time "开始时间"
datetime end_time "结束时间"
int status "状态"
}
FlashPromotionSession["秒杀场次"] {
bigint id PK
bigint flash_promotion_id FK
varchar name "场次名称"
time start_time "开场时间"
time end_time "结束时间"
}
FlashPromotionProductRelation["秒杀商品"] {
bigint id PK
bigint flash_promotion_session_id FK
bigint product_id FK
decimal flash_promotion_price "秒杀价"
int flash_promotion_count "秒杀库存"
int limit_per_member "每人限购"
}
Coupon["优惠券"] {
bigint id PK
int type "类型: 0全场 1指定"
decimal amount "面额"
decimal use_threshold "使用门槛"
int publish_count "发行量"
int use_count "已领数量"
datetime start_time "有效期起"
datetime end_time "有效期止"
}
CouponHistory["优惠券领取记录"] {
bigint id PK
bigint coupon_id FK
bigint member_id FK
int use_status "使用状态: 0未用 1已用 2已过期"
bigint order_id FK "使用订单"
datetime use_time "使用时间"
}
FullReduction["满减规则"] {
bigint id PK
bigint product_id FK
decimal full_price "满足金额"
decimal reduce_price "减免金额"
}
FlashPromotion ||--o{ FlashPromotionSession : "包含场次"
FlashPromotionSession ||--o{ FlashPromotionProductRelation : "包含商品"
Coupon ||--o{ CouponHistory : "领取记录"
Coupon }o--o{ Product : "适用范围"
FullReduction }o--|| Product : "商品满减"
5. CMS 内容域
内容域覆盖专题、话题、帮助、优选等运营内容。
mermaid
erDiagram
HelpCategory["帮助分类"] {
bigint id PK
varchar name "分类名称"
varchar icon "图标"
int sort "排序"
int show_status "显示状态"
}
Help["帮助文章"] {
bigint id PK
bigint help_category_id FK
varchar name "文章标题"
varchar content "正文"
int read_count "阅读量"
}
SubjectCategory["专题分类"] {
bigint id PK
varchar name "分类名称"
}
Subject["专题"] {
bigint id PK
bigint subject_category_id FK
varchar title "专题标题"
varchar pic "封面"
varchar content "正文"
int read_count "阅读量"
}
TopicCategory["话题分类"] {
bigint id PK
varchar name "分类名称"
}
Topic["话题"] {
bigint id PK
bigint topic_category_id FK
varchar title "话题标题"
varchar content "正文"
}
PreferenceArea["优选专区"] {
bigint id PK
varchar name "专区名称"
varchar pic "图片"
int sort "排序"
int show_status "显示状态"
}
MemberReport["举报记录"] {
bigint id PK
bigint member_id FK "举报人"
int report_type "举报类型"
varchar content "举报内容"
int handle_status "处理状态"
}
HelpCategory ||--o{ Help : "包含文章"
SubjectCategory ||--o{ Subject : "包含专题"
TopicCategory ||--o{ Topic : "包含话题"
Subject }o--o{ Product : "关联商品"
PreferenceArea }o--o{ Product : "推荐商品"
6. MongoDB 集合（补充）
收藏和浏览历史存储在 MongoDB 中，通过 member_id 关联 MySQL 中的会员数据。
mermaid
erDiagram
Member["会员 (MySQL)"] {
bigint id PK
varchar username
}
ProductCollection["商品收藏 (MongoDB)"] {
ObjectId id PK
bigint member_id "会员ID"
bigint product_id "商品ID"
string product_name "商品名称"
string product_pic "商品图片"
datetime create_time "收藏时间"
}
ReadHistory["浏览历史 (MongoDB)"] {
ObjectId id PK
bigint member_id "会员ID"
bigint product_id "商品ID"
string product_name "商品名称"
string product_pic "商品图片"
datetime create_time "浏览时间"
}
Member ||--o{ ProductCollection : "收藏商品"
Member ||--o{ ReadHistory : "浏览记录"
说明：
- MongoDB 集合采用文档模型，适合非结构化的收藏/历史场景
- 通过
member_id和product_id与 MySQL 中的会员和商品关联 - 浏览历史按时间倒序展示，支持分页查询