I'm being asked to add a "deprecator" indicator to my code tables to indicate when a valid value is no longer used. This goes against my grain of 1) using indicators in my table (I would prefer to use a date), and 2) in our business, we need to keep history, so you can't identify which value was active at any given time unless you use a date (rather than this indicator).

We are focusing, primarily on developing operational data stores. Can you help build my case or offer alternatives (best practices)?


Chuck Kelley's Answer:From my perspective, I like dates. However, I have many times used the date and a "CurrentRow_YN" column to recognize the current value, which is the reverse of the "deprecator" indicator that you have been asked to include. Regardless, you will have to update the rows during the ETL process which is not so good on the performance side of things.

Steve Hoberman's Answer: I'm assuming that the indicator is a current record indicator, which singles out the most current occurrence. So if Bob moves five times for example, there will be five rows in the table, and only the most recent row will have its indicator value set to "Y." Dates are essential to have in your table, so that you can identify the time span when something is or was current. Because this is for an operational data store, which qualifies as a long-term information asset, you would want to have begin and end dates even though there may not be a requirement as yet to view information as a point in time. If dates are not stored, it would provide very limited value to know the past view of information. So definitely keep the dates ... I would think you would also want to keep the indicator though. The indicator provides a very fast and efficient way to extract the most current view of information. The indicator is much faster than a date. For instance, extracting where "Current record = 'Y'" is much faster than extracting where "expiration date = Sept 9, 3000" for example.

Clay Rehm's Answer:I agree with you. Add effective and expiration date fields but also add the indicator field to keep your management and users happy. There are times you need to modify best practices to satisfy the people who are using the data.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access