Displaying table data Aug09 '06

You have a database table that contains data. Duh.

On the web page, you use an HTML table to display the data. Your users can now go to the web page with the HTML table, and view all of the data.

Simple, eh?

Not quite.

What happens when there’s lots of data (let’s say... more than 100 records), and your HTML table takes a long time to load? After all - the more data that gets entered into that database table - the longer it takes the web page to generate the HTML table.

This is a major crux for usability. Your users can’t be sitting there waiting endlessly for the page to load.

"No problem," you think. Just LIMIT the query on the HTML page to 100 rows (or something reasonable). Also, add an ORDER BY clause to the query, to sort the data by... most recently added, for example. This way, the web page always returns only 100 records, sorted by most recently added - no matter how much data is in the database.

The web page now loads fast, and your users are happy.

Finished, right?

Wrong.

The web page may load fast, but now it’s not showing all of the data. For example, if your database table has 120 records, and you limit your query to 100 records, there are 20 records not showing on the page.

What if the user needs to see those records?

The dilemma

Here’s where the dilemma begins. You have to allow for quick page loads, quick sorting - but at the same time, allow the user to see every piece of data, no matter how large the database table.

There are a number of ways to approach this problem - all of which allow your users to get at the data they need.

That is, after all, the most important concept - allowing users to view the information they need.

Step 1: Limit rows

I think it’s obvious that we have to limit the number of rows returned to the page. We really have no choice, especially for large database tables that have new records frequently added.

Before you know it, your database table will have hundreds, or even thousands of records, and displaying all that on a single HTML page is absurd. Imagine having to wait for 1,000 records to be retured as an HTML table, and then having to scroll that list.

Ouch.

Not to mention, if you use JavaScript to allow "sorting" by column header, there will be a great lag time, the more rows there are. JavaScript has to parse the entire table, when sorting, and rewrite the data. The more rows, the longer JavaScript takes to sort.

So limiting the HTML page to only 100 records (or something reasonable) makes sense. It keeps the page loading at a reasonable speed, and allows sorting to occur quickly.

Step 2: Options for narrowing data

Now that we’ve agreed that limiting the number of rows is essential, we can move onto our options for allowing users to get at the data they need.

There are three recognizable options to choose from:

• A "pagination" system
• Filters/Search
• Sorting

"Pagination" system

A "pagination" system is quite simple, in appearance.

Here’s how an example pagination system might look:

Showing rows: 1 - 200 of 1324
Page 1 of 7 • Next » • View page: 1,2,3,4,5,6,7

You’ll notice a lot of numbers here:

• The number of records shown on the page: 200
• The total number of records in the database: 1324
• The page number we’re currently on: 1
• The maximum page number: 7
• All of the page numbers, for quick access: 1,2,3,4,5,6,7

Positives

A pagination system is nice because it allows users to access all of the results. Their page still loads fast, too, because it’s still limited to a certain number of results per page. Sorting also works just as fast.

Negatives

The bad part about the pagination system is that a new query is resubmitted for every page you view, taxing the server for data from the same database table. It’s also somewhat hard to navigate - having to click page numbers, and not being able to see all of the data at once, for quick comparisons. "Jumping back and forth" from page to page can be a slow process, since the page has to reload every time.

Filters/Search

Filters and search fields are handy for "narrowing" down table data. The user can simply type in a value to search for, or select from a pre-defined set of values, hit "Filter," and watch as the table only displays records that match the criteria.

Here’s an example of how filters might look:

Filter By Name:

Positives

The user can choose their criteria, and only show rows based on that criteria. This allows users to "narrow in" on the data they’re after.

Negatives

The results are still limited to the maximum number of records displayed from the query. In other words, if you LIMIT your query to 100 records (for reasons discussed above), the user still only sees the first 100 results of their filter. If their filter returns 120 records, they only see 100. How do they "get to" the other 20?

Sorting

Sorting is the simple action of clicking a column header, and watching the columns sort in ascending (A-Z) or descending (Z-A) order.

Positives

Sorting allows users to display (on top) more relevant information.

Negatives

Still only sorts the results from that page - as opposed to all results in the database.

Solutions

There are a couple of solutions, based on the positive and negatives from each option above.

Always limit, Always paginate

We’ve already agreed that limiting the number of records for each page is a necessity.

Well... if we also "always paginate," then the user always has full access to all of the data, and their pages still load quick, since there are only so many records on each page.

The only problem then, is the negatives of pagination: resubmitting queries to the server; hard to navigate/compare.

Multiple Filters

This seems to be the best option, I think.

By allowing for multiple filters, you allow the user to narrow down the results as much as possible.

For example, let’s say the user wants to filter on this criteria:

Filter by Name:

, also Filter by Age:

So, instead of filtering only by one piece of criteria, the user can filter by as many as they want. This should help them get down to the data they need, without the concern of pagination issues.

Categories: HTML , SQL , Web Development

Add Feedback (view all)