Designing a Time Axis for HR Systems: Interval vs Effective‑Date Models

In HR core systems the concept of a time axis is similar to the fourth dimension of spacetime: it records the lifecycle of employees, departments, and positions so that any point in the past or future can be reconstructed accurately.

Accurate historical data is essential for modules such as attendance, performance, and payroll, where calculations often span different dates (e.g., Q1 performance set in April, March salary paid in April, etc.).

Two widely used design patterns satisfy this requirement:

1. Interval model (SAP) – each record stores start_time, end_time and a status. Adjacent records never overlap; the last record’s end_time is set to a far‑future value (e.g., 9999‑12‑31).

create table table_department (
    code        varchar(20) not null comment '部门编号',
    start_time  datetime    not null comment '开始日期',
    end_time    datetime    not null comment '结束日期',
    status      int         not null comment '部门状态',
    name        varchar(20) not null comment '部门名称',
    parent_code varchar(20) not null comment '父部门编号',
    manager_id  varchar(20) not null comment '部门负责人工号'
);

Typical queries:

-- include inactive departments
select * from table_department
where start_time <= '2023-04-01' and end_time > '2023-04-01';

-- exclude inactive departments
select * from table_department
where start_time <= '2023-04-01' and end_time > '2023-04-01' and status = 1;

Maintenance rules: when inserting a new record, adjust the previous record’s end_time; when inserting historical data, shift existing intervals to avoid overlap.

2. Effective‑date model (PeopleSoft) – each record stores effdt (effective date), effseq (sequence), and status. Records are ordered by effdt and then effseq, producing N+1 intervals from N rows.

create table table_department (
    code        varchar(20) not null comment '部门编号',
    effdt       date        not null comment '生效日期',
    effseq      int         not null comment '生效序号',
    status      int         not null comment '部门状态',
    name        varchar(20) not null comment '部门名称',
    parent_code varchar(20) not null comment '父部门编号',
    manager_id  varchar(20) not null comment '部门负责人工号'
);

Typical queries (including a common mistake):

// wrong – status inside subquery
select * from table_department a
where a.effdt = (
    select max(b.effdt) from table_department b
    where b.status = 1 and b.code = a.code and b.effdt <= '2023-04-01')
  and a.effseq = (
    select max(c.effseq) from table_department c
    where c.status = 1 and c.code = a.code and c.effdt = a.effdt);

// correct – status applied after subqueries
select * from table_department a
where a.effdt = (
    select max(b.effdt) from table_department b
    where b.code = a.code and b.effdt <= '2023-04-01')
  and a.effseq = (
    select max(c.effseq) from table_department c
    where c.code = a.code and c.effdt = a.effdt)
  and a.status = 1;

Comparison:

Maintainability – effective‑date model is easier because only the date dimension needs to be managed.

Ease of use – interval model allows a simple range query (date between start and end).

Typical error – placing status filter inside the subquery leads to wrong results.

Evolution of the time‑axis design in the company:

No time axis – changes take effect immediately.

Daily snapshots – static copies used for historical queries.

Self‑built lifecycle using interval model – precise to seconds but only for current day.

Adoption of PeopleSoft – professional effective‑date model becomes the core data source.

Future – replace PeopleSoft with a self‑developed solution that combines the strengths of both models.

Implementing a time axis brings significant challenges for developers: complex maintenance, high data‑quality requirements, and careful coordination with downstream systems that may not need the temporal dimension.

In summary, a well‑designed time axis transforms HR data from a set of isolated records into an ordered, traceable timeline, enabling both retrospective analysis and future planning for large enterprises.