# Complete Optimization Problem and Solution: shop_membership

## 1. Problem Context and Goals

### Context  
A retail chain is focused on optimizing the allocation of its membership marketing budget across various branches. The primary decision involves determining the budget allocation for each branch, represented by the continuous variable BudgetAllocation.amount[branch_id]. The goal is to maximize the total purchases made by members, which is directly influenced by the historical purchase data of each branch. The operational parameters include a total marketing budget that serves as a constraint, ensuring that the sum of all branch allocations does not exceed this budget. Additionally, each branch must receive a minimum budget allocation to ensure effective marketing efforts. This setup naturally aligns with a linear optimization formulation, where the objective is to maximize the total purchases by strategically distributing the available budget. The business configuration includes a total marketing budget of 45,000 and a minimum budget allocation of 10,000 for each branch, ensuring that all branches have sufficient resources to drive member purchases.

### Goals  
The primary goal of this optimization problem is to maximize the total purchases made by members across all branches. This is achieved by optimally allocating the fixed marketing budget based on historical purchase data and membership levels. The success of this optimization is measured by the increase in total purchases, which is directly linked to the budget allocation for each branch. The linear optimization goal is to maximize the total purchases by effectively utilizing the available budget, ensuring that each branch receives at least the minimum required allocation.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the feasibility and effectiveness of the budget allocation:

- The total budget allocated across all branches must not exceed the total marketing budget of 45,000. This constraint ensures that the allocation remains within the available financial resources.
- Each branch must receive a minimum budget allocation of 10,000. This constraint guarantees that every branch has enough funding to implement meaningful marketing strategies and drive member purchases.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, ensuring that the optimization problem remains linear and solvable using standard linear programming techniques.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization data and updating configuration logic for scalar parameters and formulas.

CREATE TABLE BudgetAllocation (
  branch_id INTEGER,
  amount FLOAT
);

CREATE TABLE Purchase (
  branch_id INTEGER,
  Total_pounds FLOAT
);
```

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

- **BudgetAllocation Table**: This table stores the budget allocation for each branch. It plays a critical role in the optimization process as it represents the decision variables.
  - **branch_id**: An integer identifier for each branch, used as an index for budget allocation.
  - **amount**: A float representing the allocated budget amount for the branch, serving as the decision variable for budget allocation.

- **Purchase Table**: This table contains historical purchase data, which is used to determine the effectiveness of budget allocations.
  - **branch_id**: An integer identifier for each branch, used as an index for purchase coefficients.
  - **Total_pounds**: A float representing the total historical purchases in pounds for each branch, serving as the coefficient for budget allocation 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 marketing budgets and purchase data for retail chains, ensuring a balance between budget allocation and historical purchase performance.

-- Realistic data for BudgetAllocation
INSERT INTO BudgetAllocation (branch_id, amount) VALUES (1, 12000);
INSERT INTO BudgetAllocation (branch_id, amount) VALUES (2, 18000);
INSERT INTO BudgetAllocation (branch_id, amount) VALUES (3, 15000);

-- Realistic data for Purchase
INSERT INTO Purchase (branch_id, Total_pounds) VALUES (1, 60000);
INSERT INTO Purchase (branch_id, Total_pounds) VALUES (2, 90000);
INSERT INTO Purchase (branch_id, Total_pounds) VALUES (3, 80000);
```