Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of relationships, so a relational database seems to make sense.

The common solution that I have seen is to add an integer column order:

| id | title          | order |
+----+----------------+-------+
| 0  | Lorem ipsum    | 3     |
| 1  | Dolor sit      | 2     |
| 2  | Amet, consect  | 0     |
| 3  | Elit fusce     | 1     |

Then, we can sort the rows by order to get them in the proper order.

However, this seems clumsy:

  • If I want to move record 0 to the start, I have to reorder every record
  • If I want to insert a new record in the middle, I have to reorder every record after it
  • If I want to remove a record, I have to reorder every record after it

It's easy to imagine a situations like:

  • Two records have the same order
  • There are gaps in the order between records

These could happen fairly easily for a number of reasons.

This is the approach that applications like Joomla take:

Example of Joomla's approach to ordering

You could argue that the interface here is bad, and that instead of humans directly editing numbers, they should use arrows or drag-and-drop—and you'd probably be right. But behind the scenes, the same thing is happening.

Some people have proposed using a decimal to store order, so that you can use "2.5" to insert a record in between the records at order 2 and 3. And while that helps a little, it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?)

Is there a better way to store order in a table?

share|improve this question
    
This is my first post here, so I hope I've done everything right. I tried to be as explicit in my question as possible. If there's anything I can improve, please let me know! –  Tom Marthenal Mar 17 '13 at 18:44
1  
Just so you know . . . "The reason such systems are called "relational" is that the term relation is basically just a mathematical term for a table . . ." -- An Introduction to Database Systems, CJ Date, 7th ed. p 25 –  Mike Sherrill 'Cat Recall' Mar 17 '13 at 20:06
add comment

3 Answers

up vote 6 down vote accepted

If I want to move record 0 to the start, I have to reorder every record

No, there's a simpler way.

update your_table
set order = -1 
where id = 0;

If I want to insert a new record in the middle, I have to reorder every record after it

That's true, unless you use a data type that supports "between" values. Float and numeric types allow you to update a value to, say, 2.5. But varchar(n) works, too. (Think 'a', 'b', 'c'; then think 'ba', 'bb', 'bc'.)

If I want to remove a record, I have to reorder every record after it

No, there's a simpler way. Just delete the row. The remaining rows will still sort correctly.

It's easy to imagine a situations like:

Two records have the same order

A unique constraint can prevent that.

There are gaps in the order between records

Gaps have no effect on how a dbms sorts values in a column.

Some people have proposed using a decimal to store order, so that you can use "2.5" to insert a record in between the records at order 2 and 3. And while that helps a little, it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?)

You don't stop until you have to. The dbms has no problem sorting values that have 2, 7, or 15 places after the decimal point.

I think your real problem is that you'd like to see values in sorted order as integers. You can do that.

create table your_table (
  id int primary key, 
  title varchar(13), 
  sort_order float
);

insert into your_table values
(0, 'Lorem ipsum', 2.0),
(1, 'Dolor sit', 1.5),
(2, 'Amet, consect', 0.0),
(3, 'Elit fusce', 1.0);

-- This windowing function will "transform" the floats into sorted integers.
select id, title,
       row_number() over (order by sort_order)
from your_table
share|improve this answer
    
how do you calculate the number in between? –  xenoterracide Apr 4 '13 at 15:52
add comment

Generally, ordering is done according to some information in the records, title, ID, or whatever is appropriate for that particular situation.

If you do need a special ordering, using an integer column isn't as bad as it might seem. For example, to make room for a record to go into 5th place, you could do something like:

update table_1 set place = place + 1 where place > 5.

Hopefully you can declare the column to be unique and maybe have a procedure to make rearrangements "atomic". The details depend on the system but that's the general idea.

share|improve this answer
add comment

…it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?)

Who cares? These numbers are only there for the computer to deal with so it doesn't matter how many fractional digits they have or how ugly they appear to us.

Using decimal values means that to move item F between items J and K all you need to do is select the order values for J and K then average them then update F. Two SELECT statements and one UPDATE statement (probably done using serializable isolation to avoid deadlocks).

If you want to see integers rather than fractions in the output then either calculate integers in the client application or use the ROW_NUMBER() or RANK() functions (if your RDBMS includes them).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.