Questions on Update and Cascade Delete Implementation

[jacobpressures]

I have a db that stores tests, a pool questions, and answers. Each employee though taking the same test will get a different "version" since the questions are selected from a pool. The questions he is asked and the answers he selects are saved to the db in the user tables. The responses.AnswerID refers back to the Answers table where employee answers can be check as right or wrong.

Problem:

If a question is updated, i have a least an updated column, however, the whole structure of the question may be changed, including the answer. Those who answered the question before it was changed may report getting the question wrong when in fact i was right before the change.

The question may be totally different from the original question, being about a different subject than before.

Cascade deletes could delete past employee testing info. However, if the questions are deleted without affecting the employee tests, then there will be nothing for the employee question ids to refer back to.

So how do i modify the db without corrupting the data?

The table schema can be found at

http://www.geocities.com/jacobpressures/

Thanks!

 

[mrdenny]

Mark the question as inactive and create a new question. This way all the old questions are still in the system, they are just not active. Then have the test pull from only the active questions.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[jacobpressures]

Thanks very much! I'll do that!