Resource Scheduling - PackDB Examples

THE PROBLEM

A facility manager assigns staff to time slots across multiple days. Resources (staff) have efficiency ratings and available days; slots have start times, days, and priority levels. A resource can only be assigned to a slot on a day they're available, and only if their skill level meets the slot's requirement. Each resource handles at most 3 slots, and each slot needs exactly 1 resource. The objective maximizes the combined efficiency × priority score across all assignments.

Note: PER (grouped constraints) is a planned feature — coming soon.

SAMPLE DATA

CREATE TABLE Resources (
    resource_id INTEGER, name VARCHAR, efficiency DOUBLE,
    skill_level INTEGER, available_day VARCHAR
);
INSERT INTO Resources VALUES
    (101, 'Alice', 0.95, 5, 'Monday'),
    (102, 'Bob', 0.88, 4, 'Monday'),
    (103, 'Carol', 0.92, 5, 'Tuesday');

CREATE TABLE Slots (
    slot_id INTEGER, start_time VARCHAR, day VARCHAR,
    priority INTEGER, required_level INTEGER
);
INSERT INTO Slots VALUES
    (1, '09:00', 'Monday', 10, 3),
    (2, '10:00', 'Monday', 8, 3),
    (3, '11:00', 'Monday', 9, 4),
    (4, '13:00', 'Tuesday', 10, 4);

THE QUERY

SELECT r.resource_id, r.name as resource_name, s.slot_id,
       s.start_time, s.day, r.efficiency, s.priority, x as assigned
FROM Resources r
JOIN Slots s ON r.available_day = s.day
WHERE s.start_time BETWEEN '09:00' AND '17:00'
  AND r.skill_level >= s.required_level
DECIDE x IS BOOLEAN
SUCH THAT
    SUM(x) PER r.resource_id <= 3 AND
    SUM(x) PER s.slot_id = 1
MAXIMIZE SUM(x * r.efficiency * s.priority);

QUERY BREAKDOWN

1

JOIN ... ON r.available_day = s.day — Only consider resource-slot pairs where the resource is available on that day.

2

WHERE r.skill_level >= s.required_level — Further filter: resource must meet the slot's skill requirement.

3

SUM(x) PER r.resource_id <= 3 — Each resource is assigned to at most 3 slots.

4

SUM(x) PER s.slot_id = 1 — Each slot is filled by exactly one resource.

5

MAXIMIZE SUM(x * r.efficiency * s.priority) — Maximize combined efficiency × priority.

RESULT

resource	slot	time	day	efficiency	priority	assigned
Alice	1	09:00	Monday	0.95	10	1
Alice	3	11:00	Monday	0.95	9	1
Bob	2	10:00	Monday	0.88	8	1
Carol	4	13:00	Tuesday	0.92	10	1

INTERPRETATION

Alice (efficiency 0.95) gets the two highest-priority Monday slots (10 and 9), Bob (0.88) fills the remaining Monday slot (priority 8), and Carol (0.92) covers the Tuesday slot (priority 10). The total score is (0.95×10) + (0.95×9) + (0.88×8) + (0.92×10) = 9.5 + 8.55 + 7.04 + 9.2 = 34.29. The JOIN and WHERE clauses ensure feasibility before optimization begins — Carol can't be assigned to Monday slots, and no one with insufficient skill level is considered.