What is the best database structure for implementing a referral system with 3 levels in PHP?

When implementing a referral system with 3 levels in PHP, the best database structure would involve having a single table to store user information and another table to store the referral relationships between users. Each user record would have a field to store their referrer's ID, allowing for tracking of multiple levels of referrals. 

// Database structure
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    referrer_id INT
);

CREATE TABLE referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    referrer_id INT
);

// PHP code to track referrals
function addReferral($userId, $referrerId) {
    // Insert referral record into referrals table
    $sql = "INSERT INTO referrals (user_id, referrer_id) VALUES ($userId, $referrerId)";
    // Execute SQL query
}

function getReferralChain($userId) {
    $referralChain = array();
    $referrerId = $userId;
    
    while ($referrerId != null) {
        // Get referrer ID from referrals table
        $sql = "SELECT referrer_id FROM referrals WHERE user_id = $referrerId";
        // Execute SQL query and store result in $referrerId
        $referralChain[] = $referrerId;
    }
    
    return $referralChain;
}

Keywords

database structure referral system 3 levels PHP implementation

Related Questions