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)

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