Department assignment: an employee's history, not a column

"Which department is she in?" sounds like a question about one field on an employee record. In the Employees sample database it is not. There is no department column on employees at all. The answer lives in dept_emp, a table of department assignments: each row says that one employee belonged to one department from one date to another.

That small design choice changes what "the department" means. A person does not have a department; they have a history of assignments, and "current department" is a question you ask of that history on a particular day.

What one assignment row says

An assignment links an employee to a department for a period:

Employee 10010 has two rows. They worked in Production from 1996 until 26 June 2000, then moved to Quality Management (d006) on the same day, with a to_date of 9999-01-01. Neither row is wrong and neither is "the old value": together they are the employee's department history.

9999-01-01 means "still true"

The sample marks an assignment that has not ended with the far-future date 9999-01-01. It is a convention, not a prediction. In the browser edition of the Employees sample, 826 of the 1,127 assignments carry it.

Two habits keep that date from leaking into results:

• Filter on it deliberately. "Current department" is to_date = '9999-01-01', or more robustly to_date > :today. Leave it out and every former department counts too.
• Never do arithmetic on it. Average tenure computed from to_date - from_date turns an open assignment into an 8,000-year career. Replace the sentinel with today's date, or measure open assignments separately.

Some people have no current department

Out of 1,024 selected employees, 103 have two assignments — they moved between departments. Nobody has two open assignments at once.

But 198 employees have no open assignment at all: their latest to_date is a real date in the past. In this dataset that is how a departure looks. A query that joins employees to their open assignment silently drops them; a headcount that ignores to_date silently keeps them. Decide which question you are answering before choosing the join.

The source also ships a view, current_dept_emp, that returns each employee's latest assignment. Latest is not the same as current: for those 198 people, the latest assignment has already ended. Check to_date even when using the view.

Boundaries: which day belongs to which department?

Employee 10010's Production assignment ends on 2000-06-26 and their Quality Management assignment starts on 2000-06-26. If both dates are treated as inclusive, that day is counted in both departments, and a daily headcount double-counts the move.

The usual reading is half-open: an assignment covers from_date up to, but not including, to_date. Write the condition that way — from_date <= :day AND :day < to_date — and every day belongs to exactly one assignment. Whatever rule you choose, state it next to the numbers it produced.

One row per employee and department

The table's key is (emp_no, dept_no). That keeps the model simple, but it also means an employee who leaves a department and later returns cannot have two separate rows for it. If your own system needs to record a return, the period has to be part of the key — an assignment is identified by who, where and when.

Managers are assignments too

Department managers follow the same pattern in dept_manager: a department manager assignment links an employee to the department they manage, with its own dates. The sample has 24 of them across nine departments, and nine are open — one current manager per department.

A manager assignment is a separate fact from a department assignment. Managing Sales and working in Sales are recorded in different tables and can have different dates. Keep them apart when asking "who works in Sales?".

Read the history, then ask the question

An employee is a person in an employment context, and the context changes over time. The Employees sample makes that explicit with dated rows for departments, titles and salaries. The salary-history guide reads pay the same way: one dated row per change. The person guide follows the idea across roles: one human, several relationships, each with its own story.

Before reporting "employees by department", decide three things: on which day, with which boundary rule, and whether people without a current assignment belong in the count.