CODEWITHSUNDEEP
databasealgorithm
eltonsky
eltonsky

Reputation: 367

smart display order in database table

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

Upvotes: 0

Views: 301

Answers (2)

rici
rici

Reputation: 241841

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.

Upvotes: 1

ryan1234
ryan1234

Reputation: 7275

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.

Upvotes: 0

Related Questions