ComfortMoose ComfortMoose - 4 months ago 11
SQL Question

Best way to rearrange order of db tables items?

I have a db table of items by no particular order. I want a user to be able to sort and resort the items by how important they deem them to be. What is the best way to approach this? Think of a todo list of items someone can order by the order in which they want to do the.

I'm doing this in php/codeigniter, but overall approach thoughts are very welcome. Thanks.

Answer

In other words, the user can arrange the items in an arbitrary order, and that ordering needs to be persisted in the database?

As one commenter points out, there is no inherent ordering to rows in a database. Any retrieval that needs rows in a particular sequence needs to state the desired sequence via an ORDER BY clause.

For what you are suggesting, rather than incorporate a column to store the ordering information directly in your data table I would suggest a one-to-one join table with two columns - one being the primary key to the row, and a second used to store an integer that holds the user's sequence. This allows the sequence to change without actually modifying the rows themselves (preserving caches, etc.). For example, a table like:

CREATE TABLE UserSequence(
    key INT UNSIGNED,
    sequence INT UNSIGNED)

Where the 'key' column is a foreign key to the primary key of the table holding your data rows.

If you use largish numbers for the sequence (e.g., ordinal x 1000), you will leave space for users to create new items (or reorder existing items) with a priority somewhere in the middle of the sequence without renumbering all the rows.

Retrieval in sequence then is a simple matter of joining the UserSequence table in your query and ORDER BY the sequence column.

Also note, you could put a uniqueness constraint in place on the sequence column, which will allow the DB to tell you when you need to fall back and re-number items according to your chosen value for stride (e.g. 1000) rather than do a mid-sequence insert (where, for example, the sequence value is chosen via something like prev + (next - prev)/2). A foreign key constraint on the key column is also advisable.

Comments