# Complete Optimization Problem and Solution: riding_club

## 1. Problem Context and Goals

### Context  
The riding club is focused on enhancing its performance by strategically assigning players to coaches. The primary decision involves determining whether a player should be assigned to a specific coach, represented by binary decisions. The club's operational strategy is to maximize the total points earned from these assignments. This is achieved by leveraging compatibility scores between players and coaches, which serve as a measure of potential success in training outcomes. The club has established a minimum compatibility score that must be met for any player-coach pairing to ensure effective training sessions. This threshold is a critical parameter in the decision-making process. The club operates under resource constraints, such as the maximum number of players each coach can manage, ensuring that assignments are feasible and sustainable. The focus is on making precise, linear decisions that align with these operational parameters, avoiding complex relationships that could complicate the optimization process.

### Goals  
The primary goal of the riding club is to maximize the total points accumulated from player-coach assignments. This involves optimizing the allocation of players to coaches based on their compatibility scores. The success of this optimization is measured by the total points, which are directly influenced by the compatibility scores of the assigned pairs. The club aims to achieve the highest possible total points by ensuring that each assignment contributes positively to the overall score, adhering to the linear optimization framework.

## 2. Constraints    

The riding club's optimization process is governed by several key constraints. Firstly, each coach has a limit on the number of players they can effectively manage, ensuring that no coach is overburdened. This constraint ensures that the sum of assignments for each coach does not exceed their capacity. Additionally, for a player to be assigned to a coach, their compatibility score must meet or exceed the minimum threshold set by the club. This ensures that only pairings with a reasonable chance of success are considered, maintaining the quality of training and maximizing the potential for positive outcomes.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a table for assignment decision variables, updating configuration logic for scalar parameters, and ensuring all constraints are correctly implemented based on OR expert mapping analysis.

CREATE TABLE compatibility_scores (
  Player_ID INTEGER,
  Coach_ID INTEGER,
  compatibility_score FLOAT
);

CREATE TABLE coach_constraints (
  Coach_ID INTEGER,
  max_players INTEGER
);

CREATE TABLE assignments (
  Player_ID INTEGER,
  Coach_ID INTEGER,
  assignment BOOLEAN
);


```

### Data Dictionary  
The riding club's data is organized into several key tables, each serving a distinct purpose in the optimization process. The "compatibility_scores" table captures the compatibility between players and coaches, which is crucial for determining the potential success of assignments. Each entry includes a player ID, a coach ID, and a compatibility score, which acts as a coefficient in the optimization objective. The "coach_constraints" table outlines the capacity limitations for each coach, specifying the maximum number of players they can manage. This information is vital for ensuring that assignments do not exceed these limits. Lastly, the "assignments" table records the binary decision variables, indicating whether a player is assigned to a coach. This table is central to the optimization model, as it directly reflects the decisions being made.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical player-coach dynamics in a riding club, ensuring compatibility scores are realistic and coach capacities are reasonable.

-- Realistic data for compatibility_scores
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (1, 1, 0.85);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (1, 2, 0.65);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (2, 1, 0.75);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (2, 3, 0.6);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (3, 2, 0.9);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (3, 3, 0.7);

-- Realistic data for coach_constraints
INSERT INTO coach_constraints (Coach_ID, max_players) VALUES (1, 5);
INSERT INTO coach_constraints (Coach_ID, max_players) VALUES (2, 4);
INSERT INTO coach_constraints (Coach_ID, max_players) VALUES (3, 6);

-- Realistic data for assignments
INSERT INTO assignments (Player_ID, Coach_ID, assignment) VALUES (1, 1, True);
INSERT INTO assignments (Player_ID, Coach_ID, assignment) VALUES (2, 1, False);
INSERT INTO assignments (Player_ID, Coach_ID, assignment) VALUES (2, 3, True);
INSERT INTO assignments (Player_ID, Coach_ID, assignment) VALUES (3, 2, True);


```