How to Surrogate key for History data maintenance

Database
Enthusiast

How to Surrogate key for History data maintenance

Hi Team,


I have data like:-

Table A:-

emp_id     first_name     lat_name      email_adr

------      ----------        ------------     ---------

E1           DRAVID            B               xyz@apple.com

E2           DRAVID            B               ghj@apple.com

Table B:-

emp_id      Designation          start_from 

------        -----------             -----------

E1            Sales manager         1st Jan

E2            Manager                  10th Jan

Table C:-

emp_id       Courses_Completed

-------       -----------------

E1                 10

E2                   5

I want to see result like:-

emp_id          Designation     Courses_Completed

--------          ------------     ------------------------

E2                  Manager            15

Description:-

Initialy employee E1 was sales manager but after 9th jan it become E2 and designation becomes MANAGER.

Means both the employee E1 and E2 are same.Now i want ot see how many courses DRAVID has completed.

Is there any solution for above mentioned problem using "SURROGATE KEY"?

How i will use surrogate key in all the table,so that after join among the tables i should get desired result?

How i will maintain surrogate key to identify both E1 and E2 are same?

Regards,

Abhilash Kumar

Tags (1)