Beginner Developer Lesson: Maintaining the Link to Historic Data in a Lookup List

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.

image

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:

image

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

lstCategories.DataSource=GetCategoryList

2)      A record where the Category list item “Assembly” was already assigned before it became archived

lstCategories.DataSource=GetCategoryList(1)

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.

 

Dan

 

Advertisements

About dandouglas
Dan Douglas is based in Toronto, Ontario, Canada and is a professional independent Software Consultant and an experienced and proven subject matter expert, decision maker, and leader in the area of Software Development and Architecture. His professional experience represents over 15 years of architecting and developing highly successful large scale solutions. Dan also believes that properly empowering teams with trust and responsibility yields the greatest results. | For inquiries about Dan's software consulting practice, please see the contact page.  Dan has also built up a network of highly successful and professional Software Developers and Architects who are highly respected and can be called upon and used in conjunction with his own consulting practice to handle the largest of consulting projects.

6 Responses to Beginner Developer Lesson: Maintaining the Link to Historic Data in a Lookup List

  1. This is because the shaver is not quite as sophisticated, since the skin of the
    leg is much tougher, and the leg itself has fewer
    curves than the face. Braun series 7 optimizes the foil type shaving system by using the Opti
    – Foil integration. As you might have guessed
    the Braun Series 7 790cc Shaver is quite strong in both of
    these categories.

  2. Alisha says:

    – Rodeo: This is a famous restaurant in Delhi that shall
    be described as a Tex Mex place. 35% of US Web-enabled mobile phone
    owners said they had participated in some form of mobile shopping in 2010 according to a study by E-marketer.
    There is the Neapolitan pizza usually called the pizza Napolitano.

  3. However, the price is significantly more than the cheaper Chinese models.
    There are gas powered, electric, and corded chainsaws.
    In order to keep your chainsaw in good working condition, regular maintenance is necessary and this includes
    the regular use of chainsaw sharpeners.

  4. As you progress with your training the adjustable dumbbell sets will give you more flexibility because you
    can keep adding weight as you need it. These handy and space saving dumbbells are positively received by fitness
    enthusiasts not only because of its amazing features
    but the great result as well they get from using
    it. The frequency and speed at which you need to change the weight settings of your adjustable dumbbells mostly depends upon your fitness goals.

  5. Thanks for sharing your thoughts about league of legend riot points.
    Regards

  6. You actually make it seem so easy with your presentation but I find
    this topic to be actually something which I think I would never understand.
    It seems too complex and extremely broad for
    me. I am looking forward for your next post, I’ll try to get the hang of it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s