Try it. And catch it, finally.

When developing Database centric Applications you regularly stumble upon the “actually stupid” trouble that the User naturally wants to sort Data Tables presented to him “by every column available”. And the sort order should of course be the “naturally expected one”. Actually not a big deal? Sure not, however some troubles will unavoidably come along your road if you don’t take care of all the typical involved scenarios in advance (meaning: when designing the application).

Basically these issues boil down to the fact that something in a table column is presented to the user that is somehow different to what is directly stored in the underlying database. Typically

• Some key value is stored in the database, but on the UI the key is replaced with some display string. The User wants to see the Column sorted by Display Strings not by the underlying Keys.
• Some default language value is stored in the database, but for display the string is presented in some other language selected by the User. The User wants to see the column sorted in the displayed language not in some other one.
• A numeric value is for some reason (eventually for no wise reason…) stored in a string typed column of the database. The User wants to see such columns sorted numerically (and not “alphabetically”, meaning here sorted by the Character Codes of the displayed Numbers).
• A numeric value is somehow a systematic part of strings stored in a column of the underlying database, like in a column called “Reference”, holding values like “Issue #13, Page 24″. The User wants to see the mentioned Reference showing up after “Issue #2, Page 30″ and before “Issue #13, Page 101″. Unfortunately, when sorting “alphabetically”, it will show up exactly the other way round: after “Issue #13, Page 101″ and before “Issue #2, Page 30″…
• A column displays a string concatenated of several (arbitrarily typed) columns, eventually mixed with some fixed strings, like in “Maier Alfred, Austria, born Feb. 19, 1958″. The User wants to see such a Mr. Maier sorted before this other Austrian Mr. Alfred Maier born in December 1981, “of course”. Will he? Well, it probably depends on how all this stuff is implemented…

In order to be able to sort all this stuff in a consistent, performing (database centric) way and in a way the user will naturally expect it to be, we will need an equally consistent and problem aware approach for

• Maintaining, loading and storing I18n aware display labels in the database.
• Taking care of numerical characters stored inside string typed database columns.
• Systematically generating concatenated Columns consisting of the contents of several database columns.
• Internally representing Data Tables aware of the Database Sort Column(s) for each Display Column.

Yep, I’d definitely like to spend some more thoughts on such a consistent approach and develop everything needed (and not yet available) in code.

(This post is part of my thoughts about A portfolio for Enterprise Web Applications)