0

Many tables in our db is using a field "display_order" to decide the order of records on the screen. And we are using int for this field. In the beginning, when we insert records, we will leave some gap between them, e.g., first record we give display_order = 0, second record display_order = 1000. In this way we have some gap between first and second record. So if we want to insert a new record in between these two, we can give a display_order = 500.

This approach has a limitation that when you run out of the gap between 2 adjacent records, you have to start updating display_order for many records in order to make some gap.

I have thought of using floating point or just string for this, but not sure that will hurt comparing performance.

What do you think of this from your experience?

Cheers, Elton

eltonsky
  • 367
  • 2
  • 11
  • It is a hard problem, since the display_order ("tabbing order") is (part of) a candidate key. See my answer here: http://stackoverflow.com/a/14092775/905902 – wildplasser Jan 21 '13 at 23:42
  • hmmm, I think my question is a bit different. I don't care if all keys are used or not. I just don't want to do updates to make gap for new record. So I guess I am looking for a good way, or good format for my display_order field. – eltonsky Jan 22 '13 at 00:56

2 Answers2

1

Floating point should not have any performance impact, and it's by far the simplest solution: just use the average of the surrounding values. In pathological cases, you can run out of bits for the division, so you need to make sure the average is different from both surrounding values, and if it turns out to be the same, you'll need to renumber. But that shouldn't happen very often.

rici
  • 234,347
  • 28
  • 237
  • 341
0

What about multiplying the display order by 2 or 3? This preserves the original order and introduces some more space between items. It's also easy to run that as an update query.

ryan1234
  • 7,237
  • 6
  • 25
  • 36