# Complete Optimization Problem and Solution: party_host

## 1. Problem Context and Goals

### Context  
A party hosting company is tasked with efficiently assigning hosts to parties while minimizing the total cost of hosting. Each host has an associated cost, and the company must ensure that each party has a sufficient number of hosts, does not exceed the maximum allowed hosts, and that the hosts assigned have expertise that matches the party theme. The decision to assign a host to a party is binary, meaning a host is either assigned or not assigned to a specific party.  

The company operates with the following operational parameters:  
- **Cost per host**: Each host has a specific cost associated with their services, which directly impacts the total cost of hosting parties.  
- **Minimum hosts per party**: Each party requires a minimum number of hosts to ensure adequate service.  
- **Maximum hosts per party**: Each party has a limit on the number of hosts that can be assigned to avoid overstaffing.  
- **Expertise matching**: A host can only be assigned to a party if their expertise aligns with the party’s theme.  

These parameters are critical to the decision-making process and are used to define the objective and constraints of the optimization problem. The goal is to make these assignments in a way that minimizes total costs while adhering to the operational requirements.  

### Goals  
The primary goal of this optimization problem is to minimize the total cost of hosting parties by assigning hosts to parties in the most cost-effective manner. This is achieved by considering the cost associated with each host and ensuring that the assignments respect the minimum and maximum host requirements for each party, as well as the expertise matching criteria.  

Success is measured by the ability to reduce the total cost of hosting while maintaining operational feasibility. The optimization process ensures that the company’s resources are used efficiently, balancing cost savings with the need to meet party hosting requirements.  

## 2. Constraints  

The optimization problem must adhere to the following constraints:  
1. **Minimum hosts per party**: Each party must have at least the minimum number of hosts required to ensure adequate service. This ensures that parties are not understaffed.  
2. **Maximum hosts per party**: Each party cannot exceed the maximum number of hosts allowed. This prevents overstaffing and unnecessary costs.  
3. **Expertise matching**: A host can only be assigned to a party if their expertise matches the party’s theme. This ensures that the hosts assigned are qualified to meet the party’s needs.  

These constraints ensure that the assignments are operationally feasible and align with the company’s business requirements.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added decision variable table 'assign' to address missing optimization requirement. Updated business configuration logic to include scalar parameters and formulas. Ensured schema follows normalization principles and industry standards.

CREATE TABLE cost_per_host (
  Host_ID INTEGER,
  cost INTEGER
);

CREATE TABLE min_hosts (
  Party_ID INTEGER,
  min_hosts INTEGER
);

CREATE TABLE max_hosts (
  Party_ID INTEGER,
  max_hosts INTEGER
);

CREATE TABLE expertise_match (
  Party_ID INTEGER,
  Host_ID INTEGER,
  match BOOLEAN
);

CREATE TABLE assign (
  Party_ID INTEGER,
  Host_ID INTEGER,
  assign BOOLEAN
);
```

### Data Dictionary  
The following tables and columns are used in the optimization problem:  
- **cost_per_host**: Contains the cost associated with each host. This data is used to calculate the total cost of hosting parties.  
  - *Host_ID*: Unique identifier for each host.  
  - *cost*: The cost of the host, used as a coefficient in the objective function.  
- **min_hosts**: Specifies the minimum number of hosts required for each party. This data is used to set the lower bound for the number of hosts assigned to a party.  
  - *Party_ID*: Unique identifier for each party.  
  - *min_hosts*: The minimum number of hosts required for the party, used as a constraint bound.  
- **max_hosts**: Specifies the maximum number of hosts allowed for each party. This data is used to set the upper bound for the number of hosts assigned to a party.  
  - *Party_ID*: Unique identifier for each party.  
  - *max_hosts*: The maximum number of hosts allowed for the party, used as a constraint bound.  
- **expertise_match**: Indicates whether a host’s expertise matches a party’s theme. This data is used to ensure that only qualified hosts are assigned to parties.  
  - *Party_ID*: Unique identifier for each party.  
  - *Host_ID*: Unique identifier for each host.  
  - *match*: A binary indicator (true/false) that determines if the host’s expertise matches the party’s theme, used as a constraint bound.  
- **assign**: Represents the decision to assign a host to a party. This table is used to store the optimization results.  
  - *Party_ID*: Unique identifier for each party.  
  - *Host_ID*: Unique identifier for each host.  
  - *assign*: A binary decision variable (true/false) indicating whether the host is assigned to the party.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical party hosting scenarios, ensuring realistic costs, host availability, and expertise matching. The number of hosts per party was set to reflect common party sizes, and costs were varied to represent different host experience levels.

-- Realistic data for cost_per_host
INSERT INTO cost_per_host (Host_ID, cost) VALUES (1, 50);
INSERT INTO cost_per_host (Host_ID, cost) VALUES (2, 70);
INSERT INTO cost_per_host (Host_ID, cost) VALUES (3, 60);

-- Realistic data for min_hosts
INSERT INTO min_hosts (Party_ID, min_hosts) VALUES (1, 2);
INSERT INTO min_hosts (Party_ID, min_hosts) VALUES (2, 3);
INSERT INTO min_hosts (Party_ID, min_hosts) VALUES (3, 4);

-- Realistic data for max_hosts
INSERT INTO max_hosts (Party_ID, max_hosts) VALUES (1, 4);
INSERT INTO max_hosts (Party_ID, max_hosts) VALUES (2, 5);
INSERT INTO max_hosts (Party_ID, max_hosts) VALUES (3, 6);

-- Realistic data for expertise_match
INSERT INTO expertise_match (Party_ID, Host_ID, match) VALUES (1, 1, True);
INSERT INTO expertise_match (Party_ID, Host_ID, match) VALUES (1, 2, False);
INSERT INTO expertise_match (Party_ID, Host_ID, match) VALUES (2, 2, True);

-- Realistic data for assign
INSERT INTO assign (Party_ID, Host_ID, assign) VALUES (1, 1, True);
INSERT INTO assign (Party_ID, Host_ID, assign) VALUES (1, 2, False);
INSERT INTO assign (Party_ID, Host_ID, assign) VALUES (2, 2, True);
```