Affiliate Management

• Introduction
• Tables
  • Affiliate_apps
  • Affiliate_users
  • Affiliate_visitors
  • Affiliate_payments
  • Applications
  • Aff_users_log
  • Oc_users

Introduction

The OTAS system offers affiliate support, enabling interested users to sign up and create a unique URL for sharing with their audience. For every student who registers through this URL, the user earns a commission on successful applications.

Tables

The ERD for the affiliate tables in OTAS shows the structure and relationships between affiliate applications, users, visits, payments, and their interactions with the overall applications and user system. Here's an analysis of the relationships as depicted:

• affiliate_apps: This table store information about affiliate applications, like commission and the affiliate user id.

• affiliate_users: This table is related to affiliate_apps and likely stores user data specific to affiliates, which could include their account information within the affiliate applications.

• affiliate_visitors: This table appears to track visits or traffic from affiliate sources, probably recording each visit's details to an affiliate's site or application.

• affiliate_payments: This table is likely used to record financial transactions related to affiliates, such as commissions.

• applications: While not directly related to the affiliate tables, it's connected via affiliate_apps. This suggests that applications (most likely student applications within OTAS) can be associated with specific affiliate apps, possibly to track where the student applications are coming from or if any affiliate is linked to an application.

• aff_users_log: This table stores the history of changes in affiliate users and their commissions.

• oc_users: This is the main users table previously described, indicating that affiliate users are also part of the general user system and may have roles or actions within the broader OTAS environment.

The ERD suggests a system where affiliate activities, ranging from application usage to visitor tracking and payment processing, are closely monitored and linked to both the general application process and the user management system within OTAS. This allows for a detailed understanding and management of the affiliate program's impact on user engagement and financials.

Affiliate_apps

The affiliate_apps table seems to be designed to manage the relationship between affiliate users and the applications they're associated with, along with the commission details. Here's a summary of each column:

• id: Acts as a unique identifier for each record within the table, incrementing automatically.
• aff_user_id: Stores the ID of the affiliate user, linking to the affiliate_users table.
• app_id: Holds the identifier for the specific application associated with the affiliate user.
• commission: Records the commission amount or percentage that the affiliate user is entitled to for their association with the app.
• created_at: Timestamps the creation of the record, indicating when the relationship between the affiliate user and the application was established.
• updated_at: Records the most recent update to the record, tracking any changes to the relationship or commission details.

This table structure allows OTAS to track which affiliate users are connected to which applications and the commissions they earn, providing a clear audit trail and financial management for the affiliate program.

Affiliate_users

The affiliate_users table appears to be designed to store essential information about users who are part of the affiliate program in OTAS. Here's a brief overview of its columns and their purposes:

• id: A unique identifier for each affiliate user, which increments automatically.
• name: The full name of the affiliate user.
• phone: The telephone contact number for the affiliate user.
• email: The email address of the affiliate user.
• affiliate_code: A unique, randomly generated code assigned to the affiliate user for tracking purposes or referrals.
• updated_at: Timestamp of the most recent update made to the affiliate user's record.
• deleted_at: Timestamp for when the affiliate user's record was soft-deleted, allowing for data recovery if needed.
• created_at: Timestamp for when the affiliate user's record was initially created.
• gender: The gender of the affiliate user.
• birth_date: The birth date of the affiliate user, used for age verification or demographic analysis.
• nationality: The ID linking to the spotlayerteam_institutionsprograms_country table for the user's nationality.
• country: The ID linking to the spotlayerteam_institutionsprograms_country table for the user's country of residence.
• total_visitors: A deprecated column, no longer in use, which may have previously tracked the number of visitors referred by the affiliate user.
• user_id: The ID of the associated user in the oc_users table, connecting the affiliate profile to a user account within the OTAS system.

This table is crucial for managing affiliate user profiles, tracking their activity, and associating their actions with corresponding user accounts in the main OTAS user management system.

Affiliate_visitors

The affiliate_visitors table appears to be set up to track the visits to affiliate links, providing insights into the reach and engagement of affiliate marketing efforts. Here's a concise outline of the table's columns:

• id: Acts as the primary key, uniquely identifying each visitor record and automatically incrementing with each new entry.
• user_id: Serves as a foreign key that establishes a relationship with the affiliate_users table, linking each visit to the affiliate user who owns the respective affiliate link.
• ip_address: Records the IP address of the visitor's device, presumably for the purpose of tracking the source of each visit to the affiliate link.
• visit_count: Tally of the number of visits that have originated from a unique IP address, enabling the system to track repeat visits from the same source.

This table is critical for monitoring the performance of affiliate links, allowing the OTAS system to analyze traffic and recognize patterns in visitor behavior.

Affiliate_payments

The affiliate_payments table in OTAS is designed to record the financial transactions made to affiliate users as remuneration for their successful referrals that have resulted in completed applications. Here is a summary of the table's structure:

• id: Serves as the unique identifier for each payment entry, incrementing automatically.
• app_id: Links to the specific application associated with the payment, serving as a foreign key to the applications table.
• amount: Specifies the monetary value paid out in the transaction.
• user_id: Connects to the oc_users table as a foreign key, identifying the OTAS user account that corresponds to the affiliate user receiving the payment.
• created_at: Records the date and time when the payment record was initially created.
• updated_at: Captures the date and time of the latest modification to the payment record.

This table is essential for managing and tracking affiliate compensation, ensuring transparency and accuracy in the payment process for affiliate-driven applications within the OTAS platform.

Applications

The applications table, known in the database as spotlayerteam_institutionsprograms_application, is a central element in the OTAS system, and it has been previously discussed in the context of application management. This table tracks the various applications submitted through OTAS, capturing all relevant details pertaining to student applications to different programs and institutions.

Aff_users_log

The aff_users_log table is structured to track modifications to the records of affiliate users and their associated applications, ensuring a detailed history of changes for auditing and management purposes. Here's a breakdown of the columns in this table:

• id: Functions as the unique identifier for each entry, with an automatic increment for each new record.
• user_id: Acts as a foreign key linking to the oc_users table, identifying the OTAS user who made the change.
• aff_user_id: Serves as a foreign key linking to the affiliate user whose record was altered.
• attribute: Specifies the name of the data field or attribute in the record that experienced a change.
• old_value: Captures the previous value of the attribute before the change took place.
• new_value: Stores the updated value that replaced the old one.
• created_at: Timestamps the moment when the change was made to the record.
• updated_at: Ideally, this should timestamp the most recent update to the log entry itself, but since it typically mirrors the created_at in logs, it may be redundant unless log entries are updated after creation.
• app_id: A nullable foreign key that links to the applications table if the change pertains to an application record; it remains null if the change is only related to an affiliate user record.

This logging table is crucial for maintaining the integrity of the affiliate program, allowing administrators to track who made changes, what changes were made, and when these changes were implemented. It provides accountability and a clear change history for each affiliate-related record.

Oc_users

The oc_users table is fundamental to the OTAS user management system and contains detailed information about each user. As previously discussed in the Users Management documentation, it includes a range of columns such as id, name, email, various flags indicating user status and roles, timestamps for creation and updates, and other user-specific data.