# Complete Optimization Problem and Solution: company_employee

## 1. Problem Context and Goals

### Context  
The company is focused on optimizing the allocation of its workforce across various companies to enhance overall profitability. The primary decision involves determining the number of employees to allocate to each company. This decision is guided by the objective of maximizing total profits, which is calculated based on the profits generated by each company and the number of employees allocated to them. The company has a total of 100 employees available for allocation, which serves as a constraint in the optimization model. Additionally, each company has a maximum capacity for the number of employees it can employ, which further constrains the allocation process. The business configuration includes these operational parameters to ensure that the allocation decisions are both feasible and optimal.

### Goals  
The optimization goal is to maximize the total profits generated by the companies. This is achieved by strategically allocating employees to companies in a way that maximizes the sum of the profits generated by each company, weighted by the number of employees allocated to them. Success is measured by the increase in total profits, which is directly linked to the profits generated by each company and the number of employees allocated. The goal is articulated in a linear optimization framework, focusing on maximizing the total profits without involving complex mathematical expressions.

## 2. Constraints    

The constraints for this optimization problem are straightforward and linear. The first constraint ensures that the total number of employees allocated across all companies does not exceed the total number of employees available, which is 100. This constraint ensures that the allocation is feasible given the available resources. The second constraint specifies that the number of employees allocated to each company must not exceed the maximum number of employees that the company can employ. This ensures that the allocation respects the capacity limitations of each company. These constraints are expressed in business terms that naturally align with linear mathematical forms.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization data, modifying existing tables to improve mapping adequacy, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE company (
  Company_ID INTEGER,
  Profits_in_Billion FLOAT
);

CREATE TABLE employment (
  People_ID INTEGER,
  allocated_employees INTEGER
);

CREATE TABLE company_constraints (
  Company_ID INTEGER,
  max_employees INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and roles in the optimization process:

- **Company Table**: This table stores data specific to each company, including the profits they generate. The profits are used as coefficients in the objective function to determine the optimal allocation of employees.
  - **Company_ID**: A unique identifier for each company, serving as an index for decision variables and coefficients.
  - **Profits_in_Billion**: Represents the profits generated by each company, which are used as coefficients in the objective function.

- **Employment Table**: This table tracks the allocation of employees to companies, serving as the decision variables in the optimization model.
  - **People_ID**: A unique identifier for each employee, used as an index for decision variables.
  - **Allocated_Employees**: The number of employees allocated to each company, representing the decision variable in the optimization model.

- **Company Constraints Table**: This table stores constraints related to the maximum number of employees each company can employ.
  - **Company_ID**: A unique identifier for each company, serving as an index for constraint bounds.
  - **Max_Employees**: The maximum number of employees each company can employ, used as a constraint bound in the optimization model.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical business scenarios where companies have varying profit potentials and employee capacity constraints. The employee allocation was designed to ensure that the optimization problem is both challenging and solvable.

-- Realistic data for company
INSERT INTO company (Company_ID, Profits_in_Billion) VALUES (1, 1.5);
INSERT INTO company (Company_ID, Profits_in_Billion) VALUES (2, 2.0);
INSERT INTO company (Company_ID, Profits_in_Billion) VALUES (3, 3.0);

-- Realistic data for employment
INSERT INTO employment (People_ID, allocated_employees) VALUES (101, 30);
INSERT INTO employment (People_ID, allocated_employees) VALUES (102, 20);
INSERT INTO employment (People_ID, allocated_employees) VALUES (103, 10);
INSERT INTO employment (People_ID, allocated_employees) VALUES (104, 25);
INSERT INTO employment (People_ID, allocated_employees) VALUES (105, 15);

-- Realistic data for company_constraints
INSERT INTO company_constraints (Company_ID, max_employees) VALUES (1, 50);
INSERT INTO company_constraints (Company_ID, max_employees) VALUES (2, 60);
INSERT INTO company_constraints (Company_ID, max_employees) VALUES (3, 70);
```