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
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?