0

I'd like to save some data in a MySQL table. The data have their orders which can be modified at my will. Say I have 10 rows. I want to move the 10th row to the 5th position, or insert some more rows between 2nd and 3rd position. Then, with a viewer I can get the data with the order I set. How can I implement such a table?

As I thought, I would save the order as float number in a new column. Each time I change the order, say, move 10th row between 5th and 6th, I would get the order number of 5th and 6th and get the average number of them, and update the order column of 10th row. Then I can get the data that ORDER BY order column. But I don't think it's a good idea. Any help about this problem?

Hou Lu
  • 3,012
  • 2
  • 16
  • 23

1 Answers1

1

You don't order the table, that makes no sense. You are actually not interested in the order the entries are placed in that table. Consider it random.

You are interested in the order you want to see the entries in. For that you create an additional column, call it "select_order" or "priority", however you like. In that you store simple integers which you use to describe the order you want to see the entries in.

Now you can "re-order" the entries however you like by changing those numbers in that order column. At query time you add an ORDER BY select_order clause to your SELECT query and will receive the entries in exactly the order you want.

This is the standard approach for relational database models. Which does not mean that there are no other approaches that might be interesting to look into for very special situations:

  • a priority table instead of a column which is joined during the SELECT query. This might make sense for situations with much more write than read operations. Note the much however.
  • a multiple column approach for situations where you can group entries and only re-order inside such groups. That dramatically reduces the number of entries you have to updated in case or re-ordering.
arkascha
  • 41,620
  • 7
  • 58
  • 90
  • What if I have to change the order of these rows? I have to change all the entries after the row that I modified. – Hou Lu May 08 '17 at 06:57
  • That is correct. It is a single `UPDATE` query, though. – arkascha May 08 '17 at 06:58
  • Don't call it order – Strawberry May 08 '17 at 07:10
  • @Strawberry True, actually, that is a reserved key word ;-) – arkascha May 08 '17 at 07:29
  • Won't it be poor on performance? Always modify so many entries? – Hou Lu May 08 '17 at 07:34
  • @LuCima It is a certain overhead, but consider that this is only during rare updates. Typical applications do _much_ more read than write operations. And the issue is: if you need to change the order, then you have to write something anyway. Updating a single column definitely is faster than shuffling the whole table content! – arkascha May 08 '17 at 07:36
  • @arkascha I see. I also read the similar post and it suggests the same way. – Hou Lu May 08 '17 at 07:53
  • @LuCima This absolutely is the standard approach when using a relational database model. It actually does make a lot of sense one you start getting used to the idea that a "natural" order in such a table does not really make any sense. – arkascha May 08 '17 at 07:54
  • @LuCima Oh, one thing: keep in mind to create matching indexes for your tables for performance reasons. So especially create an index on that column! – arkascha May 08 '17 at 07:55
  • @arkascha Got it. I'll have a try. – Hou Lu May 08 '17 at 07:57
  • @LuCima I added two more options to the answer, just to point out the difference between the standard approach and very, very special situations. – arkascha May 08 '17 at 08:01