keeping track of certain related field changes
 

I am totally new to databases and MySQL. I am working on a project in which I have a few related fields such as question and multiple choice asnwers. The value of those fields changes from time to time as data gathers.

Now my question is: how can I track those changes so that at any given time I am able to display the correct combination of the data? Any hints would be really appreciated.

Can you be more explicit?

What kinds of changes?

Do you mean that data in the records actually changes? For example, answer A to question 73 changes from "yes" to "maybe"? Or do you mean that new records (new questions, new answers) are added?

And what defines "correct combination of the data" in your mind?

Hi,

So I have a few more explicit requirements

The record can be changed, added or removed. Its for a questionaire form and it consists of multiple questions and i should be able to change the structure of the form on the fly, not just adding a new form every time there are changes.

Yes? That's not difficult. In fact, if you design the table and your PHP (I assume?) code correctly, it falls out naturally.

But I still don't understand in what way you want to TRACK changes.

Do you want to keep a history of what has been changed? Do you want to save changes info into a DB table or into a log file? Or what?

Thats what specifically being asked from me here. Given the scenario as explained in my previous thread, I have been asked how would i track the changes.
So, it could be said that what would be the most efficient/most common or the most easiest way to track the changes when one has to design a system like what i have to design.

*SIGH*. You *still* haven't explained what YOU MEAN by "track the changes".

The scenario you gave has nothing to do with "tracking" changes. All you are asking for there is a data-driven web site, where the content on the site is dependent on what is in the database.

It's almost trivial to write. It's done by literally millions of sites every day. Think about it: EVERY SINGLE e-commerce site of any size does this, just to pick an obvious example.

Do you need help designing the database for this? Or the PHP code that uses the database? (Or are you even going to use PHP? Maybe you will use ASP or JSP or who knows what else?)

Ask some *SPECIFIC* questions. So far all you have done is make a general statement about what you are supposed to do, and even that statement doesn't explain what you mean by "track changes".

I guess I will say that you likely want two database tables: Questions table and Answers table. But depending on what else the site is supposed to do, you may want a Students table and a StudentScores table. But, again, until you give SPECIFIC requirements I don't see how anybody can help you with specific details.

You maybe right that i am just explaining the general requirment and cannot answer your tracking question yet. Actually, as i mentioned in the first thread i am new to mysql and also the requirment for my assignment is not that comprehensive. I have explained what i have been told. I didnt knew it was incomplete but according to your experience and knowldege you know that it is incomplete :) . So, i would say the assumption in the assignment is that for the part where there is no explanation given like for tracking changes i would have to take decision on my own. Maybe be seeing the pros and cons of different options which are already used on websites that provide questionare forms.

Thats my main reason asking here that according to your experience how would you proceed with it.

Bytheway, i have two questions in assingment. First, asks me how would i track the changes based on the given info and the second part asks me how would i design the db. I have some question regarding the "design the db" but i will ask that later once we proceed from this.

Thanks for your time.

I disagree.

Design the database first. Then the answer for "how to track changes" should come naturally out of the design.

But since you have now revealed that this is a homework assignment--not some "project' for work--maybe you should ask you instructor just exactly what changes are supposed to be tracked.

Is it as simple as keeping a history of all changes made in the DB? Or is it something more subtle than that? If you don't know, how can you expect us to know and help you?

Also, in your first post you wrotebut you have never even shown us what those "related fields" are and what DB tables they are in, etc.

Yes, i have to keep a history of all changes made in the dB.

Sorry for the wordings, there are only two fields question and multiple choice answers.

I hope we are moving forward :)

Finally! That is what I have been asking you since my first post.

Okay...truly simple:

Say you have a table named Questions that has fields such as questionNumber and questionText and maybe points.

SO you simply create another table named QuestionsHistory with those same fields but ADDING fields whenChanged and changedBy and then, before you change any given Questions records you do:
NOTE: Because of the peculiarities of MySQL, you should put the *ADDED* fields *FIRST* in the QuestionsHistory table. It makes the above query easier to write.

Thank you Old Pedant. Can you also describe a bit about Answers table. Should i make another History table for it such as AnswerHistory ??. or since Question and Answer table would be connected the QuestionHistory would be enough. If also possible please explain in broader terms i mean taking into concerns all the table and also mention the design factor and queries whenever possible. Thanks for all your time. You are making it alot easier for me. :)

probably. almost surely. But it depends on what the requirements of the homerwork assignment are. In a real-world situation I would say absolutely "yes.".

Really not sure what you are asking here. I do feel you are making a mountain out of a molehill.

What have you designed, so far, for the Questions and Answers tables? And do you also have a Students table and a StudentScores table? Or some other similar tables?

This is what i have done so far.

question
--------
question_id (PK)
question_text


question_history
----------------
original_question_id (FK to question.question_id)
version_timestamp
question_text
version_number
(PK of this table is composed of original_question_id and version_number)

answers
-------
answer_id (PK)
answer_text
answered_question_id (FK to questions.question_id)

answer_history
----------------
original_answer_id (FK to answers.answer_id)
version_timestamp
answer_text
answered_question_version_number (FK to question_history.version_number)

This design is for questionaiare form. I have created the question/answer(history) tables. Now, I have two things to ask from you. First, can you see any design flaws in what i have done. Second, how to proceed from here. I was thinking of creating a forms table but am not sure. Thank You

One minor change *I * would make:

I would always save an answer in the answer_history table at the same time I store its question in the question_history table. Anything else would be too confusing.

And that means that the version number in the answer_history table *MUST* match the version number in the question_history table, to maintain the referential integrity between those tables. Okay, you seem to already be doing that. Fine. But then the version_timestamp in the answer_History table is redundant. So I'd kill it.

That's about it.