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
:
CREATE TABLE AS your_table (id, title, sort_order)
AS VALUES
(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:
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?
Best Answer
No, there's a simpler way.
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'.)
No, there's a simpler way. Just delete the row. The remaining rows will still sort correctly.
A unique constraint can prevent that.
Gaps have no effect on how a dbms sorts values in a column.
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.