Storing data in a user specified order

user_898348 0
Viewed 80

I know that in SQL databases data is not stored in a particular order and that ordering is only done via ORDER BY statements.

What is the usual approach if I need to store data in a arbitrary order?

Example:

I have names and I want to store them in a certain arbitrary order like:

name
-----
foo
hello
bar

One approach would be to have an auxiliary field order like this:

order  name
-----------
1      hello
0      foo
2      bar

then I can sort by the order field.

But if I need to insert another name (newname) that should be between foo and hello I need to change the order fields of hello and bar so the table would be this:

order  name
-----------
2      hello
0      foo
3      bar
1      new
  • Is this approach correct?
  • Couldn't the renumbering process be very slow if the table contains many rows?
  • Are there better approaches for this kind of problems?
sorting sql
1 Answers

As you noted, renumbering the entire table when you have a new item is probably not a great idea.

One common practice is to keep sufficient gaps in the numbering values so you can insert new values between them. E.g., if you start off with 0, 100 and 200, the new value can easily be 50.
Note that in theory, you could also use floating-point numbers which would give you the ability to insert infinite new values between each value. In practice, the precision the database can store these floating point numbers is limited (depending on the RDBMS and the column definition).

user_24227760
Related