All Forums Database
KVB 124 posts Joined 09/12
06 Feb 2014
Temporal concept

Hi ,
  I am using SCD type 2 in my project.Below is the example of versioning which i maintain.
empno ename sal eff_dt  end_dt  curr_in rcv_in
1 a 100 2013-01-01 9999-12-31 1 1
Above is an active record because end_dt shows as high end date and curr_in shows as 1.
Suppose if there is any update on the employee,then an extra row gets added to the table as shown below.
empno ename sal eff_dt  end_dt  curr_in rcv_in
1 a 100 2013-01-01 2013-12-31 0 1
1 a 200 2014-01-01 9999-12-31 1 1
I hope we do the same using temporal concept.But is the history maintained in the temporal or it will get override the dates when there is any change.
Suppose a new row gets added to the table then what is the difference between temporal and normal concept?
Could you please give me any hint on this.I feel somewhere I am missing.
Regards
KVB

Tags:
KVB 124 posts Joined 09/12
07 Feb 2014

Can i get help from anyone?

addsmiles 13 posts Joined 01/12
07 Feb 2014

Temporal tables will maintain the history for you, just like you mentioned in your example. When you update a row. For example in this case:
UPDATE EMPLOYEE
SET SALARY = 200
WHERE EMPLOYEE_NBR = 1;
Teradata will automatically update the End date of the current row and create a new row with updated salary and high end date as in your example.
When you query the tables using normal SQL you will see only active rows. To see the history, you will need to temporal keywords (SEQUENCED, NONSEQUENCED, CURRENT) in your SQL.
I believe using temporal tables you can achieve what you want. Please refer to temporal table support reference book for detailed info.
Hope this helps!
 

Adeel Chaudhry 773 posts Joined 04/08
08 Feb 2014

Temporal tables are introduced to help reduce the [same] SCD logic to be re-developed on every other project. Hence it achieves the same functionality but without any logic to be developed rather at a DB level, which makes it more efficient and more reliable.
You can read more about it at following link:
 
http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1182_111A/GettingStarted.156.5.html
 
HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.