MySql db结构按顺序存储项目列表

Question

I need to store and retrieve items of a course plan in sequence. I also need to be able to add or remove items at any point.

The data looks like this:

-- chapter 1
 --- section 1
 ----- lesson a
 ----- lesson b
 ----- drill b
 ...

I need to be able to identify the sequence so that when the student completes lesson a, I know that he needs to move to lesson b. I also need to be able to insert items in the sequence, like say drill a, and of course now the student goes from lesson a to drill a instead of going to lesson b.

I understand relational databases are not intended for sequences. Originally, I thought about using a simple autoincrement column and use that to handle the sequence, but the insert requirement makes it unworkable.

I have seen this question and the first answer is interesting:

items table
item_id     |     item
1           |     section 1
2           |     lesson a
3           |     lesson b
4           |     drill a

sequence table
item_id     |     sequence
1           |     1
2           |     2
3           |     4
4           |     3

That way, I would keep adding items in the items table with whatever id and work out the sequence in the sequence table. The only problem with that system is that I need to change the sequence numbers for all items in the sequence table after an insertion. For instance, if I want to insert quiz a before drill a I need to update the sequence numbers.

Not a huge deal but the solutions seems a little overcomplicated. Is there an easier, smarter way to handle this?

Answer 1

Just relate records to the parent and use a sequence flag. You will still need to update all the records when you insert in the middle but I can't really think of a simple way around that without leaving yourself space to begin with.

items table:

id | name      | parent_id  | sequence
--------------------------------------
1  | chapter 1 | null       | 1
2  | section 1 | 1          | 2
3  | lesson a  | 2          | 3
4  | lesson b  | 2          | 5
5  | drill  a  | 2          | 4

When you need to insert a record in the middle a query like this will work:

UPDATE items SET sequence=sequence+1 WHERE sequence > 3;
insert into items (name, parent_id, sequence) values('quiz a', 2, 4);

To select the data in order your query will look like:

select * from items order by sequence;