reordering a stack with little effort

punkish has asked for the wisdom of the Perl Monks concerning the following question:

order monks,

I have, what sounds like, a common problem. I am creating a survey. A table holds the questions.

QUESTIONS
-----------------
QUESTION_ID (pk)
QUESTION_NUM (int)
QUESTION (varchar)
SHOW_THE_QUESTION (bit)
[download]

I have a 100 odd questions in already. Everything works. A colleague comes and adds 3 more questions, "Yo punk, please add this question after the current q 10, that question after the current q 59, etc." You get the picture. Now I have to go and re-order the darn QUESTION_NUM field. What a pain in the you-know-what.

So, I decided to implement the old FORTRAN, BASIC kinda system... provide a way to insert something in the stack so that the stack rearranges itself... allow the QUESTION_NUM field to accept floating points. So, I can add a new question, and enter 10.5 for its QUESTION_NUM, sort on the said field, and bingo.

Of course, then in the program I have to do some post-processing. The users won't understand what the heck q 10.5, or 10.725 (a result of many a new inserts) is. So, I have to renumber them after retrieving them from the db just for display purpose.

My problem doesn't end, however... some questions refer to other questions, "If you answered 'Yes' in q 20..." Of course, q 20 on the view side might QUESTION_NUM 19.25 in the table.

I am sure there are other pitfalls. In fact, just by checking off the SHOW_THE_QUESTION field I can set off the same problem.

Since this seems to be a fairly common problem, what are the creative ways that monks have solved or would advise solving this?

--

when small people start casting long shadows, it is time to go to bed

Comment on reordering a stack with little effort Download Code

