Commission Structure

• Introduction
• Tables
  • Commissions
    • Attributes
    • Commission Types
  • Commission Details
    • Attributes
  • Commission Histories
    • Attributes
  • Application Commission
    • Attributes
  • Agent Commissions
    • Attributes
  • Agent Commissions Config
    • Attributes

Introduction

In this section, we will discuss the database architecture employed for commission calculations and commission configuration.

The Entity-Relationship Diagram (ERD) illustrates the database schema designed for managing commission calculations. The schema is composed of several interconnected entities:

• Commissions: Serves as the central entity and is connected to various aspects of the system, including:

  • Levels: Defines different tiers or levels that may influence commission structures.
  • Languages: Captures the languages applicable to the commission terms or conditions.
  • Programs: Connects to programs, possibly representing different products or services eligible for commissions.
  • Pending Data: Stores information on commissions that are awaiting processing or approval.

• Commission Details: Holds specific data points for individual commissions, including a unique value identifier.

• Commission Histories: Maintains records of all commission-related transactions or adjustments. This entity tracks changes over time with fields for user identification and event timestamps (denoted as 'after' and 'before').

• Application Commission and Agent Commissions: These are two parallel entities that manage commission data related to applications and agents, respectively. Both entities store amounts and associated messages, and are linked via application and agent identifiers to the central Commissions entity.

This ERD serves as a blueprint for the database's structure, detailing how commissions are calculated and tracked, the hierarchical relationship between different levels and programs, and the multilingual support for global operations. Each entity is designed to ensure that commission-related data is captured in a granular and traceable manner, enabling accurate calculation and comprehensive reporting.

Tables

Let's refine the description of the tables involved in commission configuration and calculation within the OTAS system:

Commissions

The Commissions table serves as a repository for all commission configurations within the system. Each commission configuration may be associated with an institution, thereby applying to all programs within it, or it may be tailored to a specific program. This allows for a flexible structuring of commission parameters that can accommodate both broad and narrow applicability.

Attributes

In the Commissions table of the OTAS system, the following key columns hold critical data for commission management, excluding standard metadata fields:

• Year Id: This column establishes a foreign key relationship with the spotlayerteam_institutionsprograms_academic_year table, indicating the academic year to which the commission applies.

• Effective Date: This nullable column specifies the date from which the commission becomes applicable. If set, the commission applies to all applications completed after this date. If null, it applies to all completed applications for the specified year_id.

• Type: This string column defines the commission's category as per the listed commission types.

• Levels: A nullable JSON column that stores the IDs of the levels eligible for the commission. If null, the commission is applicable across all levels.

• Languages: Similar to levels, this nullable JSON column includes the IDs of the languages for which the commission is applicable. A null value signifies its applicability to all languages.

• Fields: Another nullable JSON column that contains the IDs of the fields to which the commission applies. If null, it includes all fields.

• Programs: This nullable JSON column holds the program IDs to which the commission is linked. A null value indicates that the commission pertains to all programs.

• Programs Type: This column, defined as a string enum with values 'except' or 'include', dictates whether the commission is applied to all programs excluding the specified ones or only to the specified programs.

• Pending Data: A nullable JSON column that contains any modified data awaiting approval within an approved commission structure.

• Commissionable Columns: The commissionable_type and commissionable_id are polymorphic columns that can relate either to an institution or a program, defining the scope of the commission's applicability.

These columns collectively create a flexible framework for commission configuration, allowing for both broad and targeted commission strategies within various academic programs and institutional parameters.

Commission Types

public static $commissionTypes = [
    "none" => "None",
    "percentage" => "Percentage",
    "exact" => "Exact Amount",
    "range-amount" => "Variable Amount",
    "special-range-amount" => "Special Variable Amount",
    "range-percentage" => "Variable Percentage",
    "special-range-percentage" => "Special Variable Percentage",
];

Commission Details

The Commission Details table (commission_details) stores commission ranges and values, and is linked to the Commissions table via the commission_id foreign key.

Attributes

The Commission Details table features essential columns to define commission ranges and amounts:

• Ending In: Sets the upper boundary of the commission range.
• Value: Specifies a set commission amount, if applicable.

