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.

  • Ok I added more details on the question body
    – KZD
    Jul 4 '17 at 22:59

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:

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

    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.

  • The index solution will force the required logic but is it considered over kill for the problem? I have one table that get 12 dynamic values from 12 table. In this I should make 12 index. What is the effect of this approach on the performance.
    – KZD
    Jul 4 '17 at 23:07
  • Sorry, your requirement for this to happen over 12 different values or columns is not specified in your question. Could you give us the actual problem in your question instead of specifying something similar? The performance effect will probably be as good as you can get. Implementing another solution would require SELECT lookup statements across the entire table, whereas the index solution stores only unique combinations of employee_id and tasks set to "Y". This means the amount of data scanned would be considerably less in the index solution proposed.
    – blobbles
    Jul 5 '17 at 0:09
  • In fact, I just realised there is no requirement to store the active_task value with the employee_id in the index. The index will only hold employee_id's with active tasks now, which should enhance performance.
    – blobbles
    Jul 5 '17 at 0:35
  • The actual case is a management system using web application for a massive corporation. in the real application they have a lot of dynamic information as their vehicles.
    – KZD
    Jul 5 '17 at 6:20
  • 1
    According to your description now, it sounds like what you are looking for is an Entity-Attribute-Value model with a history table. This is why you need to tell us the real case, we cannot model something that you are interpreting as an equivalent of something else. It is like me asking "What is they answer of the equation 3 + 4?" when I really want to ask "What is the answer to the equation 4^3?". One answer does not help me solve the other.
    – blobbles
    Jul 5 '17 at 20:52

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
  open_tasks number := 0 ;
  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


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!).

  • Are you aware of Oracle's "Workspace Manager", which allows "versioning"? (see docs.oracle.com/database/121/ADWSM/long_intro.htm#ADWSM99112) Also, I fail to see how versioning - as such - would allow us to define/enforce the (required) constraint that "each employee can only have one task at a time" (second sentence in the original question).
    – stefan
    Jul 13 '17 at 8:16
  • 1
    No you are right, history wouldn't enforce anything, but if you enforce it in the current data (a junction table for person<->task with a unique constraint on person?) and then worry about how to keep a history of that - separating the two concerns somewhat. I'm not an Oracle person myself so aren't much aware of its specifics. Scanning that page quickly, it sounds like the workspace feature is more akin to how snapshots work in filesystems and VMs so not really intended as an implementation of system versioned tables from the SQL2011 standard. Jul 13 '17 at 10:28
  • Do you have any experience with temporal tables in SQL Server? How does it handle relations? If I'm right it will store history in separate table tasks_history or something like that. So the task in tasks will be the most recent one. It certainly seems like a more complicated approach to this problem. Or maybe it just seems so.
    – Konrad
    Aug 13 '18 at 8:15
  • 1
    @Konrad - I've used temporal tables. They don't handle relations: they only version single tables and the history table can not have foreign keys (a key reason some people roll their own instead of using the built-in feature, though if you also make the parent versioned any FK in the base tables is satisfied for relevant points in time by the data in the history copies). Yes, the history table contains all versions of each row and the base table contains just the latest. If you roll your own you of course lose the convenience of FOR SYSTEM_TIME AS OF and related syntax. Aug 13 '18 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.