DB Design: Sort Order for Lookup Tables

Question

I have an application where the database back-end has around 15 lookup tables. For instance there is a table for Counties like this:

CountyID(PK)    County
49001    Beaver 
49005    Cache 
49007    Carbon 
49009    Daggett 
49011    Davis 
49015    Emery 
49029    Morgan 
49031    Piute 
49033    Rich 
49035    Salt Lake 
49037    San Juan 
49041    Sevier 
49043    Summit 
49045    Tooele 
49049    Utah 
49051    Wasatch 
49057    Weber

The UI for this app has a number of combo boxes in various places for these lookup tables, and my client has asked that the boxes list in this case:

CountyID(PK)    County
49035    Salt Lake
49049    Utah
49011    Davis
49057    Weber
49045    Tooele  
'The Rest Alphabetically

The best plan I have for accomplishing this is to add a column to each lookup table for SortOrder(numeric). I had a colleague tell me he thought that would cause the tables to violate 3rd-Normal-Form, but I think the sort order still depends on the key and only the key (even though the rest of the list is alphabetical).

Is adding the SortOrder column the best way to do this, or is there a better way I am just not seeing?

Answer 1

Yes I agree a sort order column is the best solution when the requirements call for a custom sort order like the one you cite. I wouldn't go with a numeric column however. If the data is alphanumeric, the sort order should be alphanumeric. That way you can seed the value with whatever is in the county field.

If you use a numeric field you'll have to resequence the entire table (potentially) whenever you add a new entry. So:

Columns: ID, County, SortOrder

Seed:

UPADTE County SET SortOrder = CONCAT('M-', County)

and for the special cases:

UPDATE County
SET SortOrder = CONCAT('E-' . County)
WHERE County IN ('Salt Lake', 'Utah', 'Davis', 'Weber', 'Tooele')

Arguably you may want to put another marker column in to indicate those entries are special.

Answer 2

I agree with @cletus that a sort order column is a good way to go and it does not violate 3NF (because, as you said, the sort order column entries are functionally dependent on the candidate keys of the table).

I'm not sure I agree that alphanumeric is better than numeric. In the specific case of counties, there are seldom new ones created. But there is no requirement that the numbers assigned are sequential; you can allocate them with numbers that are a multiple of a hundred, for example, leaving ample room for insertions.

Answer 3

I went with numeric and large multiples.

Even with the CONCAT('E-'.. example, I don't get the required sort order. That would give me Davis, SL, Tooele... and Salt Lake needs to be first.

I ended up using multiples of 10 and assigned the non-special-sort entries a value like 10000. That way the view for each lookup can have

ORDER BY SortOrder ASC, OtherField ASC

Another programmer suggested using DECODE in Oracle, or CASE statements in SQL Server, but this is a more general solution. YMMV.