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 &gt;= 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