Hierarchical database "next in sequence" with Rails?

Question

I am building a site that has some ordered, hierarchical data stored in the database. There's a "Volume" table, a "Book" table that has volume_id and position fields, and a "Chapter" table that has book_id and position fields.

What I'd like to build is a browser that steps through the sections. So, if I'm rendering the details about Volume 2, Book 1, Section 5, I'd like a "Next Section" link that shows the name of the next section in order. The problem is that I can't come up with a cheap SQL query that can figure out the next section in sequence (or even determine if there IS a next section).

How should I set up my tables/queries so that this sort of query is simpler? Or is there a better way?

Answer 1

If you only have one column that you are going to be doing this to, then I agree with theIV - a good tree structure will be very fast and efficient. if you have sorting on multiple columns, you can actually use will_paginate and set the page size to 1. That way your previous, next and everything else is baked in.