The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Inherent Problems with Pagination

Assume I have 100 records in my database, and I am allowing an user to view 10 of them at a time via some view.

Now assume User A is viewing the first page with 10 records.

At this point some User B deletes 2 records (say record 12, 13).

When User A request Page 2 showing records 11-20, he is actually going to miss 2 records because the new query will just get rows 11-20 and because of the shuffling, some records were now moved to Page 1 which the User has finished viewing.

Is this acceptable? How do most systems implement Pagination to avoid this issue?
Rekkai Send private email
Friday, August 03, 2007
 
 
I'd have to guess most of them ignore it.

In one application we kept the key of the last record viewed as priorMax and retrieved the next page with key > priorMax. Could only scroll forward, though.
Stan James
Friday, August 03, 2007
 
 
I think you would ignore this...

Or do you want to show the deleted rows? how would you do it?
Or do you want to show 8 rows in a page where 2 were deleted? how would you do it?

To show the deleted rows, you would have to store the complete results of the query in a "Session Variable" ....

It's too complicated ... and not worth it...

How often do people delete rows in your db? I would only think about this if it happens a LOT.
Totally Agreeing
Friday, August 03, 2007
 
 
This is also a UI problem even with the same user - look at Amazon wish lists.  When you delete stuff and go to the next page you lose items which rise up to fill in the gaps and have to go back.
Cade Roux Send private email
Friday, August 03, 2007
 
 
I hate the problem of the double query per page: one for pagination and one for results.

The shift problem could be worded like this: A user cannot access the next X results, if X results *already seen* have been deleted by someone else.

It is not an inherent problem though, but a direct consequence of a wrong pagination: The next page of page P is a slice of N records starting from the (P * N)-th record. The correct pagination is: The next page of page P is a slice of N records following the last record shown on page P.

Except for the shift problem, both methods give the same results, and the former is much easier to implement in SQL, using the LIMIT clause. An implementation of the latter method could use the pagination query, replacing "SELECT count(*)" with "SELECT id". Then, given the last id shown on page P, you could search it into the results and get the next slice of N ids. Then the results query could be "SELECT ... WHERE id IN (...)"
Andrea Ercolino Send private email
Sunday, August 05, 2007
 
 
Ooops. The implementation I suggested for the "correct pagination" in my prevoius comment is buggy, beacuse it doesn't allow for the deletion of the last record of a page...

If you could know that X records already seen were deleted by someone else, then you could still use the "wrong pagination" by LIMITing the results from the (P * N - X)-th record.

But it's a bit difficult to know X. I think that the only possibility is to remember the ids of all the seen records, and compare that array with the ids of the pagination query.

X = count( array_diff( $seen_ids, $current_ids ) );


There is also the shift problem for insertions, which can be worded like this: A user cannot access the Y results added in between the results already seen. This problem is harder, because it causes the user interface to misbehave, and needs a user interface artifact to be solved.

$added_ids = array_diff( array_slice( $current_ids, 0, count( $seen_ids ) ), $seen_ids )

The records corresponding to the added ids can be contigously-showed out-of-place on the next page...
Andrea Ercolino Send private email
Sunday, August 05, 2007
 
 
Oracle's latest version supports querying previous versions of your data, so all you'd have to do is keep the version the first page was displayed at.

Meanwhile nobody seems to care.  I've noticed Google giving completely wrong total results numbers, probably just to be more performant.

I think showing stale data is pretty counterintuitive for the user, though.  If they click on something that has been removed, for example, you'll have to give them an error page.  Better to just put a message at the top that the list has been updated recently and show them the latest version.  If they decide it impacts them, they can start over.

Wednesday, August 29, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz