Designing better data tables (2017) | Hacker News Reader

Designing better data tables (2017)

uxdesign.cc

241 pointsyread93 comments

Regarding table columns sorting - are there anyone else who thinks that clicking on the same column the third time should remove the sort? For example:

  • First Click: Sort ASC

  • Second Click: Sort DESC

  • Third Click: Remove Column Sort

This allows me to go back to the initial state where the tables have been loaded. Currently, most data tables don't support "resetting" of column sorting and are implemented in this manner:

  • First Click: Sort ASC

  • Second Click: Sort DESC

  • Third Click: Sort ASC

Rows will always have an order, even if that order is the order they are indexed in the database, which for the purposes of a table is an implicit column. So "remove column sort" means revert to the default sort. In other words, there is no such thing as no sort: rows will always be sorted, you just choose by which column and the direction. Implicit sorts aren't a great idea, I think.

I agree; in projects where I've used a lot of data tables, there is usually a column that makes sense as the default sort for the stakeholders, so I just make sure it is ordered by that at load and then it makes more sense to people to toggle ASC/DESC sorts.

Thanks - you certainly made a good point. But there's one problem with enforcement of explicit sort and this the problem I constantly have with my projects.

By default, most tables that I have are sorted by "created_datetime" but this is the column that won't be shown to the end-user since most of the time it is redundant information for them. If we're implementing explicit sort, I need to add an additional column which would be useless to them.

Having the ability to easily reset back to default sort makes everything so much cleaner as an end-user (for me). I won't have to experience another "How the heck do I go back".

Depending on the dataset structure (as seen by app/db developer) there may be in fact two sorts: one that reorders rows in a set* and one that reorders rows in a table view (M and V from MVC, respectively). Your “go back” thing is then a conflict between the two.

It may be seen as overengineering for an itunes user, but a full implementation should provide a way to do both sorts and to cancel view-sort if no longer needed.

Also, sorts may be not just column-based. Shop cart may be sorted by product group without having such column even in dataset, like rows.sort(“product.group.title”) or alike. In UI it can be done as right-clicking on a column/cell and sort->[natural, id, title, group->[natural, id, title, ...], ...] menu hierarchy.

* algorithms may depend on this ordering, so a user takes care to not change it accidentally

I'd agree, but only if you a) include the column you initially sort by[0], and b) have the UI show that the table is sorted by that column. This way, there's always a sorting marker that's only flipping and moving between columns as you click on them.

[0] - Even if it means including a DB id, though preferably, you'd resort the data by one of the columns intended for users.

Almost all tables I've worked with are sorted by the first column by default. So 'remove custom sort' simply means to go back to the sort on the first column. It isn't some nebulous implicit sorting from the database, it is going back to the state before you changed the sort.

That behaviour will be database-specific, most databases I've seen simply say that the order is undefined if there is no explicit "order by" clause.

Imagine a query like "select top 10 * from foo where bar = 1", when there is no index on the bar column. The database will need to scan through the foo table, the check the bar column for each item, then return the first 10 that it finds. So it could behave as you describe, just scanning from the top to the bottom in on-disk order, returning results in that order.

However, the database may maintain statistics of how often bar is set to 1, and if it is only sparsely set, it could recognise that a single-threaded top-to-bottom scan would be quite slow. In this case, it could be much faster to break the table up, one chunk per thread, scan each chunk, then return results as soon as enough are found. The final order of results (if there is no order by clause) could then depend on the order that the threads run in, but you'd only see that if there was a lot of data in the table to start with.

https://docs.microsoft.com/en-us/sql/t-sql/queries/top-trans...

This is why it is very common to see suggestions to always specify an explicit order when implementing pagination, as the order of results could change from one page to another if the sort order is left undefined, leading to missing or duplicated results from a user's perspective.

No, I think it should toggle between ASC and DESC, as it does.

Now, one thing that is very important to me: The sort should be stable. So, for example, I should be able to sort by successively by track number, disk number, album, and artist, to get a view that is sorted by artist, album, disk, and track.

Unfortunately, some views (eg iTunes list view) don't obey that.

This seems orthogonal? It certainly is compatible with what the post you are replying to is suggesting.

