多层营销（MLM/金字塔模式）关系型数据库层级佣金关联设计难点咨询

嘿，我明白你现在的困境——这种多层级佣金的关联逻辑确实很容易在简单父子表上卡壳，毕竟你需要追踪每个用户的整个推荐树，还要对应不同层级的佣金比例对吧？结合你的业务规则（最多5层佣金，每层固定金额），我给你两种可行的数据库设计方案，你可以根据自己的业务场景选择：

这种思路是在用户被招募时，就提前把他和所有上级（直到第5层）的关联关系、对应佣金金额都存下来，后续查询直接查表即可，不用实时递归计算。

必要表结构 #

1. 用户基础表 #

先建一个存储用户核心信息的基础表：

CREATE TABLE users (
    user_id VARCHAR(50) PRIMARY KEY, -- 比如 customer1、customer2 这类标识
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 可添加其他字段：邮箱、手机号等
    UNIQUE KEY unique_username (username)
);

2. 层级佣金关联表 #

这个表专门存储每个用户与所有上级的层级关系及对应佣金：

CREATE TABLE user_referral_hierarchy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_user_id VARCHAR(50) NOT NULL, -- 可获得佣金的上级用户ID
    referred_user_id VARCHAR(50) NOT NULL, -- 触发佣金的下级用户ID
    level INT NOT NULL CHECK (level BETWEEN 1 AND 5), -- 层级：1=直接推荐，最高到5级
    commission_amount DECIMAL(10,2) NOT NULL, -- 对应层级的佣金金额
    FOREIGN KEY (referrer_user_id) REFERENCES users(user_id),
    FOREIGN KEY (referred_user_id) REFERENCES users(user_id),
    UNIQUE KEY unique_referrer_referred_level (referrer_user_id, referred_user_id, level) -- 避免重复存储同一关系
);

数据插入逻辑 #

当新用户（比如customer4）被customer2招募时：

• 首先插入一级关系：referrer=customer2，referred=customer4，level=1，commission=50
• 然后递归遍历customer2的所有上级（直到层级+1不超过5）：比如customer1是customer2的一级上级，就插入referrer=customer1，referred=customer4，level=2，commission=25
• 如果customer1还有上级，继续插入level=3、commission=15的记录，直到level达到5为止

你可以用业务代码或者数据库触发器实现这个逻辑，确保新用户注册时自动生成所有符合规则的层级关联记录。

查询示例 #

要快速查询customer1所有能拿到佣金的下级及对应金额：

SELECT referred_user_id, level, commission_amount
FROM user_referral_hierarchy
WHERE referrer_user_id = 'customer1';

这种方案的优势是查询速度极快，适合高并发场景；缺点是占用更多存储，若后续佣金规则（层级数、金额）变化，需要批量更新历史数据。

如果不想预存大量关联数据，可以利用数据库的递归查询功能（比如MySQL的WITH RECURSIVE、PostgreSQL的递归CTE），实时计算每个用户的推荐链及对应佣金。

必要表结构 #

只需要一个直接推荐关联表，存储最基础的父-子推荐关系：

CREATE TABLE direct_referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parent_user_id VARCHAR(50) NOT NULL, -- 直接推荐人
    child_user_id VARCHAR(50) NOT NULL, -- 被推荐人
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_user_id) REFERENCES users(user_id),
    FOREIGN KEY (child_user_id) REFERENCES users(user_id),
    UNIQUE KEY unique_parent_child (parent_user_id, child_user_id) -- 避免重复绑定同一推荐关系
);

递归查询示例（以MySQL为例） #

要计算customer1所有能拿到佣金的下级及对应金额：

WITH RECURSIVE referral_chain AS (
    -- 初始条件：直接推荐的一级用户
    SELECT 
        parent_user_id AS referrer,
        child_user_id AS referred,
        1 AS level,
        50 AS commission_amount
    FROM direct_referrals
    WHERE parent_user_id = 'customer1'
    
    UNION ALL
    
    -- 递归步骤：遍历下级的下级，直到层级达到5
    SELECT 
        rc.referrer,
        dr.child_user_id AS referred,
        rc.level + 1 AS level,
        -- 根据层级匹配对应佣金
        CASE rc.level + 1
            WHEN 2 THEN 25
            WHEN 3 THEN 15
            WHEN 4 THEN 10
            WHEN 5 THEN 5
        END AS commission_amount
    FROM referral_chain rc
    JOIN direct_referrals dr ON rc.referred = dr.parent_user_id
    WHERE rc.level < 5 -- 超过5层不再计算
)
SELECT referrer, referred, level, commission_amount
FROM referral_chain;

这种方案的优势是存储占用少，佣金规则变化时只需修改查询中的CASE语句即可；缺点是推荐树过深时，递归查询的性能会下降，适合用户量中等、查询频率不极高的场景。

不管选哪种方案，都建议单独建一个佣金发放记录表，避免重复计算或数据不一致：

CREATE TABLE commission_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL, -- 获得佣金的用户
    trigger_user_id VARCHAR(50) NOT NULL, -- 触发佣金的下级用户
    level INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'cancelled') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (trigger_user_id) REFERENCES users(user_id)
);

当新用户注册时，根据层级关系生成对应的佣金记录，标记为pending，后续可批量处理发放。

你可以先造一些测试数据（比如customer1→customer2→customer4→customer6→customer8→customer10），验证两种方案是否能正确计算各层级的佣金，确保符合业务规则。

内容的提问来源于stack exchange，提问作者Gorhell