Replies are listed 'Best First'.
Re: reordering a stack with little effort by fberg (Initiate) on Mar 11, 2005 at 01:10 UTC
I think the best way to solve this problem is to avoid it. I don't see whats the problem in re-ordering the QUESTION_NUM field. If I remember well, it should be a single, simple SQL command to do that for you. Suppose you want to insert a new question after your question number 17, it should be like this: `update QUESTIONS set QUESTION_NUM = QUESTION_NUM +1 where QUESTION_NUM + > 17` [download] Then insert your new question 18.	[reply] [d/l]
Re: reordering a stack with little effort by Mugatu (Monk) on Mar 11, 2005 at 00:53 UTC
Instead of this system of using fractional question numbers, I would consider giving the questions a simple incrementing integer for identification, and having the order of the questions be stored by other means. A simple system for maintaining order might be a linked list. A table to hold this information could look something like this: id \| next ----+------ 1 \| 2 2 \| 3 3 \| 4 4 \| NULL Then, if someone asks you to insert a question between 2 and 3, you would simply have to change it like so: id \| next ----+------ 1 \| 2 2 \| 5 5 \| 3 3 \| 4 4 \| NULL	[reply]
Re^2: reordering a stack with little effort by sgifford (Prior) on Mar 11, 2005 at 06:57 UTC
Cool idea, but I can't think of how you would ask SQL to put the list in order for you...	[reply]
Re^3: reordering a stack with little effort by Crackers2 (Parson) on Mar 11, 2005 at 19:21 UTC
In Oracle you can use `CONNECT BY` to do this: `SELECT id FROM thetable START WITH id = 1 CONNECT BY PRIOR next = id` [download]	[reply] [d/l] [select]
Re^3: reordering a stack with little effort by punkish (Priest) on Mar 11, 2005 at 14:17 UTC
this is actually a very nice idea... you would simply ORDER BY NEXT instead of ORDER BY QUESTION_NUM. To tell the truth, I thought of using this method, but chose to implement the decimal system instead. I still think this is better, but since my decimal system is already working, I am gonna leave it in place. For my next implementation, I am going to use a linked list. -- when small people start casting long shadows, it is time to go to bed	[reply]
Re^4: reordering a stack with little effort by simonm (Vicar) on Mar 11, 2005 at 15:50 UTC
Re: reordering a stack with little effort by kvale (Monsignor) on Mar 11, 2005 at 00:46 UTC
In perl, I would create an array of questions, each array element holding a reference to a hash: `my @questions # a question from the Tasmanian Devil push @questions, {id => 1, text => "Why for you want to bury me in the + cold, cold ground?, show => 1}; # etc.` [download] Note here that QUESTION_NUM is just the index in the array plus 1. Then inserting a question after question 10 is a simple matter of of a splice: `# Maxwell Smart? my $new_q = {id => 9, text => "Shall we use the Cone of Silence?, show + => 1}; splice @questions, 9, 0, $new_q;` [download] For references to other questions, just add a reference => $quest_ref element to the hash. -Mark	[reply] [d/l] [select]
Re: reordering a stack with little effort by Zaxo (Archbishop) on Mar 11, 2005 at 00:44 UTC
Leave out the question number as a property of the question. In OO terms, it is not related to the question, but to a collection of questions. Call that a questionaire or a quiz, I guess. After Compline, Zaxo	[reply]
Re^2: reordering a stack with little effort by Anonymous Monk on Mar 11, 2005 at 15:09 UTC
That just depends on ones point of view. If you think it's important to have "global" information, use a "collection" and define the other there. But there's no need for global information - the entire sequence of questions can be retrieved by looking at the data locally: as long as each question knows its neighbour(s), the entire collection and its order can be retrieved as well. Of course, if you want to create multiple collections, you can't store the information locally as easily, as a question can belong to more than one collection. Having said that, even with collections, you haven't solved, or even side-stepped the problem. You will still have a sequence of numbers somewhere in a table that indicates the order in your collection - and you know have to insert a question.	[reply]
Re: reordering a stack with little effort by jhourcle (Prior) on Mar 11, 2005 at 02:07 UTC
If you're using Oracle, it's fairly easy: `SELECT ROWNUM, question_id, question FROM questions WHERE SHOW_THE_QUESTION = 1 ORDER BY question_num` [download] If you're using some other database, you can just tick off a counter as you pull them out. You then print and/or refer to the questions by those numbers when you're talking to the user taking the survey. If you're adding/removing questions at a whim, they only really have a 'number' in this sense once the survey/quiz/questionaire/whatever is instantiated, and may change for any other instance. (to build on what Zaxo said) Update: oops...ROWNUM, not ROW_NUM	[reply] [d/l]
Re: reordering a stack with little effort by pernod (Chaplain) on Mar 11, 2005 at 07:53 UTC
How about adding another column, called ordering or something? `QUESTIONS ----------------- QUESTION_ID (pk) QUESTION_NUM (int) QUESTION (varchar) SHOW_THE_QUESTION (bit) QUESTION_ORDER (int)` [download] Getting the questions in the correct order then simply means using `ORDER BY QUESTION_ORDER`. If you initially increment QUESTION_ORDER by ten or fifty or whatever, you can insert other questions in between the existing ones. This allows you to keep the integrity of "if you answered $something on $question_id .." references, while giving you (a bit) more flexibility in the ordering. This is of course not a complete solution to the problem, since some devil's advocate will surely reply to this node saying "What if you want to insert $inc + 1 questions between question 1 and 2?" (Where $inc is the difference between the two question's QUESTION_ORDER). In this case you will be forced to reorder your QUESTION_ORDER columns, but you will still have the integrity between questions. A little bit of decoupling goes a long way, though. pernod -- Mischief. Mayhem. Soap.	[reply] [d/l] [select]
Re: reordering a stack with little effort by NiJo (Friar) on Mar 12, 2005 at 19:49 UTC
The question number as integer comes for free. Using a float as QUESTION_NUM is a good idea for less than approx. 1000 questions. Retrieve the db records in a sorted array via DBI::fetchall_arrayref. `foreach my $i (@questions){...}` Searching the array is affordable for converting from float to integer.	[reply] [d/l]


We don't bite newbies here... much
	PerlMonks