BD modeling for "multi-level marketing"

Question

Personal talk,

I’m working on an app that consists of people referral, same as so-called multi-level marketing.

The general idea consists of:

Person A indicates person B (LEVEL 1) if person B indicates person C (N1 of person B and N2 of person A) and so on to person N5.

How can I plan this kind of table?

NOTE: It will need to be in a postgresql DBMS, as it will be an addition to an existing system

I’m open to any suggestion

Answer 1

Make a table that has a FK for itself. So you use recursion to describe the hierarchical structure of indications. Example:

CREATE TABLE pessoas (
  id serial PRIMARY KEY,
  indicador integer REFERENCES pessoas(id) ON DELETE CASCADE
  -- Demais campos
);