Beginner Developer Lesson: Maintaining the Link to Historic Data in a Lookup List
September 17, 2009 6 Comments
I was thinking about the basic concept of showing data in your applications that is both relevant and historic. This is a beginner lesson in software development, but it’s still an important design decision that needs to be looked at in the conception of a new feature or application.
Historic data needs to be maintained and it can take many forms. In this posting I want to briefly describe a method that can be used to preserve historic information that is found in lookup lists.
Information in a lookup list can be anything from a Category Item to an Employee Name or many other things. The problem is that some of these items can become obsolete or in the case of an employee name lookup list, the employee could leave the organization.
Typically when creating a lookup list in a database I’ll create a lookup table and have a bit (Boolean) field named archived, with a default value of 0(false), that will denote an item that is used or no longer used.
If there is any expectation or future requirement that a lookup item could become obsolete or deprecated then it’s important to include an easy way to remove the item from being used in the future, but you still have to allow the preservation of historic data. You do not want to delete this item from the list completely as you will lose any historic data attached to the item.
You just want to set a flag and archive/deprecate it. Being able to set this flag at the database table level should be enough so that you do not have to do any code modifications or other changes to support this in the future. It should be built from the get go, and you could always have the option to go a step further and build an interface and allow your users to do it from the application – but that is out of the scope of this article.
Once you have the infrastructure in place at the database table level you data could look something like this:
So, in the above screenshot you can see we have a lookup list for Category that has one archived item named Assembly.
Now, it makes sense that all reports and records that are using the CategoryID of 1 (Assembly) in their records to be able to maintain this, but new items should not be able to use the Assembly category.
Now that the groundwork has been laid, the following important guidelines should be followed:
1) All views using this lookup data in the result set should join to all of the data in the lookup table. You do not want to filter out Archived items because you want to show this data for historic reasons
2) Lists that are used within the application in order to select a new item from the above mentioned list (in this case Category) should only include items that are not Archived
3) When the user is using the application and selecting a historic record to view, you want to display the lookup item as part of the record weather it is an Archived item or not
Now there is one more piece that is often overlooked and it usually leads to the following question:
How do we display a historic item in a list, but not allow the user to select any other historic item other than the one that was originally there?
Easy, it’s all in the query. When writing the code to get the list data to populate the list (combo box, list box, etc) pass in the “selected id” (in this case the selected CategoryID of 1 – for Assembly). In this example, I’ll demonstrate how to do this in VB.NET code when using a dataset.
Public Shared Function GetCategoryList(Optional ByVal iAdditionalCategoryIDToInclude As Integer = 0) As DataTable Dim cm As New SqlClient.SqlCommand Dim dt As New DataTable cm.CommandType = CommandType.Text If iAdditionalCategoryIDToInclude = 0 Then cm.CommandText = "Select CategoryID, Category from lutblCategory Where Archived=0 Order By Category" Else cm.CommandText = "Select CategoryID, Category from lutblCategory Where Archived=0 UNION Select CategoryID, Category from lutblCategory where CategoryID=" & iAdditionalCategoryIDToInclude End If modData.FillDataTable(cm, dt, ConnectionStrings.WeldQA) Return dt End Function
This can be implemented in a few ways including using a stored procedure, LINQ, or ORM Mapping tools. I just wanted to show the code to demonstrate the type of change necessary in order to ensure that your lists will cleanly allow the preservation of historic data in the application.
It works by returning a sorted list of all non archived items in the case where we do not pass in a CategoryID to include. In the case where we do pass in the CategoryID to include we first select all unarchived items from the database table and then union that to also select the additional item from the database table regardless if it is archived or not. The union will also eliminate the possibility that the Category item will show up twice as unions by their nature ignore duplicate records.
For brevity, I won’t include the modData,FillDataTable code, but it’s basically a common routine we use to take a resultset from a command and fill a datatable.
If you wanted to populate a list you would make a call such as the following:
1) A fresh list where the user will select a Category for a new record
2) A record where the Category list item “Assembly” was already assigned before it became archived
This was a simple example of how to properly code your application and structure your database to effectively handle historical information used in list s across your applications.
Please let me know if you have any questions about the article or any suggestions on how I could improve this article.