I am creating a financial application using with Asp .net mvc5 with EF6
.I have a big puzzle about updating database. What I want is that I have Person
table (e.g. firstname, lastname, address, salary, tax, and so on)
. For the first time, I entered a record (e.g. John, Smith, US, 3500.00,)
. When John Smith
gets promotion, I will update his salary as 4000.00
while I am still wanting to keep his old salary 3500.00
. If I don't keep his 3500.00
, his tax
and other deductible
fees from his 3500.00
salary won't reflect correctly
upto the last day that he earned 3500.00
. Data of his tax all along getting 3500.00
will lose and will reflect based on 4000.00
.
Can anyone guide me on how to solve this, please? Appreciate if you can share any resources or links that I can study for this kind of problem. Thank in advance.
you need to create a table corresponding to the main table Person
lets call it Person_log
(e.g. firstname, lastname, address, salary, tax,Entry_Date_On_Log and so on)
Process 1:
create a trigger for insert,update and insert record to the Person_log
table on insert and every update on Person
table.
Process 2:
you can manually(using query code) enter the records to the Person_log
table on every and each entry to the Person
table.
Now, whenever you want to calculate the Tax or anything else corresponding to a date for a Person, you can easily find the Salary or any other records for a Person from
Person_log
table using the Date filter(Entry_Date_On_Log
).