MySQL Employee Database: Managers and Salary History

Project goal: the people data of a company: who works where, who reports to whom, and what each person earned over time. It combines a self-referencing table (managers), a history table (salaries) and window functions (comparisons inside a department).

Requirements

Departments have a name. Employees belong to one department and have a job title, a hire date and (optionally) a manager who is also an employee.
Nobody is their own manager.
Salaries change over time, and the old values must be kept. At any date, an employee has at most one salary.
A raise closes the current salary and starts a new one, as one atomic action.
Reports: the org chart, headcount and payroll per department, the highest paid employees per department, and the salary of an employee on a past date.

Tables and relationships

Table	Relationship
`departments` 1 — N `employees`	An employee has one department
`employees` 1 — N `employees`	`manager_id` points to another employee (self-reference)
`employees` 1 — N `salary_history`	One row per salary period

The schema

query.sql

SQL

CREATE TABLE departments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL,
    UNIQUE KEY uq_departments_name (name)
);

CREATE TABLE employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL,
    department_id INT UNSIGNED NOT NULL,
    manager_id INT UNSIGNED NULL,
    job_title VARCHAR(40) NOT NULL,
    hire_date DATE NOT NULL,
    UNIQUE KEY uq_employees_email (email),
    KEY idx_employees_department (department_id),
    KEY idx_employees_manager (manager_id),
    CONSTRAINT fk_employees_department FOREIGN KEY (department_id) REFERENCES departments (id),
    CONSTRAINT fk_employees_manager FOREIGN KEY (manager_id) REFERENCES employees (id)
);

