How to keep history for a one-to-many relationship?

Question

I have a simple one-to-many (1:M) relationship but I need to keep a history for the changes.

For example, a task can have many employees, but each employee can only have one task at a time.

I need to keep a history for the task assignments in order to know the past tasks that were assigned to each employee.

I know that I can convert it to a many-to-many (M:N) relationship and add a “flag” column, but this will not keep the relationship constraint that stipulates that only one task can be assigned.

I'm pretty sure that there is a design pattern or best practice for the case but I can't find it. I'm using Oracle DBMS.

Employee Columns:

• ID
• Task_ID (FK_Task_ID)
• Employee Name
• Other Employee Data

Task Columns:

• ID (PK)
• Task Details

This is a direct (1:M) relation. The required is what if I want to keep log for all the tasks assigned for a certain Employee? The trivial solution would be make a many to many relation and adding intermediate table as follows:

Employee_Task Columns:

• ID
• Employee_ID (FK_Employee_ID)
• Task_ID (FK_Task_ID)

This will remove the constraint that each employee should have only one assigned task at a time.

Answer 1

Unsure there is a design pattern for what you are suggesting. A separate history table would accomplish what you want, but from your question it looks like this is not what you are after.

An alternative in Oracle though, I believe is, that you can make a function based unique indexes which would ensure each Employee has a single active task. This would look something like:

CREATE TABLE task
( 
    task_id number(10) NOT NULL,
    task_name varchar2(100),
    task_description varchar2(500),
  CONSTRAINT task_pk PRIMARY KEY (task_id)
);

CREATE TABLE employee
( 
    employee_id number(10) NOT NULL,
    employee_name varchar2(100),
  CONSTRAINT employee_id PRIMARY KEY (employee_id)
);


CREATE TABLE employee_task
(
  task_id number(10) NOT NULL,
  employee_id number(10) NOT NULL,
  active_task char(1) check (active_task in ( 'Y', 'N' )),
  CONSTRAINT fk_employee_id FOREIGN KEY (employee_id)   REFERENCES employee(employee_id),
  CONSTRAINT fk_task_id FOREIGN KEY (task_id)   REFERENCES task(task_id)
);


CREATE UNIQUE INDEX only_one_active_per_employee ON employee_task (
    CASE WHEN active_task='Y' THEN employee_id ELSE NULL END
);

This makes use of Oracle not storing NULL values in B-tree indexes, meaning if a task is set to "N", it is not stored in the index tree and therefore all "N" records are not part of the unique index. If you were to try and assign an employee to a task that was active who also was assigned another active task, you should get a unique index violation.

Answer 2

Suppose we have 3 tables - one for employees, one for tasks, and one for "task_assignments", like so:

create table employees(
  employee_id number primary key
, employee_name varchar2(64)
, additional_information varchar2(64)
);

create table tasks(
  task_id number primary key
, task_details varchar2(64)
);

create table task_assignments(
  employee_id number references employees(employee_id)
, task_id number references tasks(task_id)
, start_date date not null 
, end_date date
, unique (employee_id, start_date)
);

The task_assignments table stores "historical" data. We assume that each employee can only work on one task at a time (as stated in your question). The task has a start_date (or time) and end_date. We can use a trigger for preventing INSERTs if an employee already has an "open" task (ie the end_date of the task is null). NOTE: the example trigger does not cover UPDATEs.

create or replace trigger one_active_task_only
before insert on task_assignments
for each row
declare
  open_tasks number := 0 ;
begin
  select count(*) into open_tasks
  from task_assignments
  where employee_id = :new.employee_id
    and end_date is null ;    

  if open_tasks >= 1 then
    raise_application_error (-20500,'This employee already has an open task');
  end if;
end one_active_task_only;
/

alter trigger one_active_task_only enable;

Testing and more details etc: see dbfiddle

Answer 3

It sounds like what you need is "system-versioned tables" from the SQL2011 standard. Oracle doesn't support them itself but if you read around how MS SQL Server 2016 implements them (there the concept is called system-versioned temporal tables) it is a pattern that you can emulate efficiently enough yourself - I've used a similar pattern, manually implemented, for history/auditing in various projects, long before there was built-in support in SQL Server (the built-in support can make things much easier, and sometimes more efficient, of course!).