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

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.

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

StackExchange Database Administrators Q#177976, answer score: 2