After thinking this through for a moment, I think the point here is that there are two fundamentally different ways of thinking about sorting. To the original poster, it's a way of viewing the data. It doesn't change the original ordering of observations in the table, and it makes sense then to be able to turn it off... To the second comment, it is a way of actually changing the representation of the data. That's why you would expect it to be stable and dependent on the order that the sorting was done.

Personally, I like the idea of it being a way of viewing the data rather than a change to the data. It would even make sense to be able to sort by column A, and then click to sort column B to breaks ties, and column C to further break ties ect. (though there would need to be a visual describing the precedence of the sorts to the user). Then, you can undone any of these by clicking a column twice more.

Yes, it's somewhat orthogonal. Having a third click meaning "ignore that I clicked here" is certainly fine. But please be stable :-)

I think it depends. I like they way it work in Spotify, where the third click on a heading returns to default sort. Most of the time I want it sorted by default order, but when looking for a song I change to order by name and after finding the song I want an easy way to get the playlist back to default sort

If there's a "hidden" intrinsic ordering in addition to the by-column orderings, then the third click should indeed reset the data to that intrinsic ordering. A music player playlist is a great example; it's a list, meaning each element tuple is augmented with a unique integer, its index on the list. The user can reorder the elements on the list arbitrarily, and the list must be revertible to the custom order after sorting by any column.

Couldn't that be made explicit by having a list index in the first column?

It could, and I thought of mentioning it, but I haven't seen it in the wild anywhere. Probably it would just add clutter and look confusing when the list is sorted by something else. The triple-click UI seems to be a fairly established way to do it.

It's interesting, I just checked - in iTunes, if you switch a playlist to View As -> Song, then it does display a first column with numbers 1..N. Now, if you sort after some other column (say, Artist), the first column does not get permuted accordingly, but stays 1..N. However, if you click the first column, it brings the playlist in the original order, and if you click again, it brings it in reverse order (while still displaying 1..N). A bit weird, but effective.

