分销系统数据库设计篇

发表于 2022年05月10日
后端

分销项目，少不了分销者关联关系、订单关联、分销收益计算，这也是基础核心的部分。不同的需求会有不同的设计标准和要求，下面是一份分销系统核心数据库设计的示例可供参考：

目录表

分销用户表

CREATE TABLE `fenxiao_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pub_open_id` varchar(255) NOT NULL DEFAULT '' COMMENT '公众号平台openid',
  `created_at` datetime NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `taoke_code` varchar(255) NOT NULL DEFAULT '' COMMENT '淘客码',
  `mobile` varchar(255) NOT NULL DEFAULT '' COMMENT '手机号',
  `city` varchar(255) DEFAULT NULL,
  `avatar` varchar(255) NOT NULL DEFAULT '',
  `password` varchar(255) NOT NULL DEFAULT '',
  `nickname` varchar(255) NOT NULL DEFAULT '',
  `gender` tinyint(4) DEFAULT NULL,
  `mini_open_id` varchar(255) NOT NULL DEFAULT '' COMMENT '小程序openid',
  `union_id` varchar(255) NOT NULL COMMENT '公共平台的union_id',
  `visit_code` varchar(255) NOT NULL COMMENT '分销邀请码',
  `visit_h5_link` varchar(255) NOT NULL,
  `vist_mini_qrcode` varchar(255) NOT NULL,
  `user_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1普通用户 2是分销用户',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10046 DEFAULT CHARSET=utf8mb4

CREATE TABLE `fenxiao_user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`pub_open_id` varchar(255) NOT NULL DEFAULT '' COMMENT '公众号平台openid',

`created_at` datetime NOT NULL,

`status` tinyint(4) NOT NULL DEFAULT '1',

`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

`taoke_code` varchar(255) NOT NULL DEFAULT '' COMMENT '淘客码',

`mobile` varchar(255) NOT NULL DEFAULT '' COMMENT '手机号',

`city` varchar(255) DEFAULT NULL,

`avatar` varchar(255) NOT NULL DEFAULT '',

`password` varchar(255) NOT NULL DEFAULT '',

`nickname` varchar(255) NOT NULL DEFAULT '',

`gender` tinyint(4) DEFAULT NULL,

`mini_open_id` varchar(255) NOT NULL DEFAULT '' COMMENT '小程序openid',

`union_id` varchar(255) NOT NULL COMMENT '公共平台的union_id',

`visit_code` varchar(255) NOT NULL COMMENT '分销邀请码',

`visit_h5_link` varchar(255) NOT NULL,

`vist_mini_qrcode` varchar(255) NOT NULL,

`user_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1普通用户 2是分销用户',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10046 DEFAULT CHARSET=utf8mb4

简单讲下核心的几个字段

因为项目是基于微信生态的，所以主要推广平台为微信公众号h5 和微信小程序 , 其中三个字段 pub_open_id,mini_open_id,union_id 分别为微信公众号的 openid，小程序的 openid 以及打通两者的开发平台上的联合 id。这样设计方便后期账户打通。

visit_code 字段是邀请码，可以在注册链接上带上用来识别用户之间的邀请关系。
visit_h5_link 和 vist_mini_qrcode 分别是带有邀请码的 h5邀请链接以及邀请小程序码图片地址

taoke_code 字段是淘客链接生成需要的一个淘客邀请码，可以理解为 pid 广告位的一个东西

分销用户邀请关系表

CREATE TABLE `fenxiao_visit_relation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_user_id` int(11) NOT NULL,
  `visited_user_id` int(11) NOT NULL,
  `level` varchar(255) NOT NULL COMMENT '邀请等级',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1正常',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4

CREATE TABLE `fenxiao_visit_relation` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`visit_user_id` int(11) NOT NULL,

`visited_user_id` int(11) NOT NULL,

`level` varchar(255) NOT NULL COMMENT '邀请等级',

`created_at` datetime NOT NULL,

`updated_at` datetime NOT NULL,

