in reply to reordering a stack with little effort
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)
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.