Member Avatar for ansari.wajid

Hi friends

Has anyone done a db for a multi-level compensation structure? I'm talking about a company that is MLM or a multi-level marketing network. Where distributors sign up under other distributors and the "downline" can go down many levels(but limited levels!). I need a sample MLM structure.Could anybody help me please.

Thank You

  • 3 Contributors
  • 2 Replies
  • 250 Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by kingmakerram87

Dani AI

Generated

— A practical, production-friendly pattern is to store sponsors in a simple members table and maintain a separate closure table (ancestor/descendant) for fast upline/downline queries and limited-depth enforcement. That same closure table makes commission runs and reporting straightforward. For this is a usable backbone for an MLM app; 's post appears unrelated to the design question.

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  sponsor_id INTEGER,                -- FK to members.id or NULL for roots
  join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status VARCHAR(20) DEFAULT 'active'
);

CREATE TABLE member_closure (
  ancestor_id INTEGER NOT NULL,
  descendant_id INTEGER NOT NULL,
  depth INTEGER NOT NULL,            -- 0 = self, 1 = direct sponsor, ...
  PRIMARY KEY (ancestor_id, descendant_id)
);
-- maintain closure on new member (:new_id) with sponsor :sponsor_id
INSERT INTO member_closure (ancestor_id, descendant_id, depth)
SELECT ancestor_id, :new_id, depth + 1 FROM member_closure WHERE descendant_id = :sponsor_id
UNION ALL
SELECT :new_id, :new_id, 0;

-- get downline up to N levels
SELECT m.* FROM member_closure c JOIN members m ON m.id = c.descendant_id
WHERE c.ancestor_id = :id AND c.depth BETWEEN 1 AND :max_depth;

-- simple commission run (orders, commission_rules, commissions tables assumed)
INSERT INTO commissions (member_id, order_id, amount, level, created_at)
SELECT c.ancestor_id, o.id, o.total * r.percent/100.0, c.depth, CURRENT_TIMESTAMP
FROM orders o
JOIN member_closure c ON c.descendant_id = o.buyer_id
JOIN commission_rules r ON r.level = c.depth
WHERE o.id = :order_id;

Notes and pitfalls: enforce FK constraints and a check to prevent sponsor_id = id; use transactions when inserting/updating sponsors so closure stays consistent; enforce max-depth in the insert path; reparenting (moving a subtree) requires careful updates to closure entries or a rebuild of the affected subtree. Closure tables give fastest reads but grow O(N^2) worst-case—consider materialized-path or a graph DB for extremely large networks. Index ancestor_id and descendant_id, partition/archive old data for scale, and add integrity tests. Finally, ensure the compensation plan and rollout comply with local laws; MLM vs. pyramid distinctions are legal matters.

Member Avatar for mamabile

I am in need of MLM software and if you get a hold of a MLM program can you please let me get a copy? Thank You!

Member Avatar for kingmakerram87

indianjobtips.com/freeads

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.