database - How to design table that can be re-sequenced? -

I need to make a design decision about the database. I need to have a < Em> AUTO_INCREMENT primary key is named ID . By default, each line user (in the web) is charged by id . For example, if the table has 4 records the UI will show the rows in the order of 0, 1, 2, 3 .

Now, the user needs to drag & amp; To change the sequence, say the drop line in the UI, Drop User Drag ROM3 and Beef 0. So, the display becomes 3, 0, 1, 2 . This sequence should be continuous in the database.

I am thinking about how to design a database table to make it continuous and scalable. My first thought is that each row contains a " sequence < / Strong> "field that shows the display sequence. By default, the value should be similar to id when selecting data from the database for the display, instead of ID

< The rows climbing on the strong> sequence are sorted, if the sequence changes, then it is updated to the new value. The result is that there may be many changes in other rows. Take the example above, basically the table is like this:

  | ID | Sequence | | 0 | 0 | | 1 | 1 | | 2 | 2 | | 3 | 3 |  

Now, after the first drag from the id of the row, Its sequence has been updated to 0. At the same time, the line along with ID 0, 1, 2 should also be updated.

  | Id | Sequence | | 0 | 1 | | 1 | 2 | | 2 | 3 | | 3 | 0 |  

I am afraid that the cost of re-sequence from this point will be very high and will not be scalable. Therefore, I think that the sequence can be built by multiplying id with K (say, 10). This leaves the difference between the sequence value for the inclusion, however, the interval can still be consumed if the K + 1 rows are moved to this difference.

  | Id | Sequence | | 0 | 0 | | 1 | 10 | | 2 | 20 | | 3 | 30 |  

This is a common problem for database design. Anyone have better ideas for knowing this?

I have a clear answer that using the last solution you mentioned is to use it but with decimals (Floats).

Then you start, say: {0.1, 0.2, 0.3, 0.4, 0.5} . If you move the last code between 0.2 and 0.3 then it becomes 0.25 . If you take it to the top it becomes 0.05 . Every time you take points between two numbers on any side in other words, the average of the previous / next items.

Use one more similar Hull characters, then sort alphabetically by alphabetically. Starting with {1, 2, 3, 4, 5} , if you take between 2 and 3, then you will use 25. If you do a string, then you keep the right order: {1, 2, 25, 3, 4} .

The only problem is that I can think in these ways that ultimately, you will affect the threshold of the exact point exact, i.e. 0.0078125 and 0.0078124 Trying to find a number between Some ways to solve this:

• Run a script run through everything and give them {0.1, 0.2, 0.3, ...} .
• Do not use two decimal places when you can use one. You can use 0.23 instead of 0.225 between 0.2 and 0.25 .
• Do not globally, if you have {0.2, 0.3, 0.6} and want to insert after 0.2 You can set the second to 0.4 and enter the new item on the 0.3 .