How to efficiently manage big size tables?

Question

Introduction

In an administration web application, I have to show some big tables having many columns. It doesn't have to work on anything but desktop (or maybe tablet) with a screen of at least 1024x768 pixels.

There's a lot of information to be shown, beauty doesn't matter much but usability does. IMHO vertical scrolling is too bad and so is fixing the table width to e.g. 1000px as some users may have more than twice as much.

Some columns contain nothing but a checkbox and there's no point to make them wider. Others contain some text whose length has no (usable) upper bound, like e.g., "surname" or "email". Therefore, I made some columns fixed width and others growable.

Showing everything is impossible, reducing width hard: There are no big numbers, so rounding can't help. I'm considering a multirow layout, but only as an option, not the base layout. Table transposition doesn't feel right with my data.

The problem

With the minimum screen width, some fields have to be truncated, but usually you can see everything important. I did this by assigning a minimum width to every column and specifying what columns should grow (a column containing a checkbox or a date should not).

With a bigger screen, the excess width gets evenly split between all growing columns. This works, but there's a requirement to be able to adjust column width manually. Obviously, such a change should be persistent. This is easy, but what should happen when the user resizes their window?

As an extreme example, imagine a growing column with minimum width 100px. This width would be user for a 1000px window. Imagine, there are five growable columns and the window is 2000px. The column gets one fifth of the excess width, so it's shown 100px + 1000px/5 = 300px wide.

Now the user makes it smaller, let's say 160px. To fit it to my simple formula, I'd have to decrease my minimum width by 140px, so it became -40px. This is obviously wrong.

I could stop using the formula whenever the user resizes a column. But then I don't know what to do when the window size changes. I could find a better formula, but first I'd like to know what the user expects in such a case.

Answer 1

It is hard to imagine all this without seeing the actual UI. But I have a slight understanding of the problem you are having to deal with. It is a bit like Wordpress control panel or phpmyadmin if you like. A lot of columns and no readable data.

I can suggest you leave the sidebar alone. I can imagine you are using the first column for an identifier (otherwise how would you know what is what). So what you can do with you table is to fix the first column and make the rest of columns to be a separate table that has a scrollable overload on X axis. In this case you can keep the focus on the line you are interested in and allow yourself some room with the column width.

Answer 2

This is an usual condition in business applications.
Firstly, about the screen resizing thing, I wouldn't distribute free space but add it all to the right. This way, your user can enlarge the columns where she needs more width - instead of having to nudge each and every one to collect a few pixels width.
Then consider a few design suggestions:

font

If possible use a condensed font, it will allow more characters per width inch.

headers

Some columns, like those that only contain a checkmark, are wider than needed because of the length of the header text.
You can use a smaller font for headers. Much smaller. People don't read headers too much, maybe once; after they "recognize" them because of their shape and the column position.
The headers line has to be multiline, tall, even when the data lines are not. And you can set a smaller line height there, like 80% or 90%, to pack the lines.
It you dare, those columns sporting only a checkmark can have their header displayed vertically, as rotated text.
A nice and easy feature, to compensate for the small or rotated font, you can display the header text on hover.

lines

Between any two columns you need two padding widths and the line itself.
If, instead of dividing with lines you use alternate background colors (like a light gray and an even lighter one) you can steal the vertical division line width and one padding quantum, while having the column boundaries cleanly visible.
If you planned to use alternate colors in rows, you can instead use lines (another light gray, or maybe white).

transpose

Not the whole table, but on a per-line basis.
For example clicking or hovering the first column, pop a DIV with only the pointed-to row data, in a two-column table displaying the column header and its value in full.
This is easily doable with simple JS, and should reveal the truncated text of the lengthy columns.
It allows you to aggressively narrow the text columns, knowing that the user will be able to see the full text whenever they want to.
Think how to make this feature discoverable if activated by click.
If activated by hover, allow the pointer to stay parked for a fraction of a second (like 200ms) before popping the data to avoid the annoying effect of instant popups whenever the user moves the pointer over the data.

column order

It is possible that being able to reorder the columns (using drag&drop) could help some users to set together the columns they are more interested in, letting the others to be clipped.