Resource Scheduling - DeciDB 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.

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) <= 3 PER resource_id AND
    SUM(x) = 1 PER slot_id
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) <= 3 PER resource_id — Each resource is assigned to at most 3 slots. PER takes unqualified column names; the join key is unambiguous because resource_id only exists on the Resources side.

4

SUM(x) = 1 PER slot_id — 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	2	10:00	Monday	0.95	8	1
Alice	3	11:00	Monday	0.95	9	1
Bob	1	09:00	Monday	0.88	10	0
Bob	2	10:00	Monday	0.88	8	0
Bob	3	11:00	Monday	0.88	9	0
Carol	4	13:00	Tuesday	0.92	10	1

INTERPRETATION

Alice (efficiency 0.95) takes all three Monday slots — she has higher efficiency than Bob (0.88), so every Monday slot is more valuable when assigned to her. The 3-slots-per-resource cap is exactly binding for Alice; Bob is left unassigned. Carol (0.92) covers the only Tuesday slot. Total score is 0.95×(10 + 8 + 9) + 0.92×10 = 25.65 + 9.2 = 34.85. The JOIN and WHERE clauses do the feasibility filtering up front: Carol can't be considered for Monday slots (wrong day), and any candidate with insufficient skill level is dropped before the optimizer ever sees them. To force a more balanced assignment (e.g., give Bob a shot), you would tighten the per-resource cap from 3 down to 2.