How to manage history data

In previous article ‘Tips to create table’, we create a table ‘EMP’. Purpose of this table was to store employee’s basic information.

Today’s tip will cover the functionality how to manage data that will be changed in future. If Employee’s department number changed from 10 to 20, no one knows this employee is transferred from department number 10.

To get this problem solved we will use another table called ‘EMP_DEPT_ASSIGN’

Script:

Create table EMP_DEPT_ASSIGN (
Emp_dept_assign_id number,
Emp_id number, -- Foreigh Key Column, master is EMP
Dept_id number, -- Foreigh Key Column, master id DEPT
Start_date date,
End_date date,
Created_by number,
Creation_date date default sysdate,
Last_updated_by number,
Updation_date date,
Enabled number(1) /*default value ‘1’*/ );

Create sequence SQ_EMP_DEPT_ASSIGN;
Create Or Replace Trigger TBI_EMP_DEPT_ASSIGN
Before insert on EMP_DEPT_ASSIGN
For each row
Begin
If :new.Emp_dept_assign_id is null then
Select sq_Emp_dept_assign.nextval into :new.Emp_dept_assign_id
from dual;
End if;
End;
/

Insert into Emp_dept_assign(
Emp_dept_assign_id,
emp_id,
dept_id,
start_date,
end_date,
created_by
,creation_date
,Enabled) Values
(null,
1,
10,
’01-SEP-11’,
null,
100, /* User id*/
Sysdate,
1);

Update Emp_dept_assign set end_date=sysdate, enabled=0
where emp_id=1;

Insert into Emp_dept_assign(
Emp_dept_assign_id,
emp_id,
dept_id,
start_date,
end_date,
created_by
,creation_date
,Enabled) Values
(null,
1,
20,
sysdate,
null,
100, /* User Id*/
Sysdate,
1);

Commit;

Select t.*,t.rowid from Emp_dept_assign t;

You will find employee’s joining dates to department 10 and 20, this will keep all history data and you will find actual picture of all employees.

Tips to create table

Create table with following default columns always

1. ID (Unique values)
2. Created_by
3. Creation_date default sysdate
4. updated_by
5. last_update_date
6. Enable default ‘1’ /* 1 = Enabled , 0 = Not Enabled */Create sequence for every table to generate auto number, with the same name as table, add sq_ as prefix.

Create trigger for unique column in table and use sequence to populate values.

Script:

Create table EMP (
Emp_id number, -- Primary Key Column
Name varchar2(100),
DOB date,
Created_by number,
Creation_date date default sysdate,
Last_updated_by number,
Updation_date date,
Enabled number(1) /*default value 1*/
);

Create sequence sq_emp;

Create trigger tbi_table_name
Before insert on emp
For each row
Begin
If :new.emp_id is null then
Select sq_emp.nextval into :new.emp_id from dual;
End if;
End;
/

Insert into Emp(
emp_id,
name,
DOB,
created_by
,creation_date
,Enabled) Values
(null,
'Amjad Ali',
'31-Dec-1974',
100, -- User id which would be available in other table user.
Sysdate,
'1');

Commit;

Select * from Emp;