Good decisions come from experience. Experience comes from making bad decisions.
― Mark Twain
Today, let’s look at one piece of experience and how we can turn that into good decisions when implementing UI logic. Please, all UI developers read this.
The bad decision
When UI developers display tabular data, it is very common for the table to offer sorting on each column. This is extremely useful, as it helps the user extract basic insight from the data by just performing a single click. Let’s look at an example where the choice of sorting default seemed to be correct at first, but was wrong later on:
Bing Webmaster tools for the jOOQ blog. When I reach the page traffic website to see the Bing traffic for the last month, I can see this:
The default table ordering is applied to one of the obvious columns: “Appeared in search”, descendingly. Personally, I might have preferred it to be applied to “Clicks from search” per default, but what’s important: The column is ordered descendingly. I really only care about our top 5 best blog posts. Not about the worst.
So, this is good. Let’s see what happens if I do click on “Clicks from search”, however:
I get an overview of our worst-performing blog posts for that week. Yes, there are some posts that don’t attract any audience for an entire month from Bing. Bummer. (Let’s blame it on Bing’s popularity, not on our blog’s). But that’s not what I cared about. I wanted to see the inverse: The top performing blog posts. In order to see those, I have to click again on the column, to invert the sort order.
The experience
I see this behaviour all the time. UI developers mindlessly defaulting to the technical natural sort order on UI table widgets. In many cases, as a user, this is a frustrating experience, because:
Heck, what did I do? Why is it displaying this data
Aaah, it is sorting ascendingly
Crap, I have to click again
The cognitive dissoncance between steps 1) and 2) shouldn’t be underestimated. Depending on the complexity of the task, or the data that is being displayed, a user might first be confused before they realise that the wanted behaviour is 2 clicks away, not 1. While it should be a technical detail that there are things like ascending and descending orderings, in UIs there is a third notion: That of natural ordering.
Why do developers get this wrong so often? Simple! Because there is also a technical natural ordering, and that’s almost always the ascending order. For instance, in Java, when you do this:
TreeSet<Integer> set = new TreeSet<>();
set.add(1);
set.add(12);
set.add(3);
System.out.println(set);
You’ll get the nicely sorted data as such:
[1, 3, 12]
The technical natural ordering depends on the “raw” data type only. In the case of Integer, this is simply the natural ascending integer number ordering.
The UI natural ordering, however, depends on the context of a data type. While a meaningless integer might still be sorted ascendingly, the previous count value (also an integer!) should be sorted descendingly by default.
The good decision
So, are there any rules with respect to the UI natural ordering? Intuition, yes! But also the following more concrete (and far from exhaustive) list of hints:
Data types and contexts in favour of ascending natural ordering
names: All sorts of names of things like people, cities, countries, etc. should be ordered ascendingly in their alphabetical (case-insensitive) order. That is how people skim phone books, that’s how they expect names to be ordered
phone numbers: (and other similar numbers) should be ordered like integers: ascendingly. But beware of their specific formatting. It is very likely that the special characters in US formats (like (555) 123-4567) shouldn’t matter when comparing numbers (e.g. with +1-800-1112222), or with numbers from other countries
row numbers: This is an obvious candidate for ascending ordering because the row number itself already yields an implicit order, by which it was calculated (see also our article on SQL ROW_NUMBER())
dates in the future: If you know your dates are in the future, then you should order them ascendingly, as users want to see the closest date first. Think of a calendar, for instance. Do you really want to display a date in the year 8375, just because you happen to celebrate your 6394th birthday? Probably not. But if in doubt, with dates, better sort them descendingly (see below), as you usually have most dates in the past and only few dates in the future.
aggregations: There are few aggregations that should be sorted ascendingly by default. One of them is SQL’s MIN() aggregation. If you’re really looking for the lowest value, the lowest value should be on top, followed by higher values. Other aggregations that are OK to default to ascending order are percentiles (e.g. the MEDIAN()), or standard deviations, or linear regression functions, because it is not clear whether the user cares about the highest or the lowest value. In this case, it is OK to simply default to the technical natural ordering. Most other aggregations, however, should be sorted descendingly (see below)
members of a period: A period is something like a year, month, week, day. Periods come with a finite, discrete number of members, such as day of year, week of year, month (for year), day of month (for month), weekday (for week), hour, minute, second (for day). The default ordering here is obvious: always ascendingly, in order of period traversal
money (price): No one wants to buy the most expensive flight! Obvious, right? But be careful. Prices are expressed in money, and money isn’t always best sorted ascendingly. If in doubt, order money owed to someone ascendingly, and money owned descendingly. What a difference a little "n" makes!
Data types and contexts in favour of descending natural ordering
dates: This is a tricky one, but there are few occasions where dates really should be sorted ascendingly, so default to sorting them descendingly, if most dates lie in he past. The reason for this rationale is the fact that users want to see the closest date first, e.g. the most recent date of a bank account transaction.
aggregations: When you run SQL COUNT(*) or SUM() or AVG() or MAX() aggregations, users will really care about the highest values only, as we saw in our Bing Webmaster tools example. Please do sort these aggregations descendingly by default!
changes: If the change between your current value and e.g. last week’s value is the thing of interest (e.g. stock market, or again Bing Webmaster tools), then both orderings are interesting. The biggest winners / losers are both useful pieces of information. However, let’s stay positive here and order stuff descendingly by default in order to display the biggest winners first. We don’t want to be negative by default. Whether the change in percent or the absolute change is of interest is another story and depends on the domain.
file sizes: Probably, the user is looking for the biggest files – e.g. to see what to delete to save most disk space. Order descendingly by default. If in doubt, think of sizes as the COUNT(*) value of any content. And that should clearly be ordered descendingly.
booleans: When ordering a column that contains true/false information (e.g. E-Mail does or does not have any attachments), then the true information is usually more interesting. Since true = 1 and false = 0, order these columns descendingly by default.
money (balance): Unlike prices (money owed to someone), balances (money owned) should be ordered descendingly. We want to know how many billions we have. No one cares about their worst assets.
Data types without ordering
There are some data types that simply shouldn’t be ordered. Don’t offer ordering by default on them, it might confuse the user and it might kill your server! These include:
URLs: In the case of Bing Webmaster tools, there is really no point in ordering URLs. I mean, the natural order would be http vs. https first, then the domain (but not from top level domain down to the irrelevant sharding identifier), then possibly the port (completely useless piece of information for the user), then the path (probably ordered by date in blogs, but pretty random otherwise). Ordering by URLs doesn’t add value, so don’t offer it. Caveat: If you display only a URL part (e.g. the domain name), ordering might make sense.
text: Now, plain text (e.g. E-Mail content) is really the very last thing you want to order. Most SQL databases don’t even allow for ordering CLOB content. This should be obvious, just don’t do it.
composite data: If data points are structured (like age and sex in one data point, in case the combination matters for your domain), they’re very hard to order correctly. Specifically, sex doesn’t have any non-technical order. If in doubt, better don’t offer ordering, or decompose the data point.
Data types where sorting challenges mean that tables are the wrong tool
Some data types are tricky to sort by default. Mostly, this is because we’re dealing with discrete or continuous values that go in both directions of a “zero” value. E.g. numbers, percentages, dates (where zero=today):
dates: As we’ve seen above, dates are a bit of a tricky data type to sort in tables, as the user experience depends on whether dates are mostly in the past (like bank account transactions) or mostly in the future (like appointments), or both (like calendar entries). A much better UI widget to display timelines that expand both into the past and the future are .. well .. timelines, which cannot be sorted by the user. They’re always ordered by date, displaying today’s date by default
percentages: If percentages are the most interesting data point in a data set (e.g. stock option changes), then chances are, that the value 0.00% is the center of your data, e.g. in a winners/losers display widget. While they’re the center of your data, they’re not the center of interest. The most interesting values will still be the top winners and the top losers. This is hard to display with sorting only. Filtering (or pagination) will need to apply in order to remove the stocks that are in the middle
(approximate) search results: You don’t see any means of ordering Google search results, right? That’s because Google searches are approximate, i.e. their results are already ordered in terms of relevance. You usually don’t want to offer your users to re-order these results (at least not on the relevance scale). One exception might be ordering of exact search results by date (or something else), but this is really hard to get right from a UX perspective, as you risk displaying lots of irrelevant results based on their freshness.
Situations where the above is not true
Now, the above are useful advice for making the right decision in the case of simple and homogeneous tables, like the one exposed in Bing’s Webmaster Tools (all columns are either unsortable (URL) or aggregations). If you display arbitrary data, then it might not be wise to apply these rules as it will confuse the user if one column defaults to descending ordering, and another defaults to ascending order. In that case, revert to sorting all columns ascendingly. The user will understand.
Conclusion
If you’re a UI developer, make natural ordering flags first class citizens of your software design. Pretty much every data type ships with an intuitive, and obvious value for default ordering, i.e. one of:
Ascending
Descending
No ordering
Every time you design a table, please do think of the above. It’s that little extra effort that will make your user interface much more meaningful. And, beware. This is really what you as a UI developer need to do. The backend developers operating on the database cannot specify this, because:
Databases contain raw, context-free data (e.g. of type NUMBER or VARCHAR)
UI ordering is not necessarily the same as SQL ordering