But then, sort is not stable. For example, if I sort by song name, and then by genre, the songs within a genre are not sorted by song name, but (for some unfathomable reason) by artist. Very annoying. Bad iTunes, bad. (This is on macOS Mojave, haven't dared the jump to Catalina yet.)

Just checked and Spotify sorting isn't stable, either. Presumably this is because, as with most apps based on a database of some sort, there may not be a "materialized" in-memory data model that one would sort, but the column to be sorted by is passed to the backing database as a part of a query. The app could, I guess, remember the last few sort-by columns and craft the query accordingly.

However, insofar as most people desire the ability to sort by multiple columns, they'd likely also want a more intuitive UI than clicking the columns in the reverse order of priority. A few years ago I participated in developing a datagrid component where you could add columns to the sort order by shift-clicking.

Very plausible. I'd certainly appreciate the workaround you suggest; and I also agree that the reverse order thing is not intuitive at all. "Add behind" by shift clicking sounds good, but hard to make discoverable...

It depends on the context.

For a music player, "index occurrence in release album" is thoroughly useless data in every situation other than playing albums in order. So it's more elegant to hide that information and specifically design the sorting features around the use case.

"Removing the sort" doesn't make much sense. If the column is sorted and you remove the sort, then it will remain sorted.

I guess pirsquare meant going back to default sort by "Removing the sort", which is a very reasonable thing which I personally miss in some software.

DBeaver have this behavior, and I find it very useful

I like your thought but personally I would prefer resetting the state with a right mousebutton or third mousebutton click.

Right click should bring a menu with options, not change state.

3rd mouse should be a shortcut to something that can already be done

If we’re proposing a solution, why not click to toggle ASC/DESC then have the ability to “remove sort” in the right-click context menu?

There's no right / third mouse button for mobile users though.

(long press, though that gesture can get overloaded)

Agree. We have a data config later in our app that stores a manual sorting of attributes. This config is client specific and managed by our data engineers. This is the default sort and is available via this tri-state behavior.

It's how e.g. Wikimedia/wikipedia tables behave.

<BEGIN PSA> Please, please, please, do not use hover controls. They are useless for touch users as well as screen readers, and the discoverability is poor.

Modals are also really really hard to make accessible, and are often confusing. If you MUST use modals, please use a well-vetted library instead of rolling your own.

There's a lot of things that look really cool but make life difficult for people that aren't you. We appreciate your consideration when you build your UX. <END OF PSA>

I would add: even if you design only for desktops and discoverability is not an issue, PLEASE do not use slow animations for hover controls to appear.

At least for screen readers, they can work really well as long as you avoid display or visibility properties to hide them. Just position absolute and left: -999em will hide them visually but still let the screen reader 'see' them.

Of course, this will still not really help with touch devices.

Is there a reliable way to detect touch screens and screen readers?

This way, you can enable hover controls only if the user is not on a touch screen or a screen reader.

It's not really a clear distinction, though. I have a laptop that has a touchscreen, but also a regular touchpad and sometimes I use an actual mouse with it. Sometimes I touch the screen if it's easier even if I have a mouse connected. I also sometimes remote into it. Then there's my tablet: I can use a bluetooth mouse with that if I want. Or an external touchpad.

I imagine I'd err on the safe side. If the screen has touch functionality (includes laptops with touchscreen), then don't do actions on hover. Is there a way to reliably detect the existence of touch functionality through the browser?

Visual Studio Online use the hover pattern for some buttons. I hate it because it’s make hard to remember what the options are and hard to click on them. Design over function are sadly the norm nowadays.

At least in case of tablets with pens/styluses (like the Surface line or Dell Latitudes), the device can recognize when you're hovering over something with a pen. It meshes nicely with the mouse-oriented design. I wish there was a way for touchscreens to detect hovering fingers, then on-hover controls could be useful on phones and penless tablets.

I think it must be 20 years ago that browser makers talked about implementing features like table sorting into the browser. I am still waiting.

I think leaving this kind of functionality to web developers, leaving everyone to figure it out for themselves and also make it work on several platforms and browsers is just silly. I don't know why we do it this way.

HTML columns have no type, so, for example, sorting a date column would not give the expected order. HTML table cells admit arbitrary HTML, so there's no easy comparability, there. Sort orders even for understood data vary greatly; sometimes a lexicographical order is sufficient, sometimes you want special handling of numbers within text, sometimes you want a case-insensitive order. Locale of the data and the user can affect sorting greatly. And that's without even getting into editing, grouping and filtering.

Basically, it would be hugely complicated, require significant additions to the HTML of tables and would probably be no more successful than the built-in date-pickers of browsers, which always get replaced to get a nice look or behavior.

A more than decade ago I was tasked to implement a table view in web application (it was time when jQuery was not yet popular, all the hype were DHTML and AJAX). The task was: "do something resembling windows file explorer".

When I gave the first version to our testers, each and every one of them came with non-overlapping set of features. Some of these features are listed in this article and even more.

If you look into details, you will be amazed how many features there are in such common and simply-looking interface element. It took me (and the testing team) more than a couple of weeks to "kind-a finish" this work.

> "If you look into details, you will be amazed how many features there are in such common and simply-looking interface element."

Yes! Reminds me of this recent tweet by Ryan Florence (a renowned JS lib author) on spending 3 hours w/ a friend creating a chart -- ie, a spec -- for "just" a dropdown button:

https://twitter.com/ryanflorence/status/1189728090575921152?...

Am I the only one who read the article and hoping to see an implementation =)? Any suggestions on any good open source data tables components you guys use?

The Adamn Lynch article that was on HN a few months ago was great, with code examples - https://adamlynch.com/flexible-data-tables-with-css-grid

I was waiting for the "and here is where you can get it (half expecting to have to sign up)" but nothing

The appropriately named DataTables[1] has worked well for me recently.

- It’s focused entirely on tables (as opposed to being a more general widget framework).

- I believe it covers most of the functionality discussed in the article.

- It has a mobile vertical layout.

[1] https://datatables.net/

As all the features were being listed, I scrolled with the exact same hope :-)

I have been using Sencha/ExtJS' grid for that purpose, which is very powerful and versatile, and recently I've been contemplating ag-grid, which seems very nice also.

