0

I manage data-tier applications for a small company and my SW is receiving criticism for the fact that information for part-costing can't be retrieved historically. So, for instance, what they would like is to be able to, at any point in time, retrieve the cost of a part as it was 6 months ago.

They used to do this through spreadsheets. They would copy the part table every day into a .xlsx file, and then anytime they wanted to know "hey, what was the cost of that part Jan 20 of last year?", they could just pull it up in excel.

So, we've begun doing the same thing in SQL, and the plan so far is that we will create a new table each time the part costs are updated, name the table with today's date, and persist it in a database for archived information. Then, we're planning to pull in whichever table we need according to it's time-stamp.

I can't help but think this is going to get very messy. Is this a bad approach for archiving data? Are there any industry standards I can adhere to for solving this problem in as few headaches as possible?

Jace
  • 777
  • 1
  • 5
  • 18
  • 1
    Have a look here: http://stackoverflow.com/questions/3874199/how-to-store-historical-data – cha Jul 29 '16 at 00:00
  • 2
    You're right, that's a bad approach. Dynamically named tables makes writing queries difficult. Instead, you could simply add a datestamp column to the relevant tables. You *could* have separate tables for current and previous values (but still just the one table for all previous values). – Blorgbeard Jul 29 '16 at 00:00

2 Answers2

1

So, we've begun doing the same thing in SQL, and the plan so far is that we will create a new table each time the part costs are updated, name the table with today's date, and persist it in a database for archived information. Then, we're planning to pull in whichever table we need according to it's time-stamp.

I can't help but think this is going to get very messy. Is this a bad approach for archiving data? Are there any industry standards I can adhere to for solving this problem in as few headaches as possible?

You are right ... this solution will be messy.

Simplest thing that you can do is to create a History table say Parts_History that will have all the columns as the main Parts table and add additional timestamp column(s) to track updates. Every time there is a new price for a part ( which I hope is done thru a stored procedure) the existing price gets moved into the new table and the main table gets updated ALL Inside one transaction. If you dont have a single SP that handles the update then you can do that inside a trigger.

I will try and see if there are any good examples out there.

objectNotFound
  • 1,683
  • 2
  • 18
  • 25
  • So, instead of copying the table each time an update is made, just copy the old version of the part to `Part_History`, and overwrite the active record with new values. The historical data can then be queried by a time-stamp applied to the record on creation. I can't believe I didn't think of that. Thank you. – Jace Jul 29 '16 at 00:20
  • 1
    @Jace you are welcome. Have a look at the rich mans :) option for tracking/auditing available in SQL 2016 : https://msdn.microsoft.com/en-us/library/dn935015.aspx – objectNotFound Jul 29 '16 at 00:34
1

As far as I now there is no standard but approach is rather obvious. You have a table say part(partid int primary key, price decimal). Create an audit table part_audit(auditId int identity(1,1) primary key, partId int, price decimal, dateChange datetime default getdate()) and a trigger on part after update, delete. In the trigger check update(price) and if so insert into part_audit from deleted. To find historical price select nearest dateChange after date of interest.

Alex Kudryashev
  • 9,120
  • 3
  • 27
  • 36