`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1正常',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4

用户关系表相对来说也不复杂，主要是来找到用户表 fenxiao_user 里的邀请关系。使用主表里的 visit_code 邀请码生成的小程序码或者 h5 邀请链接等注册登录后，可通过 visit_code 来找到当前注册用户的邀请者的 user_id，这里当前被邀请的用户 ID 是 visited_user_id, 发起邀请者的用户 ID 是 visit_user_id。

假设我们做了两级分销。当前注册的用户 C 的邀请者是 B，然后去查邀请表有 A 推荐 B 注册的，那么我们就可以在邀请关系表里面新增两条记录

|visit_user_id | visited_user_id| level |
| B | C | 1 |
| A | C | 2 |

|visit_user_id | visited_user_id| level |

| B | C | 1 |

| A | C | 2 |

如上表 A 邀请过 B，B 再邀请 C，C 注册以后就有两条关于 C 的邀请关系，因为 B 是直接邀请，所以 level = 1 A 则是 C 的间接邀请者 level = 2。

通过上面的表就很容易的去查某个用户的下级以及下下级推荐关系。

分销订单表

CREATE TABLE `fenxiao_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order_status` tinyint(4) NOT NULL,
  `project_id` tinyint(4) NOT NULL,
  `pay_price` int(11) DEFAULT NULL,
  `order_id` varchar(255) DEFAULT NULL,
  `pay_time` int(11) DEFAULT NULL COMMENT '支付时间',
  `origin_profit` int(11) DEFAULT NULL,
  `profit` int(11) DEFAULT NULL COMMENT '订单返佣金额',
  `sms_title` varchar(255) DEFAULT NULL COMMENT '订单标题\n\n订单标题\n\n',
  `quantity` tinyint(4) DEFAULT NULL COMMENT '退款笔数',
  `refund_time` int(11) DEFAULT NULL,
  `refund_profit` int(11) DEFAULT NULL,
  `create_time` int(11) DEFAULT NULL,
  `pid` varchar(255) DEFAULT NULL,
  `promotion_rate` int(11) DEFAULT NULL COMMENT '给用户的比列',
  `origin_rate` int(11) DEFAULT NULL COMMENT '原始比列',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `refund_price` int(11) DEFAULT NULL COMMENT '0',
  `project_type` int(11) DEFAULT NULL,
  `level` tinyint(4) NOT NULL DEFAULT '0',
  `origin_price` int(11) NOT NULL DEFAULT '0' COMMENT '原价',
  `order_user_id` int(11) NOT NULL COMMENT '下单人',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10333 DEFAULT CHARSET=utf8mb4

CREATE TABLE `fenxiao_order` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`order_status` tinyint(4) NOT NULL,

`project_id` tinyint(4) NOT NULL,

`pay_price` int(11) DEFAULT NULL,

`order_id` varchar(255) DEFAULT NULL,

`pay_time` int(11) DEFAULT NULL COMMENT '支付时间',

`origin_profit` int(11) DEFAULT NULL,

`profit` int(11) DEFAULT NULL COMMENT '订单返佣金额',

`sms_title` varchar(255) DEFAULT NULL COMMENT '订单标题\n\n订单标题\n\n',

`quantity` tinyint(4) DEFAULT NULL COMMENT '退款笔数',

`refund_time` int(11) DEFAULT NULL,

`refund_profit` int(11) DEFAULT NULL,

`create_time` int(11) DEFAULT NULL,

`pid` varchar(255) DEFAULT NULL,

`promotion_rate` int(11) DEFAULT NULL COMMENT '给用户的比列',

`origin_rate` int(11) DEFAULT NULL COMMENT '原始比列',

`created_at` datetime DEFAULT NULL,

`updated_at` datetime DEFAULT NULL,

`refund_price` int(11) DEFAULT NULL COMMENT '0',

`project_type` int(11) DEFAULT NULL,

`level` tinyint(4) NOT NULL DEFAULT '0',

`origin_price` int(11) NOT NULL DEFAULT '0' COMMENT '原价',

`order_user_id` int(11) NOT NULL COMMENT '下单人',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10333 DEFAULT CHARSET=utf8mb4

这里主要介绍下订单如何生成以及更新的方法

生成的时候往往是提交订单，可以利用 CPS 平台的订单回调通知方式 + 定时拉取 CPS 平台订单列表的方式来生成自己的分销订单表。
无论是回调通知还是订单列表都会有一个携带淘客推广为 pid 的一个参数，以及订单 ID。

然后就可以入库订单相关信息，同样的，我们可以记录订单到底是谁推广的，通过订单中的 pid 去查找到直接推广的UserID，然后通过 UserID 查找邀请关系表，分别按照自己设定的每一级别推广分佣比例为这一系列用户分别生成一条订单记录，user_id 是产生订单收益的当前用户 ID，order_user_id 保存真正直接链接推广下单的用户 ID，同样要保存一下邀请表中的 level 订单列表可以展示出是下级还是下下级出的单。

依旧拿上面的邀请关系来说，C 的推广链接下的单

|user_id | order_user_id | level | profit |
| C | C | 0 | 60%|
| B | C | 1 | 20%|
| A | C | 2 | 10%|

| C | C | 0 | 60%|

| B | C | 1 | 20%|

| A | C | 2 | 10%|

通过上面的订单表，我们很容易知道有人通过用户 C 的推广链接下单，用户 A 的下下级推广出去成交的，所以可以获得该笔订单的 10% 推广费。

闭包表分销数据库设计

通过上面的示例，基本已经能看清分销数据表的设计关系，但感觉有些复杂？那来个通用经典的。

闭包表是解决分层存储一个简单而又优雅的解决方案，它记录了表中所有的节点关系，并不仅仅是直接的父子关系。

在闭包表的设计中，额外创建了一张TreePaths的表(空间换取时间)，它包含两列，每一列都是一个指向Comments中的CommentId的外键。

CREATE TABLE `Comment4` (
`id`  int AUTO_INCREMENT  ,
`words`  varchar(255) NULL ,
PRIMARY KEY (`id`)
);

CREATE TABLE `Comment4` (

`id` int AUTO_INCREMENT ,

`words` varchar(255) NULL ,

PRIMARY KEY (`id`)

);

父子关系表：

CREATE TABLE `RelaComment` (
`id`  int AUTO_INCREMENT  ,
`parentid`  int not null  ,
`childid`  int not null  ,
PRIMARY KEY (`id`)
);

CREATE TABLE `RelaComment` (

`id` int AUTO_INCREMENT ,

`parentid` int not null ,

`childid` int not null ,

PRIMARY KEY (`id`)

);

在这种设计中，Comments表将不再存储树结构，而是将书中的祖先-后代关系存储为RelaComment的一行，即使这两个节点之间不是直接的父子关系；同时还增加一行指向节点自己。

插入新节点

要插入一个新的叶子节点，应首先插入一条自己到自己的关系，然后搜索relacomment 表中后代是评论5的节点，增加该节点与要插入的新节点的"祖先-后代"关系。

insert into Comment4(words) values("iam 5")
INSERT INTO relacomment(parentid,childid)
    SELECT t.parentid,5
    FROM relacomment AS t
    WHERE t.childid = 4
    UNION ALL
    SELECT 5,5

insert into Comment4(words) values("iam 5")

INSERT INTO relacomment(parentid,childid)

SELECT t.parentid,5

FROM relacomment AS t

WHERE t.childid = 4

UNION ALL

SELECT 5,5

查询所有后代节点(查子树)

SELECT c.* FROM comment4 AS c INNER JOIN relacomment t on c.id = t.childid WHERE t.parentid = 2

1	SELECT c.* FROM comment4 AS c INNER JOIN relacomment t on c.id = t.childid WHERE t.parentid = 2

查询评论4的所有祖先(查祖先树)

SELECT c.* FROM comment4 AS c INNER JOIN relacomment t on c.id = t.parentid WHERE t.childid = 4

1	SELECT c.* FROM comment4 AS c INNER JOIN relacomment t on c.id = t.parentid WHERE t.childid = 4

删除叶子节点

DELETE FROM relacomment WHERE childid= 5

1	DELETE FROM relacomment WHERE childid= 5

删除子树

　DELETE FROM relacomment
　　WHERE descendant
　　IN(SELECT childid FROM relacomment WHERE parentid = 2)

　DELETE FROM relacomment

　　WHERE descendant

　　IN(SELECT childid FROM relacomment WHERE parentid = 2)

要删除一颗完整的子树，比如评论4和它的所有后代，可删除所有在relacomment表中的后代为4的行，以及那些以评论4的后代为后代的行：

另外，闭包表还可以优化，如增加一个path_length字段，自我引用为0，直接子节点为1，再一下层为2，一次类推，查询直接自子节点就变得很简单。

闭包表是最通用的设计，并且最灵活，易扩展，并且一个节点能属于多棵树，能减少冗余的计算时间。但它要求一张额外的表来存储关系，是一个空间换取时间的方案。

总结

分销系统在市场中的使用已经非常广泛成熟，国内典型的PDD、美团、淘宝、京东的各种联盟、腾讯云、阿里云都是非常为成功的运用了分销系统，每每活动造势都会取得不错的效果，日常挂连接获取流量效果更佳。同时也有非常多的个人、团队，甚至公司专业就运营各大平台的分销代理，获得了不错的效果。

标签：三级分销 , 分销 , 数据库原文连接：分销系统数据库设计篇 所有媒体，可在保留署名、原文连接的情况下转载，若非则不得使用我方内容。

Google Analytics 优化/Google Analytics加载失败导致阻塞的解决方法 Flutter 3 发布，一文看懂新功能及特性