Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New Coder
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    *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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.
    Last edited by salmanmanekia; 11-11-2012 at 09:01 AM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    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 wrote
    I have a few related fields such as question and multiple choice asnwers
    but you have never even shown us what those "related fields" are and what DB tables they are in, etc.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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?
    Yes, i have to keep a history of all changes made in the dB.

    but you have never even shown us what those "related fields" are and what DB tables they are in, etc.
    Sorry for the wordings, there are only two fields question and multiple choice answers.

    I hope we are moving forward

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    Yes, i have to keep a history of all changes made in the dB
    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:
    Code:
    INSERT INTO QuestionsHistory
    SELECT NOW(), $idOrNameOfPersonMakingChange, * 
    FROM Questions WHERE questionNumber = $numberOfQuestionBeingChanged
    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.
    Last edited by guelphdad; 11-12-2012 at 02:16 PM. Reason: fixed bold tag
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    salmanmanekia (11-12-2012)

  • #11
    New Coder
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    Quote Originally Posted by salmanmanekia View Post
    Should i make another History table for it such as AnswerHistory ??.
    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.".

    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.
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    New Coder
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    What have you designed, so far, for the Questions and Answers 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)

    And do you also have a Students table and a StudentScores table? Or some other similar tables?
    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

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    salmanmanekia (11-15-2012)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •