# Complete Optimization Problem and Solution: customers_card_transactions

## 1. Problem Context and Goals

### Context  
A bank is focused on reducing the total transaction fees incurred by its customers by strategically allocating transactions across different card types. Each card type has a distinct fee structure, and the bank aims to optimize these allocations to minimize costs. The bank must ensure that the total transaction amount per customer does not exceed their predefined account limits. This operational decision-making process involves determining the appropriate transaction amounts for each card type while adhering to customer-specific constraints.  

The business configuration includes key parameters such as the fee associated with each card type, which directly influences the total transaction fee calculation. Additionally, the maximum transaction amount allowed per customer serves as a critical constraint to ensure compliance with account limits. The total transaction fee is calculated by summing the product of the fee for each card type and the corresponding transaction amount allocated to that card. This approach ensures a linear relationship between the decision variables and the objective, avoiding any nonlinear complexities such as variable products or divisions.  

### Goals  
The primary goal of this optimization problem is to minimize the total transaction fees incurred by the bank’s customers. This is achieved by optimizing the allocation of transaction amounts across different card types, each with its own fee structure. Success is measured by the reduction in the total fee, which is directly influenced by the fees associated with each card type and the transaction amounts allocated to them. The optimization process ensures that the total transaction amount per customer remains within their account limits, maintaining operational feasibility while achieving cost efficiency.  

## 2. Constraints  

The optimization problem is subject to one key constraint: the total transaction amount allocated to all card types for a given customer must not exceed their account limit. This ensures that the bank’s operational decisions remain within the bounds of each customer’s financial capacity. The constraint is expressed as the sum of transaction amounts across all card types for a customer being less than or equal to their account limit. This linear constraint aligns with the business requirement of maintaining customer-specific transaction limits while optimizing fee costs.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added Transaction_Allocations table to map missing decision variables, updated business configuration logic to include scalar parameters and formulas, and ensured schema follows normalization principles.

CREATE TABLE Card_Fees (
  card_type_code STRING,
  fee FLOAT
);

CREATE TABLE Account_Limits (
  customer_id INTEGER,
  account_limit FLOAT
);

CREATE TABLE Transaction_Allocations (
  customer_id INTEGER,
  card_id STRING,
  transaction_amount FLOAT
);
```

### Data Dictionary  
- **Card_Fees**:  
  - **Business Purpose**: Stores the fee associated with each card type.  
  - **Optimization Role**: Provides the coefficients for the objective function.  
  - **Columns**:  
    - `card_type_code`: Represents the type of card (e.g., VISA, MASTERCARD).  
    - `fee`: The fee associated with the card type, used to calculate the total transaction fee.  

- **Account_Limits**:  
  - **Business Purpose**: Defines the maximum transaction amount allowed per customer.  
  - **Optimization Role**: Provides the bounds for the constraints.  
  - **Columns**:  
    - `customer_id`: Unique identifier for the customer.  
    - `account_limit`: The maximum transaction amount allowed for the customer.  

- **Transaction_Allocations**:  
  - **Business Purpose**: Tracks the amount of transaction allocated to each card type per customer.  
  - **Optimization Role**: Represents the decision variables in the optimization model.  
  - **Columns**:  
    - `customer_id`: Unique identifier for the customer.  
    - `card_id`: Unique identifier for the card.  
    - `transaction_amount`: The amount of transaction allocated to the card.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical banking transaction fees, account limits, and transaction amounts, ensuring they align with real-world scenarios and enable a meaningful optimization problem.

-- Realistic data for Card_Fees
INSERT INTO Card_Fees (card_type_code, fee) VALUES ('VISA', 0.02);
INSERT INTO Card_Fees (card_type_code, fee) VALUES ('MASTERCARD', 0.03);
INSERT INTO Card_Fees (card_type_code, fee) VALUES ('AMEX', 0.035);

-- Realistic data for Account_Limits
INSERT INTO Account_Limits (customer_id, account_limit) VALUES (1, 1000);
INSERT INTO Account_Limits (customer_id, account_limit) VALUES (2, 1500);
INSERT INTO Account_Limits (customer_id, account_limit) VALUES (3, 2000);

-- Realistic data for Transaction_Allocations
INSERT INTO Transaction_Allocations (customer_id, card_id, transaction_amount) VALUES (1, 'CARD1', 100);
INSERT INTO Transaction_Allocations (customer_id, card_id, transaction_amount) VALUES (2, 'CARD2', 200);
INSERT INTO Transaction_Allocations (customer_id, card_id, transaction_amount) VALUES (3, 'CARD3', 300);
```