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

database-designoracle

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.

Best Answer

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.

Related Question