CREATE TABLE salary_history (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id INT UNSIGNED NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NULL,
    open_employee_id INT UNSIGNED GENERATED ALWAYS AS (IF(valid_to IS NULL, employee_id, NULL)) STORED,
    UNIQUE KEY uq_one_current_salary (open_employee_id),
    KEY idx_salary_employee (employee_id, valid_from),
    CONSTRAINT chk_salary_positive CHECK (amount > 0),
    CONSTRAINT chk_salary_period CHECK (valid_to IS NULL OR valid_to > valid_from),
    CONSTRAINT fk_salary_employee FOREIGN KEY (employee_id) REFERENCES employees (id)
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

A CHECK constraint cannot refer to an AUTO_INCREMENT column, so “nobody is their own manager” is enforced with a small trigger. (On an insert the id does not exist yet, so only an update can make someone their own manager.)

query.sql

SQL

DELIMITER //

CREATE TRIGGER trg_employees_not_own_manager BEFORE UPDATE ON employees FOR EACH ROW
BEGIN
    IF NEW.manager_id = NEW.id THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An employee cannot be their own manager';
    END IF;
END//

DELIMITER ;

Output

OUTPUT

Query OK, 0 rows affected

The generated column open_employee_id plus its UNIQUE index guarantee at most one open salary row per employee (a trick explained in The Design Process). NULL values are ignored by the unique index, so closed periods do not clash.

Sample data

query.sql

SQL

INSERT INTO departments (name) VALUES ('Management'), ('Engineering'), ('Sales');

INSERT INTO employees (name, email, department_id, manager_id, job_title, hire_date) VALUES
('Asha Rao', 'asha@corp.example', 1, NULL, 'CEO', '2018-01-15'),
('Ben Iyer', 'ben@corp.example', 2, 1, 'Engineering Manager', '2019-03-01'),
('Cara Das', 'cara@corp.example', 2, 2, 'Senior Developer', '2020-06-15'),
('Dev Nair', 'dev@corp.example', 2, 2, 'Developer', '2022-09-01'),
('Esha Roy', 'esha@corp.example', 2, 3, 'Junior Developer', '2024-02-01'),
('Farid Khan', 'farid@corp.example', 3, 1, 'Sales Manager', '2019-08-10'),
('Gita Shah', 'gita@corp.example', 3, 6, 'Sales Executive', '2023-04-17'),
('Hari Menon', 'hari@corp.example', 3, 6, 'Sales Executive', '2024-07-01');

INSERT INTO salary_history (employee_id, amount, valid_from, valid_to) VALUES
(1, 300000, '2018-01-15', NULL),
(2, 150000, '2019-03-01', '2023-04-01'), (2, 175000, '2023-04-01', NULL),
(3, 110000, '2020-06-15', '2022-07-01'), (3, 130000, '2022-07-01', NULL),
(4, 90000, '2022-09-01', NULL),
(5, 60000, '2024-02-01', NULL),
(6, 120000, '2019-08-10', NULL),
(7, 70000, '2023-04-17', NULL),
(8, 65000, '2024-07-01', NULL);

Output

OUTPUT

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

Query OK, 10 rows affected
Records: 10  Duplicates: 0  Warnings: 0

Query 1: the org chart

query.sql

SQL

WITH RECURSIVE chart AS (
    SELECT id, name, job_title, 0 AS level, CAST(LPAD(id, 4, '0') AS CHAR(100)) AS sort_key
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.job_title, c.level + 1, CONCAT(c.sort_key, '.', LPAD(e.id, 4, '0'))
    FROM employees e JOIN chart c ON e.manager_id = c.id
)
SELECT CONCAT(REPEAT('    ', level), name) AS employee, job_title, level FROM chart ORDER BY sort_key;

Output

OUTPUT

+----------------------+---------------------+-------+
| employee             | job_title           | level |
+----------------------+---------------------+-------+
| Asha Rao             | CEO                 |     0 |
|     Ben Iyer         | Engineering Manager |     1 |
|         Cara Das     | Senior Developer    |     2 |
|             Esha Roy | Junior Developer    |     3 |
|         Dev Nair     | Developer           |     2 |
|     Farid Khan       | Sales Manager       |     1 |
|         Gita Shah    | Sales Executive     |     2 |
|         Hari Menon   | Sales Executive     |     2 |
+----------------------+---------------------+-------+
8 rows in set

The sort_key (the ids of the path, padded) makes each person appear right under their manager (Recursive Hierarchies).

Query 2: who reports to whom, and how many

query.sql

SQL

SELECT m.name AS manager, COUNT(e.id) AS direct_reports, GROUP_CONCAT(e.name ORDER BY e.name SEPARATOR ', ') AS reports
FROM employees m JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC, m.name;

Output

OUTPUT

+------------+----------------+-----------------------+
| manager    | direct_reports | reports               |
+------------+----------------+-----------------------+
| Asha Rao   |              2 | Ben Iyer, Farid Khan  |
| Ben Iyer   |              2 | Cara Das, Dev Nair    |
| Farid Khan |              2 | Gita Shah, Hari Menon |
| Cara Das   |              1 | Esha Roy              |
+------------+----------------+-----------------------+
4 rows in set

Query 3: current salaries and payroll per department

A view gives everyone the current salary without repeating the valid_to IS NULL rule (Views):

query.sql

SQL

CREATE VIEW current_salaries AS
SELECT employee_id, amount, valid_from FROM salary_history WHERE valid_to IS NULL;

SELECT d.name AS department, COUNT(*) AS headcount, SUM(cs.amount) AS monthly_payroll, ROUND(AVG(cs.amount)) AS average_salary
FROM departments d
JOIN employees e ON e.department_id = d.id
JOIN current_salaries cs ON cs.employee_id = e.id
GROUP BY d.id, d.name
ORDER BY monthly_payroll DESC;

Output

OUTPUT

+-------------+-----------+-----------------+----------------+
| department  | headcount | monthly_payroll | average_salary |
+-------------+-----------+-----------------+----------------+
| Engineering |         4 |       455000.00 |         113750 |
| Management  |         1 |       300000.00 |         300000 |
| Sales       |         3 |       255000.00 |          85000 |
+-------------+-----------+-----------------+----------------+
3 rows in set

(The amounts are a monthly salary in this sample. Use whatever unit your company uses, and keep it consistent.)

Query 4: ranking and comparison inside a department

query.sql

SQL

SELECT d.name AS department, e.name, cs.amount,
       RANK() OVER (PARTITION BY d.id ORDER BY cs.amount DESC) AS rank_in_department,
       ROUND(cs.amount / AVG(cs.amount) OVER (PARTITION BY d.id) * 100) AS pct_of_department_average
FROM employees e
JOIN departments d ON d.id = e.department_id
JOIN current_salaries cs ON cs.employee_id = e.id
ORDER BY d.name, rank_in_department, e.name;

Output

OUTPUT

+-------------+------------+-----------+--------------------+---------------------------+
| department  | name       | amount    | rank_in_department | pct_of_department_average |
+-------------+------------+-----------+--------------------+---------------------------+
| Engineering | Ben Iyer   | 175000.00 |                  1 |                       154 |
| Engineering | Cara Das   | 130000.00 |                  2 |                       114 |
| Engineering | Dev Nair   |  90000.00 |                  3 |                        79 |
| Engineering | Esha Roy   |  60000.00 |                  4 |                        53 |
| Management  | Asha Rao   | 300000.00 |                  1 |                       100 |
| Sales       | Farid Khan | 120000.00 |                  1 |                       141 |
| Sales       | Gita Shah  |  70000.00 |                  2 |                        82 |
| Sales       | Hari Menon |  65000.00 |                  3 |                        76 |
+-------------+------------+-----------+--------------------+---------------------------+
8 rows in set

Window functions keep every row and add the department’s average beside each employee, which GROUP BY cannot do (Window Aggregates).

Query 5: a salary on a past date

query.sql

SQL

SELECT e.name, sh.amount
FROM employees e JOIN salary_history sh ON sh.employee_id = e.id
WHERE e.name IN ('Ben Iyer', 'Cara Das') AND '2022-01-01' >= sh.valid_from AND (sh.valid_to IS NULL OR '2022-01-01' < sh.valid_to)
ORDER BY e.name;

Output

OUTPUT

+----------+-----------+
| name     | amount    |
+----------+-----------+
| Ben Iyer | 150000.00 |
| Cara Das | 110000.00 |
+----------+-----------+
2 rows in set

Giving a raise

Both steps in one transaction, so there is never a gap or two open salaries (Price History):

query.sql

SQL

DELIMITER //

CREATE PROCEDURE give_raise(IN p_employee INT UNSIGNED, IN p_new_amount DECIMAL(10,2), IN p_effective DATE)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    UPDATE salary_history SET valid_to = p_effective WHERE employee_id = p_employee AND valid_to IS NULL;
    INSERT INTO salary_history (employee_id, amount, valid_from, valid_to) VALUES (p_employee, p_new_amount, p_effective, NULL);
    COMMIT;
END//

DELIMITER ;

CALL give_raise(4, 100000, '2026-04-01');
SELECT amount, valid_from, valid_to FROM salary_history WHERE employee_id = 4 ORDER BY valid_from;

Output

OUTPUT

+-----------+------------+------------+
| amount    | valid_from | valid_to   |
+-----------+------------+------------+
|  90000.00 | 2022-09-01 | 2026-04-01 |
| 100000.00 | 2026-04-01 | NULL       |
+-----------+------------+------------+
2 rows in set

The generated column would stop a mistake that left two open rows:

query.sql

SQL

INSERT INTO salary_history (employee_id, amount, valid_from) VALUES (4, 120000, '2026-05-01');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry '4' for key 'salary_history.uq_one_current_salary'

The rules protect the data

query.sql

SQL

UPDATE employees SET manager_id = id WHERE id = 5;

Output

OUTPUT

ERROR 1644 (45000): An employee cannot be their own manager

Common mistakes

Storing the salary on the employees row only. The history is lost with every raise.
Two open salary rows, from a missing transaction or code that forgets to close the old one. The unique generated column catches it.
Cycles in the management chain (A manages B, B manages A). The trigger stops only the simplest case, a person managing themselves. Check for longer cycles before changing a manager (Hierarchies).
Deleting a manager who has reports. The foreign key refuses. Reassign the reports first.
Salary data visible to everyone. Salaries are sensitive. Give HR accounts access, and everybody else a view without the salary table (Privacy).
AVG over a join that repeats rows, which distorts averages.

Final result

query.sql

SQL

SELECT (SELECT COUNT(*) FROM employees) AS employees, (SELECT COUNT(*) FROM salary_history) AS salary_rows, (SELECT COUNT(*) FROM salary_history WHERE valid_to IS NULL) AS current_salaries, (SELECT SUM(amount) FROM current_salaries) AS total_monthly_payroll;

Output

OUTPUT

+-----------+-------------+------------------+-----------------------+
| employees | salary_rows | current_salaries | total_monthly_payroll |
+-----------+-------------+------------------+-----------------------+
|         8 |          11 |                8 |            1020000.00 |
+-----------+-------------+------------------+-----------------------+
1 row in set

Eight employees, each with exactly one current salary. Next steps: leave and attendance tables, terminations (termination_date and closing the open salary), job grades with salary bands, and column-level privileges so that managers see only their own team (Privileges).