Note: For 'percentage' or 'exact' commission types, a single row is added to the Commission Details table with the ending_in column set to null.

Note: For range-type commissions, ranges inherently start at 1 and are not explicitly stored. Subsequent ranges begin immediately after the previous range's ending_in value plus one. The final range must be open-ended, lacking an ending_in value, to accommodate any additional applications.

Commission Histories

The Commission Histories table (commission_histories) records modifications to commission configurations, detailing who made changes, when they were made, and tracks approvals, rejections (along with any rejection messages), and any re-evaluations performed on the commission.

Attributes

The Commission Histories table stores all changes and auditing details, including comprehensive records of modifications made.

• User Id: A foreign key column linking to the oc_users table, storing the ID of the user who made the change.
• Event: A string column indicating the operation performed on the commission (e.g., created, updated, approved, rejected, status-approved, status-rejected, deleted, re-evaluated, restored).
• Before: A nullable JSON column capturing the values prior to the change.
• After: A nullable JSON column recording the values following the change.

Note: The before and after columns only contain the values that were changed, not the entire data set from the commission table.

Application Commission

The Application Commission table (application_commission) holds the commission amount for each application, establishing relations with both the spotlayerteam_institutionsprograms_application and commissions tables. Additionally, it includes a descriptive message explaining the origin of the commission amount.

Attributes

Let's focus on describing the most important columns in the application_commission table while omitting common columns like id, created_at, updated_at, and updated_by, which are typically self-explanatory.

• Application ID: Links to the spotlayerteam_institutionsprograms_application table, identifying the specific application.
• Commission ID: Connects to the commissions table, indicating the relevant commission configuration.
• Amount: Records the commission amount for the application.
• Message: Provides a detailed explanation of how the commission amount was derived.

Agent Commissions

The Agent Commissions table (agent_commissions) records the commission amounts for agents who facilitated applications. It establishes connections with the spotlayerteam_institutionsprograms_application table for application details, the oc_users table for agent information, and the commissions table for commission data. Additionally, this table includes a descriptive message that clarifies the basis of the calculated commission amount.

Attributes

Let's focus on describing the most important columns in the agent_commissions table while omitting common columns like id, created_at, updated_at, and updated_by, which are typically self-explanatory.

• Application ID: Links to the spotlayerteam_institutionsprograms_application table, identifying the specific application.
• Agent ID: Links to the oc_users table, identifying the specific agent.
• Commission ID: Connects to the commissions table, indicating the relevant commission configuration.
• Amount: Records the commission amount for the application.
• Message: Provides a detailed explanation of how the commission amount was derived.

Agent Commissions Config

The Agent Commissions Config table (agent_commissions_config) is designated for storing the percentage of commission assigned to agents for each academic year.

Attributes

• Agent ID: Connects to the oc_users table, specifying the particular agent.
• Year ID: Forms a foreign key link with the spotlayerteam_institutionsprograms_academic_year table.
• Commission: Documents the percentage of commission allocated for the specified academic year.

Note: The commission percentage specified in the Agent Commission Config is used to calculate a portion of the commission collected from the institution. This calculation is based on the commission configuration applied to applications created by the agent.

Audits Config

The audits table, utilized by laravel-auditing, tracks changes in the commission of the application history. Whenever an application's commission is calculated or altered post-completion, these modifications are recorded in the audits table. The structure of this table includes:

• id: An auto-incremental integer serving as the primary key.
• user_type & user_id: Polymorphic columns identifying the user who made the changes. OTAS employs KEERill\Users\Models\User as the authentication model, hence user_type consistently reflects this model.
• event: A string detailing the type of event (e.g., created, updated, deleted, restored).
• auditable_type & auditable_id: Polymorphic columns linking to the table and the specific row that was changed.
• old_values: A JSON column capturing the values before the change.
• new_values: A JSON column detailing the values following the change.
• url: The URL of the page where the change occurred.
• ip_address: The IP address of the user who made the change.
• user_agent: The browser's user agent of the user making the change.
• tags: A nullable column, typically null.
• created_at: Timestamp marking when the change was made.
• updated_at: Generally mirrors the created_at timestamp, as the historical record doesn't change.