When the entire contents of reference data tables are displayed, e.g. in dropdowns or reports, the sort order in which the records appear needs to be attended to. There are a number of design considerations that should be evaluated in order to determine the best design solution for a particular situation.
In a dropdown list of the records in a reference data tables that has alphabetic codes, the obvious choice is to sort alphabetically on the code or the description. There may be unexpected results if mixed case is used inconsistently, which is one reason why we so often see upper case used in reference data. A decision has to be made as to whether to sort on the code or the description. While very often the description is the most natural choice, the code may sometimes need to be used, and developers should be careful to determine if this is the case.
It is sometimes necessary to use different sort orders based on frequency of selection of reference data values. For customers living the USA, it can be frustrating to have to scroll through a long country list in a dropdown to find the entry for “USA”. Instead, this entry can be placed at the top of the list so it is immediately obvious. It may be a good idea to repeat the entry in the location it would have appeared in within the entire list for individuals who for whatever reason started to scroll down without seeing the entry at the top of the list.
Sometimes there is a true priority in a reference data table, irrespective of whatever values the code and the descriptions. For instance, a tables of classes of expenses that is used for payment purposes may have an order of payment involved (e.g. interest payments are top priority, then personnel costs, and so on). In such situations a column in needed in the table to specify the processing order. This column may or may not apply to display order too.
From this brief discussion it can be seen that order in reference data tables, be it for purposes of display, processing, or other requirements, is a genuine design consideration that may require additional structure in reference data tables. Order, therefore, should not be neglected when designing reference data tables.