Table-Scoped Variables - DeciDB Examples

THE PROBLEM

A hospital is restructuring its nursing staff and needs to decide which nurses to retain. Each nurse is scheduled for multiple shifts, and joining nurses with shifts produces one result row per nurse–shift combination. The decision, however, is per nurse — you either keep a nurse for all their shifts or release them entirely. Without table-scoped variables, DeciDB would create one independent binary variable per result row, allowing inconsistent decisions like "retain Alice for Monday but not Tuesday." The n. prefix in DECIDE n.keepN IS BOOLEAN tells DeciDB to create exactly one variable per unique nurse, shared across all rows that originate from that nurse.

SAMPLE DATA

CREATE TABLE nurses (
    id INTEGER, name VARCHAR, hourly_rate REAL
);
CREATE TABLE shifts (
    shift_id INTEGER, nurse_id INTEGER,
    shift_date VARCHAR, hours INTEGER
);

INSERT INTO nurses VALUES
    (1, 'Alice', 45.0),
    (2, 'Bob',   38.0),
    (3, 'Carol', 52.0),
    (4, 'Dan',   41.0);

INSERT INTO shifts VALUES
    (101, 1, '2026-06-01',  8),
    (102, 1, '2026-06-02', 10),
    (103, 2, '2026-06-01',  8),
    (104, 2, '2026-06-03',  6),
    (105, 3, '2026-06-02',  8),
    (106, 3, '2026-06-03', 12),
    (107, 4, '2026-06-01',  4),
    (108, 4, '2026-06-02',  8);

THE QUERY

SELECT n.name, s.shift_date, s.hours, keepN AS retained
FROM nurses n
JOIN shifts s ON n.id = s.nurse_id
DECIDE n.keepN IS BOOLEAN
SUCH THAT
    SUM(keepN * s.hours * n.hourly_rate) <= 1200
MAXIMIZE SUM(keepN * s.hours);

QUERY BREAKDOWN

1

DECIDE n.keepN IS BOOLEAN — The n. prefix makes this a table-scoped variable. DeciDB creates one binary variable per unique nurse in the nurses table (4 variables total), not one per result row (8 rows). All join rows originating from the same nurse share the same keepN value.

2

SUM(keepN * s.hours * n.hourly_rate) <= 1200 — Total payroll budget. Summing over all 8 rows, each retained nurse contributes their total hours × rate across all their shifts. Alice: 18h × $45 = $810; Bob: 14h × $38 = $532; Carol: 20h × $52 = $1,040; Dan: 12h × $41 = $492.

3

MAXIMIZE SUM(keepN * s.hours) — Maximize total staffed hours across all retained nurses and their shifts. More hours means better patient coverage.

RESULT

name	shift_date	hours	retained
Alice	2026-06-01	8	0
Alice	2026-06-02	10	0
Bob	2026-06-01	8	1
Bob	2026-06-03	6	1
Carol	2026-06-02	8	0
Carol	2026-06-03	12	0
Dan	2026-06-01	4	1
Dan	2026-06-02	8	1

INTERPRETATION

The solver retains Bob (14h, $532) and Dan (12h, $492) for a combined payroll of $1,024 — within the $1,200 budget — covering 26 total hours. Alice ($810) and Carol ($1,040) alone exhaust most of the budget, leaving no room to add a second nurse; neither combination of Alice or Carol with another nurse fits. Notice that Bob appears in two rows and has retained=1 on both, and Dan likewise. This is table-scoped semantics in action: one keepN variable is shared across all shift rows for a given nurse, guaranteeing a consistent retain/release decision. Without the n. prefix, the solver would create 8 independent variables and could freely assign keepN=1 to Bob’s Monday shift and keepN=0 to his Wednesday shift — a meaningless half-retain that this formulation prevents.