Instructor scheduling data model: matching instructors by availability and qualification
Instructor scheduling comes down to three separate questions: is this person free for every meeting, are they allowed to teach this course on those dates, and which of the people who pass is the best choice? Store each answer in its own tables, treat the first two as hard filters, and score only the survivors.
This guide gives a PostgreSQL data model for each question, plus a candidate query and a scoring query. Both were run on PostgreSQL 16.15 against a small invented dataset, and the results shown are the real output. The wider TMS schema and general constraint scheduling are covered in separate guides.
Key takeaways
- Store availability as weekly rules in the instructor’s local time, plus dated exceptions. Keep confirmed bookings and venue-to-venue travel times in their own tables.
- Separate credential types from the credentials instructors hold, and test each credential against the last day of the session, not the first.
- Apply availability and qualification as hard filters, then rank the survivors with a weighted score whose components stay visible.
- Run the two filters separately when debugging, so you can tell a planner why someone is missing from the list.
What does instructor scheduling have to decide?
Instructor scheduling has to settle three things for every session: whether the instructor is free for each meeting, whether they hold every credential the course demands for its whole run, and whether they are the best option among those who pass. The first two are hard filters. The third is a score.
| Question | What you store | Rule type |
|---|---|---|
| Free for every meeting? | Weekly rules, exceptions, existing bookings, travel times | Hard filter |
| Allowed to teach it? | Credential types, issue and expiry dates, prerequisites, course requirements | Hard filter |
| Best choice? | Delivery history, workload, preferences, credential runway | Soft score |
Keep the split strict. If a legal rule becomes one weight among many, someone will eventually be assigned with a lapsed credential.
How should you store instructor availability?
Store availability as weekly rules in the instructor’s local time, plus dated exceptions for leave and one-off extra days. Keep confirmed bookings in a separate table. At query time, expand the rules into windows, remove leave, and test each meeting against what is left.
Why store weekly rules in local time instead of UTC slots?
A rule such as “Tuesdays 09:00 to 17:00 in Europe/London” stays true when clocks change. A stored UTC slot does not. In the test database, a London 09:00 start is 08:00 UTC on October 19, 2026 and 09:00 UTC on October 26, 2026.
The iCalendar standard, RFC 5545, also settles two edge cases: a local time inside a daylight-saving gap uses the offset from before the gap, and a repeated local hour refers to its first occurrence. Check what your own database does with early-morning windows on clock-change days before you rely on it.
Should you store availability or busy time?
Store both. Some designs record only busy time and treat everyone as free otherwise. A 2015 driving-school data model on Redgate’s site does this: an instructor is available if no occupancy record exists, and working hours are assumed to be 8:00 AM to 6:00 PM on business days. That suits one school with one pattern. It has no place for an instructor who works only Tuesday to Thursday.
Rules and exceptions say when someone may teach. Bookings say when they are taken. Travel buffers belong in a venue-pair table, because Manchester to London is not London to London. A virtual meeting has no venue, so its buffer is zero.
CREATE TABLE availability_rule ( -- weekly pattern in the instructor's own clock
instructor_id int NOT NULL REFERENCES instructor,
weekday int NOT NULL CHECK (weekday BETWEEN 1 AND 7), -- ISO: 1 = Monday
start_local time NOT NULL,
end_local time NOT NULL CHECK (end_local > start_local),
tz text NOT NULL, -- IANA name, e.g. Europe/London
valid_from date NOT NULL,
valid_to date -- exclusive; NULL = open-ended
);
CREATE TABLE availability_exception ( -- leave, holidays, one-off extra days
instructor_id int NOT NULL REFERENCES instructor,
during tstzrange NOT NULL,
kind text NOT NULL CHECK (kind IN ('unavailable', 'extra'))
);
CREATE TABLE travel_time (
from_venue int NOT NULL REFERENCES venue,
to_venue int NOT NULL REFERENCES venue,
minutes int NOT NULL,
PRIMARY KEY (from_venue, to_venue)
);
CREATE TABLE credential_prerequisite (
credential_type_id int NOT NULL REFERENCES credential_type,
requires_type_id int NOT NULL REFERENCES credential_type
);
CREATE TABLE instructor_credential (
instructor_id int NOT NULL REFERENCES instructor,
credential_type_id int NOT NULL REFERENCES credential_type,
issued_on date NOT NULL,
expires_on date, -- first day NOT valid; NULL = no expiry
status text NOT NULL DEFAULT 'active' -- active, suspended or revoked
);
CREATE TABLE course_requirement ( -- AND across group_no, OR within a group
course_id int NOT NULL REFERENCES course,
group_no int NOT NULL,
credential_type_id int NOT NULL REFERENCES credential_type
);
The queries also assume tables for instructors, courses, sessions (with a time zone), meetings (a time range and a venue) and bookings. The booking table carries an exclusion constraint on instructor and time range, so two planners cannot double-book one person.
How do you model qualifications, certifications and prerequisites?
Separate the kind of credential from the instance an instructor holds. A credential type names what it is. An instructor credential records who holds it, with issue and expiry dates and a status. A course requirement lists which types a course demands. Prerequisites link one type to another.
Why check expiry against the last meeting, not the first?
A credential that is valid on day one but lapses before day two should not pass. Test it against the last day of the session, and treat expires_on as the first day it is no longer valid. That matches the half-open convention used for time ranges: a credential expiring on November 18 covers a November 17 meeting but not a November 18 one. Also test issued_on against the first day.
How do you express “A or B” requirements and prerequisites?
Give each course requirement a group number. Every group must be satisfied, and any one credential type inside a group will do. In the sample data, course 10 needs Advanced Facilitator or Master Facilitator, and also Safety Trainer.
A prerequisite is a second rule: a credential counts only if the instructor also holds the types it requires. The query below checks one level. For deeper chains, list every transitive prerequisite in the table.
Pro tip: Renew by inserting, not overwriting. When a credential is renewed, add a new row with new dates and leave the old one in place. The old row explains past assignments, and a renewal recorded late shows up as a gap in coverage instead of silently erasing history.
What does the instructor scheduling query look like?
The query works in two passes. Pass one builds each instructor’s free time as a multirange and tests every meeting against it, with travel padding around existing bookings. Pass two keeps instructors whose credentials cover the whole session. The three query blocks below are one statement, and :session_id is a psql variable you would replace with a bind parameter.
The invented session is an Advanced Facilitation Workshop at London HQ, 09:00 to 17:00 on November 17 and 18, 2026. The eight instructors are built to fail in different ways.
| Instructor | Situation in the test data |
|---|---|
| Asha | Free all week and qualified, but has a 07:00 to 08:30 briefing in Manchester on November 17 |
| Ben | Free and qualified; has taught this course four times in the past year |
| Chen | Qualified, but on annual leave on November 18 |
| Dana | Free, but her Advanced Facilitator credential expires on November 18 |
| Farid | Free; holds Advanced Facilitator, but its Facilitator prerequisite lapsed on September 30 |
| Gita | Free and qualified through Master Facilitator; already booked nine weekdays this month |
| Hugo | Qualified, but works only Tuesday to Thursday, 10:00 to 16:00 |
| Ines | Free and qualified; Safety Trainer expires December 18, 2026; has taught the course once |
How do you test availability against rules, leave and bookings?
Expand each weekly rule into concrete windows, add extra days, subtract leave, and require every meeting to sit inside what remains. PostgreSQL’s multirange type makes this short. range_agg unions the windows, the - operator removes leave, and @> tests containment, as the PostgreSQL range function documentation describes.
WITH win AS ( -- session span, local dates
SELECT s.session_id, s.course_id,
min(lower(m.during)) AS starts_at,
max(upper(m.during)) AS ends_at,
(min(lower(m.during)) AT TIME ZONE s.tz)::date AS first_day,
((max(upper(m.during)) - interval '1 second') AT TIME ZONE s.tz)::date AS last_day
FROM session s JOIN meeting m USING (session_id)
WHERE s.session_id = :session_id
GROUP BY s.session_id, s.course_id, s.tz
),
weekly AS ( -- weekly rules to concrete windows
SELECT r.instructor_id,
tstzrange((g.d + r.start_local) AT TIME ZONE r.tz,
(g.d + r.end_local) AT TIME ZONE r.tz) AS w
FROM availability_rule r
CROSS JOIN win
CROSS JOIN LATERAL generate_series(win.first_day - 1, win.last_day + 1, interval '1 day') AS g(d)
WHERE extract(isodow FROM g.d) = r.weekday
AND g.d::date >= r.valid_from
AND g.d::date < COALESCE(r.valid_to, 'infinity'::date)
),
free AS ( -- rules + extra days - leave
SELECT i.instructor_id,
COALESCE((SELECT range_agg(x.w) FROM (
SELECT w FROM weekly k WHERE k.instructor_id = i.instructor_id
UNION ALL
SELECT during FROM availability_exception e
WHERE e.instructor_id = i.instructor_id AND e.kind = 'extra') x),
'{}'::tstzmultirange)
- COALESCE((SELECT range_agg(e.during) FROM availability_exception e
WHERE e.instructor_id = i.instructor_id AND e.kind = 'unavailable'),
'{}'::tstzmultirange) AS free_time
FROM instructor i WHERE i.active
),
available AS ( -- every meeting fits, with travel padding
SELECT f.instructor_id
FROM free f
WHERE NOT EXISTS (SELECT 1 FROM meeting m
WHERE m.session_id = :session_id AND NOT (f.free_time @> m.during))
AND NOT EXISTS (SELECT 1
FROM meeting m
JOIN booking b ON b.instructor_id = f.instructor_id
AND b.session_id <> m.session_id
LEFT JOIN travel_time t ON t.from_venue = b.venue_id AND t.to_venue = m.venue_id
WHERE m.session_id = :session_id
AND tstzrange(lower(m.during) - COALESCE(t.minutes, 0) * interval '1 minute',
upper(m.during) + COALESCE(t.minutes, 0) * interval '1 minute') && b.busy)
),
Adjacent windows such as 09:00 to 12:00 and 12:00 to 17:00 merge into one range, so a meeting across a split shift still fits. The travel padding widens each meeting by the venue-pair travel time before testing it against other bookings. Without it, Asha would pass. It is slightly conservative, because it pads both sides.
How do you test qualification, expiry and prerequisites?
First keep credentials valid for the whole session, then keep those whose prerequisites are also held, then require that every requirement group has at least one survivor.
held AS ( -- valid for the whole session
SELECT ic.*
FROM instructor_credential ic, win
WHERE ic.status = 'active'
AND ic.issued_on <= win.first_day
AND (ic.expires_on IS NULL OR ic.expires_on > win.last_day)
),
usable AS ( -- prerequisites also held
SELECT h.*
FROM held h
WHERE NOT EXISTS (SELECT 1 FROM credential_prerequisite p
WHERE p.credential_type_id = h.credential_type_id
AND NOT EXISTS (SELECT 1 FROM held p2
WHERE p2.instructor_id = h.instructor_id
AND p2.credential_type_id = p.requires_type_id))
),
qualified AS ( -- every group satisfied
SELECT i.instructor_id
FROM instructor i, win
WHERE NOT EXISTS (
SELECT 1
FROM (SELECT DISTINCT group_no FROM course_requirement WHERE course_id = win.course_id) g
WHERE NOT EXISTS (SELECT 1
FROM course_requirement cr
JOIN usable u ON u.credential_type_id = cr.credential_type_id
WHERE cr.course_id = win.course_id
AND cr.group_no = g.group_no
AND u.instructor_id = i.instructor_id))
),
Running the two filters separately gave the explanation for each instructor.
| Instructor | Free for both meetings? | Credentials valid for both days? | Candidate? |
|---|---|---|---|
| Asha | No, travel from Manchester | Yes | No |
| Ben | Yes | Yes | Yes |
| Chen | No, leave on November 18 | Yes | No |
| Dana | Yes | No, expires November 18 | No |
| Farid | Yes | No, prerequisite lapsed | No |
| Gita | Yes | Yes | Yes |
| Hugo | No, window is 10:00 to 16:00 | Yes | No |
| Ines | Yes | Yes | Yes |
How do you rank the instructors who pass?
Scale each signal to a number between 0 and 1, multiply by a weight, and sort by the sum. Keep every component in the output so a planner can see why one instructor outranks another. Break ties on a stable column such as the instructor ID.
| Signal | How it is scaled | Weight |
|---|---|---|
| Experience | Deliveries of this course in the past 12 months, capped at 4, divided by 4 | 0.35 |
| Spare capacity | 1 minus booked hours in the session’s month, capped at 80, divided by 80 | 0.30 |
| Preference | Stored value from 0 to 1; 0.5 when none is recorded | 0.25 |
| Credential runway | Days from the session’s last day to the earliest expiry of a needed credential, capped at 90, divided by 90; 1 when nothing expires | 0.10 |
The caps and weights are placeholders, not research findings. Tune them against decisions your planners actually make. The block below finishes the query: it joins the two filters, then adds the scoring.
candidates AS ( -- hard filters only
SELECT instructor_id FROM available
INTERSECT
SELECT instructor_id FROM qualified
),
signals AS ( -- raw facts
SELECT c.instructor_id,
(SELECT count(*) FROM delivery_history h, win
WHERE h.instructor_id = c.instructor_id AND h.course_id = win.course_id
AND h.delivered_on >= win.first_day - 365) AS deliveries_12m,
(SELECT COALESCE(sum(extract(epoch FROM upper(b.busy) - lower(b.busy))) / 3600, 0)
FROM booking b, win
WHERE b.instructor_id = c.instructor_id
AND b.busy && tstzrange(date_trunc('month', win.starts_at),
date_trunc('month', win.starts_at) + interval '1 month')) AS booked_hours,
(SELECT COALESCE(max(p.preference), 0.5) FROM instructor_preference p, win
WHERE p.instructor_id = c.instructor_id AND p.course_id = win.course_id) AS preference,
(SELECT min(u.expires_on)
FROM usable u
JOIN course_requirement cr ON cr.credential_type_id = u.credential_type_id
WHERE u.instructor_id = c.instructor_id
AND cr.course_id = (SELECT course_id FROM win))
- (SELECT last_day FROM win) AS days_to_expiry
FROM candidates c
),
scored AS ( -- each scaled 0..1
SELECT s.*,
least(deliveries_12m, 4) / 4.0 AS experience,
1 - least(booked_hours, 80) / 80.0 AS spare_capacity,
least(COALESCE(days_to_expiry, 90), 90) / 90.0 AS credential_runway
FROM signals s
)
SELECT i.name,
round(experience::numeric, 2) AS experience,
round(spare_capacity::numeric, 2) AS spare,
round(preference, 2) AS pref,
round(credential_runway::numeric, 2) AS runway,
round((0.35 * experience + 0.30 * spare_capacity
+ 0.25 * preference + 0.10 * credential_runway)::numeric, 3) AS score
FROM scored s JOIN instructor i USING (instructor_id)
ORDER BY score DESC, i.instructor_id;
The output for session 100:
| Instructor | Experience | Spare | Preference | Runway | Score |
|---|---|---|---|---|---|
| Ben | 1.00 | 0.80 | 0.50 | 1.00 | 0.815 |
| Ines | 0.25 | 0.90 | 0.80 | 0.33 | 0.591 |
| Gita | 0.50 | 0.10 | 1.00 | 0.64 | 0.519 |
Gita is the keenest, but 72 booked hours this month pull her to last under these weights. Shift enough weight to preference and she moves first, so keep the weights in one visible place. Treat the list as suggestions for a planner to confirm.
What goes wrong with instructor scheduling data in production?
Most failures come from stale data and unstated conventions, not from query bugs. The model is only as good as what people enter, and the logic cannot know about leave nobody recorded. Plan for these cases before launch:
- Leave that nobody records, so the instructor looks free.
- Renewals entered after the old credential lapsed, which blocks assignments until someone fixes the dates.
- Import files that treat expiry as the last valid day while your table treats it as the first invalid day.
- Co-teaching or a day-two substitute, which this query does not cover because it assigns one instructor to the whole session. Run the same checks per meeting instead.
- Two planners choosing the same instructor at once, which the booking table’s exclusion constraint rejects at write time.
Pro tip: Return reasons, not just a list. The table above came from running the availability and qualification filters separately. Build the same diagnostic into your tool, so when a planner asks why Chen is missing, the answer is “annual leave on November 18” and not “the algorithm says no”.
What should you do next?
Create the tables in a test database and load the ten instructors your planners find hardest: part-timers, frequent travelers and anyone with a credential about to expire. Run the candidate query and compare its list with what your planners would pick by hand. Every disagreement points to a missing rule or a wrong convention.
Then agree the score weights with the people who schedule. If you would rather buy than build, SimpliTrain’s training scheduling system page says the platform keeps session, instructor and learner records connected. It does not describe qualification matching, so ask for a demo and put credential expiry, prerequisites and travel buffers on your question list.
Frequently asked questions
How do you store instructor availability in a database?
Store weekly availability rules with a start time, end time and time zone name, then add a separate table of dated exceptions for leave and extra days. Keep confirmed bookings elsewhere. At query time, expand the rules into windows for the dates you need, subtract leave, and check that each meeting fits inside the result.
How do you check whether an instructor is qualified for a course?
Compare the credentials the instructor holds with the course’s requirement list. Each credential must be active, issued before the first day, unexpired through the last day, and backed by any prerequisite. Group requirements so that every group is satisfied, and any one credential inside a group is enough.
How should certification expiry affect instructor scheduling?
Treat expiry as a hard rule measured against the last meeting of the session, not the start date. A credential that lapses mid-course should disqualify the instructor. Use time remaining before expiry as a soft signal as well, so people with credentials that lapse soon are not chosen over equally good alternatives.
How do you rank instructors who are both available and qualified?
Score each candidate on signals such as recent experience with the course, spare capacity, stated preference and credential runway. Scale every signal to the same 0 to 1 range, apply weights your planners agree on, and show the components. Use a stable tie-breaker so results are repeatable.
How do you handle instructors who work in different time zones?
Store each availability rule with an IANA time zone name such as Europe/London, and convert the rule’s local start and end into exact instants when you expand it. Compare those instants with the meeting’s range. Never store a fixed UTC offset, because it will be wrong after a clock change.