如何在mysql中存储顺序数据?

I have been given some Exercise Routine content which is to be shown sequentially to user. An example in simplest form is shown below.

我得到了一些练习常规内容，这些内容将按顺序显示给用户。下面显示了一个最简单的示例。

id  name        duration    completed
----------------------------------------
1   jumping     10          1
2   running     15          0
3   cycling     12          0
4   push ups    30          1

Usually the tables are minimum 100 rows/exercises and there are about 300 tables (different exercises for different criteria). We don't have any front end and update database directly using dbForge Studio db editor.

通常表是至少100行/练习，大约有300个表(不同标准的不同练习)。我们没有任何前端，直接使用dbForge Studio db编辑器更新数据库。

The previous developer had designed the table so that the unique id column also serves as sequence number. It also forms part of url. E.g. xxx/exercise/12 specifically looks for actual 12th row with id=12 (he assumed all ids are incrementing with 1 and are in sequence) in the database rather than row with id=12.

前一个开发人员设计了这个表，以便惟一id列也可以作为序列号。它也是url的一部分。例如，xxx/exercise/12特别在数据库中查找id=12的实际第12行(他假设所有id都以1递增并按顺序递增)，而不是id=12的行。

The obvious problem with this is when we want to add an exercise to table lets say after exercise 5 then we add it manually after 5, AND THEN have to manually update the ids for all the following exercises so that they are in sequence. Ideally, we should just add a row in the table and somehow explain the php code know that this exercise should appear after exercise 5.

明显的问题是，当我们想要在练习5之后添加一个练习，然后我们在练习5之后手动添加它，然后必须手动更新下面所有练习的id，以便它们是有序的。理想情况下，我们应该在表中添加一行，并以某种方式解释php代码，知道这个练习应该在练习5之后出现。

Similarly, for example, if we delete an exercise at number 17, then from url xxx/exercise/17 the content wont be served. Ideally, id=18 is now at spot 17 in sequence, so it has to be served that content.

类似地，例如，如果我们删除第17位的练习，那么从url xxx/练习/17中不会提供内容。理想情况下，id=18现在的顺序是spot 17，因此必须为其提供内容。

attempts

尝试

I have thought of adding another column sequence which will tell in what sequence to serve. But then again I am back to square 1, because I'll have to update the sequence numbers upon add/delete.

我想添加另一个列序列，它将告诉我们应该使用哪个序列。但是我又回到了平方1，因为我必须在添加/删除时更新序列号。

I have also thought of creating another table and storing id numbers in whatever sequence we want to serve the content. - But I am not sure if this is actually a good idea.

我还考虑过创建另一个表，并以我们希望提供内容的任何顺序存储id号。-但我不确定这是不是一个好主意。

Can anyone please guide me with suitable database / table design for this problem?

对于这个问题，谁能给我一个合适的数据库/表格设计的指导?

EDIT

编辑

I think I haven't been clear in my question.
I do not want to refill / reorder the keys. I am aware thats not how DBs should be designed.
BUT The problem is If I add a content at the end of the table then how can I tell table/php that I want to serve the newly added content (with id lets say 127) after id=15 rather than serving it last.

我想我的问题还没有弄清楚。我不想重新填充/重新订购这些钥匙。我知道DBs不应该这样设计。但是问题是，如果我在表的末尾添加了一个内容，那么我如何才能告诉表/php我想在id=15之后提供新添加的内容(id假设是127)，而不是最后一次。

3 个解决方案

#1

Regarding, "I have also thought of creating another table and storing id numbers in whatever sequence we want to serve the content. - But I am not sure if this is actually a good idea."

关于，“我还考虑过创建另一个表，并按照我们希望提供内容的顺序存储id号。”-但我不确定这是不是一个好主意。

It's not a good idea, it's a wonderful idea. Here is a skelton design. It ain't perfect, but it will get you started.

这不是一个好主意，这是个好主意。这是一个骨架设计。它不是完美的，但它会让你开始。

Table Exercise - ExerciseID, NameOfExercise, MeasuredIn, other fields you may want, . Sample values of NameOfExercise are push-up, measure in repetitions, and RunningOnTheSpot, measured in seconds.

表式锻炼——锻炼的名称，测量范围，其他你可能想要的领域。运动的样本值是俯卧撑，重复测量，跑在锅上，以秒为单位。

Table Routine - RoutineId, NameOfRoutine, other fields you might want. Sample values are, Jane Fonda's Routine, Navy Seal Routine, and Old Fart's Routine.

表例程——RoutineId, NameOfRoutine，其他你可能需要的字段。样本值是简·方达的例行公事、海军海豹突击队的例行公事和老屁的例行公事。

Finally, table ExerciseRoutine. This is a many to may relationship. An exercise can be in more than routine and a routine can have more than one exercise. Fields would be, ExerciseID, RoutineID, Sequence, MeasuredInMultipler, and other fields you might want.

最后,表ExerciseRoutine。这是一个多到梅的关系。一项锻炼可以不仅仅是例行公事，一项例行公事可以有不止一项的锻炼。字段可以是，exercise seid, RoutineID, Sequence, MeasuredInMultipler，以及其他您可能想要的字段。

Here is some sample data.

这是一些样本数据。

ExerciseId, NameOfExercise,       MeasuredIn
1           Push Ups              repetitions
2           Running on the Spot   seconds
3           Jumping Jacks         repetitions

RoutineId, NameOfRoutine
1          Jane Fonda
2          Navy Seal

and finally

最后

 RoutineID, ExerciseID, Sequence, MeasuredInMultipler
 1          2           1         60
 1          3           2         10
 2          1           1         500
 2          3           2         100

So the Jane Fonda routine is running on the spot for 60 seconds followed by 10 Jumping Jacks. Meanwhile, the Navy Seal Routine is 500 Push Ups followed by 100 Jumping Jacks

所以简·方达的动作是在现场跑60秒，接着是10个跳爆竹。与此同时，海豹突击队的常规动作是500个俯卧撑，然后是100个跳跃千斤顶

This approach allows you to have many routines, all independent of each other.

这种方法允许您有许多例程，它们相互独立。

#2

How about leaving the current ids as they are, let them remain as the ids and work in your URIs. Make a new column for sorting on. Instead of incrementing these values by 1 you can increment by 5, 10 or a hundred for each consecutive row. This allows you to insert new rows inbetween existing ones without modifying the previous data.

让当前id保持原样，让它们保留为id，并在uri中工作。创建一个新的列进行排序。与其将这些值增加1，不如对每一行增加5、10或100。这允许您在现有的行之间插入新的行，而无需修改以前的数据。

#3

You could add a sort column and then on insert or delete execute an additional update statement to correct the sort column. If you really wanted to you could look at MySQL triggers to perform the update

您可以添加一个sort列，然后在insert或delete时执行一个附加的update语句来纠正sort列。如果你真的想要，你可以看看MySQL触发器来执行更新。

#1