Thursday, February 21, 2013 10:44 PM
I have a table A which has 4 cols
ID col col1 col2 col3
Now ID is created for relational purpose which is ok and col value is also fixed but col1,col2 and col3 are not fixed values. The values of last 3 columns can be changed. it can be daily , weekly or monthly.
What I want that if I store ID to SomeOtherTable, I would like to remember what was the values of col1,col2 and col3 when a ID was stored in SomeOtherTable.
What is the best way to do this? Dont want to add 3 cols along with ID in SomeOtherTable.
Thursday, February 21, 2013 11:11 PM
It appears to me the more appropriate way would be instead of updating the col2, col3, col4 values, you could append them as new rows and this will have new ID values(assuming ID column is the primary) and you can put this ID value as reference in the second table and you can look backup both current and past values using ID field. I think this is better approach, since you anyways need to log or store the col2,col3,col4 information somewhere, if not in this table. Atleast this approach would reduce the need to look up between 2-3 tables and if you want to narrow down to the exact difference milli seconds in the timestamp might play a bit...if you do not want to do above way,
I think you would need a Timestamp Column in the "someothertable" that refers to when ID values was inserted/updated and you would need some kind of logging either through triggers or CDC to capture the changes in the col1,col2,col3 values along with Timestamp and ID field. you can later use this timestamp and Id to look back the col1,Col2,Col3 values.
Hope it Helps!!
Friday, February 22, 2013 12:03 AM
Appending may not be suitable to you as you might be updating based on these id and col values.
i would do in the following way:
as you want to maintain the initial values, where do you want to keep those values?
1) create a new table same like existing one with out any references.
2) fire a trigger when you insert data into the original table to insert same records in to your new table.
so any updates in your table will not effect your new table.
Friday, February 22, 2013 12:09 AM
+1 for Stan210's design. It's so common in data warehouse design that it has a special name.