asked
Best practice for Table Historys and pin pointing data at a point in time
Hi there, I,ve been doing history tables for some time now in databases but never put to much effort or thought into it. I wonder what is the best practice out there. my main goal is to record any changes to a record for a particular day. If more than one change happens in a day then then only one history record will exist. I need to record the date the record was changed also as when I retrieve data I need to pull the correct correct from history as it was at a particular time. So for example I have a customers table and what to pull out what their address was for a particular date. My Sprocs like get Cust details will take in an optional date and if no date is passed in then it returns the most recent record. So heres what I was looking for advice on... and anything else. Do I keep the history table in the same table and use a logical delete flag to hide the historial ones. I normally dont do this as some tables can change a lot and have lots of records. Do I use a seperate table that mirrors the main table. I usally do this Should I only put change records into the history table and not the current one. What is the most efficent want given a date to pull out the right record... get every record for a customer <= date passed in and then sort by most recent date and take the top. Thanks for all the help... regards M
first things first, why are you keeping the historical information? Once that is answered then the rest of your question can be answered. If it is for audit reasons then there will be a business case that describes what you have to keep, how it must be kept, for what duration and so on. If it is for accountability purposes (ie who changed what and when) then this will again bring its own requirements - do you want to know every change, what do you need to do with the info. Are you trying to rebuild a record after it is altered/deleted inappropriately? if so then you need to capture all of the information needed to accomplish that. Once you know what is needed then you can work on how to achieve it. There are methods including Triggers, using the Output clause of the Insert/Update/Delete command, Service Broker etc etc. You need to decide which is best for you considering the business of the system and the performance spec you are working with.
hi Father J... thanks for the reply. The main business reason is that I can retrive data at a particular point in time for say a customer... so if I was running a query to see who the residents where of a particular city on a particular date, then I could retrieve them. If a resident has only ever lived there and never changed cities then they would have no historial info so I would pull their current info. So the main objective, is that if a user runs a report against addresses in 2011 and they get a list of residents and over time residents move out of the area... then if the run the same report in 2014 for residents in an area in 2011... they get the same report that was generated at that time. I agree with your approaches and typically any historical tracking I,ve done was based around accountibility and audit, but for this purpose I need to be sure that I access the same data for a given date... ie what was the current data in the system for customer addresses at a point in time. regards -Doiremik
Question :1 Server : USING sql server 2008 R2 -> CAN some one help on this clarification. a) I have "prestaging" table as source table. b) I kept working table and History table as other set. c) The data from "prestaging" tables will move to both "working tables" and "History tables". d) The working table record have few validation is there after validated then all the successful record will move to the "TARGET" table. d) There is possibility of the old data can get amended from source system and then they will send the amended record in the "Prestaging tables " with the UNIQUE no column as primary key. d) Before inserting in to target table I need to compare the history table if the same record is appearing again based on the unique column then I need to Update "Modeflag" column as update record else the record will be send as "Inserted" d) There is possibilities of comparsion of history table will have more than 5 lakhs of records. d) Currently we are comparing with inner join with UNIQUE no as clusterered index column and then updating the mode-flag . we required best possibilities way to avoid performance issue to handle this situation . Kindly provide the best solution to compare the history record . Note :THE BOVE ALL LOGIC WRITTEN ON ONE STROED PROCEDURE AND THEN SQL JOB WILL EXECUTE ON EVERYDAY.
Using the example you have given of customers and addresses, I would incorporate this requirement into your design. Don't think of it as a customer having one address, and you want to keep history, think of it as a many-to-one relationship, a customer has many addresses, and it is the date that determines which is the 'right one' for the query. This also means storing all the addresses in one table, not the current one in one table and all history in another. Being consistent with the design like this means that the access path to the data is the same regardless of the query needing current or history. It also removes the overhead of having to manage the history (updating the current, moving the previous current to the history, etc). You can use a flag to indicate the current, or simply have a 'ToDate' with a null value ( a lot like handling a type 2 slowly changing dimension in data warehousing). You could then also have check constraints to make sure there is only one current, or no 'holes'.
2 People are following this question.
