Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Enable UUID for surrogate keys (optional)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Employee Table (Surrogate Key)
CREATE TABLE employees (
    emp_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL
);

-- Job Assignments (One fact: "Employee X held Job Y at Time T")
CREATE TABLE job_assignments (
    emp_id UUID REFERENCES employees(emp_id),
    job_title TEXT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    PRIMARY KEY (emp_id, start_date) -- Prevents overlaps
);

-- Salaries (One fact: "Employee X had Salary S at Time T")
CREATE TABLE salaries (
    emp_id UUID REFERENCES employees(emp_id),
    salary NUMERIC(10,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    PRIMARY KEY (emp_id, start_date) -- Prevents overlaps
);

-- Insert Sample Data
INSERT INTO employees (emp_id, name) VALUES
    ('11111111-1111-1111-1111-111111111111', 'Alice'),
    ('22222222-2222-2222-2222-222222222222', 'Bob');

-- Job history of Alice
INSERT INTO job_assignments (emp_id, job_title, start_date, end_date) VALUES
    ('11111111-1111-1111-1111-111111111111', 'Software Engineer', '2020-01-01', '2021-06-30'),
    ('11111111-1111-1111-1111-111111111111', 'Senior Engineer', '2021-07-01', '2023-12-31');

-- Salary history of Alice
INSERT INTO salaries (emp_id, salary, start_date, end_date) VALUES
    ('11111111-1111-1111-1111-111111111111', 70000, '2020-01-01', '2021-06-30'),
    ('11111111-1111-1111-1111-111111111111', 85000, '2021-07-01', '2023-12-31');

-- Job history of Bob
INSERT INTO job_assignments (emp_id, job_title, start_date, end_date) VALUES
    ('22222222-2222-2222-2222-222222222222', 'Data Analyst', '2019-03-01', '2022-08-31');

-- Salary history of Bob
INSERT INTO salaries (emp_id, salary, start_date, end_date) VALUES
    ('22222222-2222-2222-2222-222222222222', 60000, '2019-03-01', '2022-08-31');

-- Query to reconstruct full history
SELECT e.name, j.job_title, j.start_date, j.end_date, s.salary
FROM employees e
JOIN job_assignments j ON e.emp_id = j.emp_id
JOIN salaries s ON e.emp_id = s.emp_id AND j.start_date = s.start_date
ORDER BY e.name, j.start_date;

CREATE EXTENSION

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 2

INSERT 0 2

INSERT 0 2

INSERT 0 1

INSERT 0 1

name	job_title	start_date	end_date	salary
Alice	Software Engineer	2020-01-01	2021-06-30	70000.00
Alice	Senior Engineer	2021-07-01	2023-12-31	85000.00
Bob	Data Analyst	2019-03-01	2022-08-31	60000.00

SELECT 3