Temporal concept

Database
KVB
Enthusiast

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 (1)
3 REPLIES
KVB
Enthusiast

Re: Temporal concept

Can i get help from anyone?

Enthusiast

Re: Temporal concept

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!

Teradata Employee

Re: Temporal concept

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/Gett...

HTH!