如何在mysql中存储顺序数据 [英] How to store sequential data in mysql

问题描述

我已经给出一些要向用户依次显示的练习例程内容。最简单的例子如下所示。

 
 id名称持续时间
 -------------------- -------------------- 
 1跳10 1 
 2运行15 0 
 3循环12 0 
 4推通常这些表是最少100行/练习，大约有300个表（不同的练习用于不同的标准） ）。我们没有任何前端和更新数据库直接使用dbforge Studio 数据库编辑器。
 
 
 
以前的开发人员设计了这个表，唯一ID列也用作序列号。它也是url的一部分。例如。 xxx / exercise / 12具体地查找id = 12的实际第12行（他假定所有的id都按照1进行递增，并且按照顺序），而不是使用 id = 12 。
 
 
 
显而易见的问题是，当我们要添加一个练习到表格后，我们可以在<5>练习5之后说明，然后我们手动添加5之后，然后必须手动更新所有以下练习的ID，以便它们顺序。理想情况下，我们应该在表格中添加一行，   解释这个练习应该在练习5之后出现的PHP代码。 p> 
 
 
同样，例如，如果我们删除17号的练习，那么从url  xxx / exercise / 17 内容不会服务理想情况下，id = 18现在位于第17位，因此必须提供该内容。
 
 
 
 尝试 
 
 
 
我已经考虑添加另外一个列序列，它将以什么顺序说明。但是我再次回到第1位，因为我必须在添加/删除时更新序列号。
 
 
 
我也想到创建另一个表并存储ID号码以任何顺序我们想要提供的内容。 - 但是我不知道这是否真的是一个好主意。
 
 
 
 
 
 
任何人都可以引导我使用合适的数据库/表设计这个问题？
 
 
 
 编辑 
 
 
 
我想我还没有在我的问题清楚
 
我不想重新填写/重新排序密钥。我知道不应该设计DB。 
 
 BUT问题是如果我在表的最后添加一个内容，那么如何告诉表/ php我想为新添加的内容（id为127） id = 15 而不是最后一次。
解决方案
关于我还想到创建另一个表以我们想要提供内容的顺序存储ID号码 - 但是我不知道这是否真的是一个好主意。
 
 
 
这不是一个好主意，它是一个美好的主意这是一个skelton设计。这不是完美的，但它会让你开始。
 
 
 
表练习 -  ExerciseID，NameOfExercise，MeasuredIn，您可能需要的其他字段。 NameOfExercise的示例值为上推，重复测量和RunningOnTheSpot，以秒为单位测量。
 
 
 
表例程 -  RoutineId，NameOfRoutine，您可能需要的其他字段。示例值包括Jane Fonda的日程，海军密封和老屁例程。
 
 
 
最后，表ExerciseRoutine。这是很多可能的关系。运动可以超过常规，例行程序可以进行多次运动。字段将是ExerciseID，RoutineID，Sequence，MeasuredInMultipler和您可能需要的其他字段。
 
 
 
以下是一些示例数据。
  ExerciseId，NameOfExercise，MeasuredIn 
 1推动重复次数
 2在现货秒上运行
 3跳跃插孔重复
 
 RoutineId，NameOfRoutine 
 1 Jane Fonda 
 2 Navy Seal 
  
最终
  RoutineID，ExerciseID，Sequence，MeasuredInMultipler 
 1 2 1 60 
 1 3 2 10 
 2 1 1 500 
 2 3 2 100 
  
所以Jane Fonda例程正在运行现场60秒，其次是10个跳跃插孔。同时，海军封印程序是500次推动，然后是100个跳跃插孔。
 
 
 
这种方法允许你有很多例程，彼此独立。
 
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.

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.

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.

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.

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.

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.



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.
解决方案
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."

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.

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.

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

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

                        
这篇关于如何在mysql中存储顺序数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！