We use Ag-grid at my company. It's probably the most fully-featured data grid I've ever worked with on the Web, but it definitely has some pain points. I think it basically supports almost everything mentioned in this article out of the box (except for modals etc., which shouldn't be part of the grid itself anyway...). They even recently added support for charting selected ranges of data from the table.

Be forewarned though: if you're trying to use this from React with dynamic data that can be modified on the client, you're probably not going to have fun. Ag-grid is a vanilla JS library with a very thin React wrapper written around it, so even though on the surface it looks like it fits into the React model, most complex things you'll want to do end up meaning you have to use the imperative table API. I can't speak to how it fits in with other frameworks out there, but I would imagine it's going to be a similar situation.

That being said, if you're just going to drop some data into the table that's read-only, i.e. just for analysis etc., I think it's pretty solid and hard to find something with anywhere near a comparable level of features.

Oh thank you for this explanation and warning, I am indeed contemplating using the React version of Ag-Grid (for the experimental rewrite of a relatively complex internal management app, written entirely in ExtJS 4, and I was wondering how well it would integrate into it (especially given that it has flavours for all the major frameworks, React being only one of them).

Ag-grid looks very cool but it's almost a megabyte of minified javascript. Is there a way to cut out unneeded features?

Shameless plug (I no longer work at Vaadin, but was part of the original dev team of vaadin-grid):

https://vaadin.com/components/vaadin-grid

I created one a while ago called Dynatable. It's currently a jQuery plugin, but we already have a vanilla JS rewrite mostly done, so that'll be coming soon.

https://www.dynatable.com/

Same here, I haven't seen anything like this for React.js

I've found AntD's table to be able to do all these things.

There are some gotchas with column widths when using horizontal scrolling with fixed header and fixed column(s), but it works.

Using CxJS grid and other widgets it's relatively easy to support all of the scenarios mentioned in this amazing article. Please note that CxJS is not open-source.

https://docs.cxjs.io/widgets/grids

https://gallery.cxjs.io/material/grid/basic

The article is an excellent example of how something that seems easy and common-place ("show tabular data in a table") requires a lot of work to make it really good.

I'd say this is common to a lot of our work as developers. On the face of it a task seems easy - but it typically involves adding lot of fine detail to make it excellent.

> The article is an excellent example of how something that seems easy and common-place ("show tabular data in a table") requires a lot of work to make it really good.

One thing they don't mention, is exporting. Anytime you're dealing with tabular data, you really need an "export to excel" button. You'll never be able to do all the slicing, filtering, and sorting that excel can do. By all means, add it into your app and hopefully over time your users won't need it, but at the start, it's vital.

I wish apps allowing editing tables were oriented around a workflow where you export to Excel, edit it there, and reimport it back. Let's be honest, your web app's table is never going to be anywhere near as ergonomic and functional as Excel, so why not just leverage it?

Looking from my experience I'd say: it typically involves adding lot of fine detail to make it at least bearable

LOL @ "Multi-modal". The whole point of "modal" is that it introduces a new [mode](https://en.wikipedia.org/wiki/Mode_(user_interface)) in which the interactions that were possible a moment ago cease to be possible, until you've dealt with the modal and leave that mode, returning to your prior mode. "Multi-modal" is just "windows".

I agree until the in-view presentation. When there are few rows, the inside modal or sliding panel will feel awkward. So basically https://datatables.net/ This jquery tool meets most of the requirements.

I recently had to implement a js/jquery web application & datatables is golden : so many features, so many possibilities. Used for so long by so many => so few bugs left.

I keep this resource bookmarked. It lists a number of the most popular JS/HTML table/grid components many of which implement the UX guidelines in this article.

https://jspreadsheets.com

I recently stumbled upon jExcel. I use it as an editable datatable that allows copy pasting to and from excel. That's what all my users end up doing anyway with any non trivial dataset, so might as wel make it look and behave as much as excel as possible

This is a pretty good article that shows some good design ideas for data tables. That said, it is a puff piece for hiring at a company and not a recommendation for something to use.

There is exactly zero open source and/or free component data tables for React that do everything in this article.

Not only that, but many are so full of bugs or so difficult to implement that they might as well not exist at all.

Serious opportunity to create a top leading project that would have a huge impact on the usability of the web if everyone adopted it.

Edit: Yes, I'd contribute to working on a project like this under a permissive MIT license, but I don't want to do it alone.

> There is exactly zero open source and/or free component data tables for React that do everything in this article.

Pretty sad no? Spreadsheets have been around for 30 years and there is still no excellent solution for web apps?

I'd say that Airtable is actually doing a pretty great job. Unfortunately their grid implementation isn't free. =)

That's because everyone has a different set of requirements.

Throughout my web-dev career, I did table or detailed-list views many times, but each time with different requirements. Aside from sheer size of code to implement all the features, you will have contradicting requirements and there is no one-size-fits-all solution.

Top concerns are: 1. is your data homogeneous (all columns in a row are presented equally) 2. can this data change? 3. is there a lot of data? (so you have to implement virtual list) 4. is data dynamically loaded? 5. do you need actions on data, and how to display them (inline / overlay / menu?) 6. some features are really simple on surface (resizing, drag-drop reorder of rows, auto-sizing on dbl-click is a hell of itself) but are pain in the ass to implement. 7. is filtering/sorting done on a client or you have a server to help you? 8. and many many more...

There are multiple implementations, but you have to investigate which one fits you need.

I've been using react-table [1] for a while and it works really well, supporting most of the UX patterns described here.

For any use cases not supported it is often easy enough to extend, and the library is MIT licensed so you could always add more features to it.

[1] https://github.com/tannerlinsley/react-table

Very little love for typescript. v7 is very different from v6. It requires you to implement pretty much everything yourself.

I highly recommend ag-grid, it works well with React. The free version is MIT licensed, while the paid enterprise features are open source on Github, but not free to use.

https://www.ag-grid.com/

Wow, they managed to fit a surprising set of features in a less than MB of javascript. As someone who implemented different grids multiple times I'd say it is a nice job!

Great recommendation. My only issue with this is that they don't have a super solid solution for Material Design based grids. Now I know that is more of a problem with MD (and they note that on their theme page), but omg... once again... it is like we are back at the drawing board again.

Good searching and filtering should be on the first place. May be you don't need long tabular presentation at all because of this.

Sometimes it is better to take advantage of web browser builtin search function (Ctrl+F). Unfortunately some overly-eager implementations break it when supplanting with their own...

Most real data is too big to fetch in one piece by a client. Internal admin interfaces come to mind. On each job every time support people ask to include monstrous sorting behavior over multiple columns to be able to find group in question. However simple search would be enough.

Yeah. There are different sizes of data, that's why there is no good control for everyone.

Size of data can be:

1. Small, so you can display it all at once and the user will have luxury to ctrl+f what he/she wants;

2. Big to display, but small enough to push to client: then you will need to create pagination or virtualized view. Ctrl+F will not work (at least without some tricks), but you can easily filter/search it yourself in JS;

3. Too big to transfer: the only way to search/filter is on the server and separate controls (of course Ctrl+F won't work).

I'm worried at what size 1 becomes 2. I've seen web tables that choke on 100 rows. I feel that if a table can't handle 10-100x that without destroying site's performance, it's done wrong.

I also wonder whether we shouldn't have new <table> tag, meant specifically for data tables. Say, <datatable>. It would have all sorts of sorting and filtering and pivot tables built-in, and it could exploit native code to ensure high performance. Case 3 could be handled by a well-defined API that translates table operations to JS calls, in case a full dataset can't be sent to the browser.

It will be a heck of API to agree on. And then to implement correctly.

As another poster linked ag-grid this is how API surface should look like: https://www.ag-grid.com/javascript-grid-server-side-model/

This. I've been preaching this for years now.

Paging is an anti pattern. You need good search and filters, not paging. With paging all you do is overload the user with information, forcing them to scan mountains of irrelevant data over and over again until they find what they were looking for.

If you need paging, your UX design is wrong.

That's a really good heuristic, but unfortunately, lots of users are still stuck in the "I need an exhaustive list of all of our data, ever"-mindset because that's what their manager mandates. Then they print it and put it in a drawer. shrug

This can be overkill for searching something like a product catalog, though I'd love to have something this detailed on Amazon. For exploring large data sets with hundreds of data points, it's required, and paying customers will demand it because they know how much it helps them do their work without having to request and wait for new features every time a new type of use case comes up.

A lot of these features are available in the Sencha ExtJs Grid (both for desktop apps and mobile).

Sencha ExtJs is a paid front-end JS framework, which I find extremely comprehensive. I have worked on this framework quite extensively and find these features missing even in most, if not all, modern JS frameworks.

Examples:

For Desktop: https://examples.sencha.com/extjs/7.0.0/examples/kitchensink...

For Mobile: https://examples.sencha.com/extjs/7.0.0/examples/kitchensink...

What's wrong with the way this page loads images? On the first try I get nothing but grey rectangles, on refresh I get images, on another refresh I get blurred image placeholders.

Hmm:

    Loading failed for the <script> with source “https://cdn.optimizely.com/js/16180790160.js”. design-better-data-tables-4ecc99d23356:8:1
    -+++++=        .+++++=
    .+@@@@@+       #@@@@*:
      .@@@@@=     *@@@@@  
       @+@@@@-   =#@@@@@  
       @ +@@@@: :% @@@@@  
       @  *@@@@-%: @@@@@  
       @   *@@@@-  @@@@@  
      -@-   #@@+  :@@@@@: 
    -#@@@#-  ##  =@@@@@@@=
    .......      .........
    main.fe894587.chunk.js:1:322280
    We're hiring! https://medium.com/jobs-at-medium/work-at-medium-959d1a85284e main.fe894587.chunk.js:1:322536
    onmozfullscreenchange is deprecated. media.html:11:9799
    onmozfullscreenerror is deprecated. media.html:11:9799
    Loading failed for the <script> with source “https://d1z2jf7jlzjs58.cloudfront.net/keys/medium.com/p.js”. design-better-data-tables-4ecc99d23356:1:1
    Loading failed for the <script> with source “https://cdn.branch.io/branch-latest.min.js”. design-better-data-tables-4ecc99d23356:1:1
    Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://collector-medium.lightstep.com/api/v0/reports. (Reason: CORS request did not succeed).
    
    This site appears to use a scroll-linked positioning effect. This may not work well with asynchronous panning; see https://developer.mozilla.org/docs/Mozilla/Performance/ScrollLinkedEffects for further details and to join the discussion on     related tools and features! design-better-data-tables-4ecc99d23356
    Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://collector-medium.lightstep.com/api/v0/reports. (Reason: CORS request did not succeed).
    Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://collector-medium.lightstep.com/api/v0/reports. (Reason: CORS request did not succeed).

Turn off JS and you will get proper images.

Just yesterday I had to laugh in despair at how bad Google Cloud's UI is, including scrolling the headers instead of pinning to the top. What's much worse though is the perf, load times for simple pages are 10 seconds, my machine is pegged at 100% cpu, slogging through logs viewer like molasses. I just find it hard to believe it'd be so bad, on such a crucial product. This coming from a company that has dev rels advocating better perf (as they should!), but not applying it to one of their most important products.

Is there a framework or package, that out of the box includes the majority of these table features?

DataTables does most of it minus the modals and pop-overs

There was, a simple, elegant, easy to use and modular framework called JQuery, and it had plenty of plugins for sortable tables.

Unfortunately, we're all too clever for that nowadays, so you'll probably have to download and compile half a terabyte of Rust from a proprietary package manager just to get something that only works in the nightly build of Chrome.

Hey, this is kind of unrelated, but the other day you mentioned "Blueprints for a Sparkling Tomorrow". May I ask what brought that to your mind, since the book was published years ago?

Funny, I've been referencing this article a lot lately, because I'm designing a fairly complex table in an app I'm building.

I wish there were more articles like this. The illustrations are great.

It's ok and good until "Quick view". Click on the left of the screen, sheet slides out from the right, and you have to move the mouse all the way to the right to close it.

To play with data: check Excel filters. It is really hard to beat.

Am I the only person in the world who just can't stand fixed headers, be it a table header or a website top menu?

Whats the best Js library for implementing functionalities listed in the article?

Reply on news.ycombinator.com