Production Planning - PackDB Examples

THE PROBLEM

A factory produces three products. Each has a per-unit production cost, a minimum order quantity (contractual obligation), and a maximum production capacity. The goal is to find how many units of each product to manufacture while meeting minimum orders, not exceeding capacity, and keeping total production cost within a $50,000 budget. Unlike the knapsack problem, here the decision variable x is an integer (not binary) — it represents a quantity, not a yes/no choice.

SAMPLE DATA

CREATE TABLE Products (
    product_id INTEGER, name VARCHAR, cost_per_unit INTEGER,
    min_quantity INTEGER, max_capacity INTEGER
);
INSERT INTO Products VALUES
    (1, 'Widget A', 10, 100, 500),
    (2, 'Widget B', 15, 50, 300),
    (3, 'Widget C', 20, 75, 200);

THE QUERY

SELECT product_id, name, cost_per_unit, min_quantity, max_capacity, x as quantity_to_produce
FROM Products
DECIDE x IS INTEGER
SUCH THAT
    x >= min_quantity AND
    x <= max_capacity AND
    SUM(x * cost_per_unit) <= 50000
MINIMIZE SUM(x * cost_per_unit);

QUERY BREAKDOWN

1

DECIDE x IS INTEGER — x is a non-negative integer for each row, representing how many units to produce.

2

x >= min_quantity — Row-level constraint: each product must meet its minimum order.

3

x <= max_capacity — Row-level constraint: production cannot exceed factory capacity.

4

SUM(x * cost_per_unit) <= 50000 — Aggregate constraint: total cost stays within budget.

5

MINIMIZE SUM(x * cost_per_unit) — Find the cheapest production plan that satisfies all constraints.

RESULT

product_id	name	cost_per_unit	min_quantity	max_capacity	quantity_to_produce
1	Widget A	10	100	500	100
2	Widget B	15	50	300	50
3	Widget C	20	75	200	75

INTERPRETATION

The solver produces exactly the minimum required quantity for each product: 100 of Widget A, 50 of Widget B, and 75 of Widget C. Total cost: (100×10) + (50×15) + (75×20) = $3,250 — well under the $50,000 budget. Since we are minimizing cost, producing more than the minimum would only increase the total, so the optimal solution is to meet the minimums and stop.