Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

database design query.

Options
  • 30-04-2008 1:45pm
    #1
    Creamy Goodness
    Registered Users Posts: 26,579 ✭✭✭✭


    i have this table like this which stores information on a problem on a piece of code.

    problemID
    project
    product
    prodnum
    description
    origin
    status
    submitDate
    answer1
    ...
    answer10

    answer1 = answer to question 1 i want to ask.
    there are 10 questions in total to the user, and want to store the question text and the answers in the database, so that they can be changed easily through an admin panel on the webpage using PHP.

    what i'm stuck on though is what is the best way to design this?


Comments

  • Options
    #2
    kmick
    Registered Users Posts: 3,375 ✭✭✭kmick


    Normalisation is the only way to go if you expect to have a lot of records in this DB.
    http://en.wikipedia.org/wiki/Database_normalization

    Issue Table
    IssueID
    UserID
    IssueDescription
    DeptID
    etc

    Dept Table
    Dept ID
    DeptName
    Dept Details
    etc

    User Table
    UserID
    UserName
    UserPhone
    etc

    Problem Table
    QuestionID
    QuestionDetail

    Problem Answer Table
    UserID
    QuestionID
    UserAnswer

    Problem Answer Table Stats Table
    QuestionID
    AppropriateStatField


  • Options
    #3
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    Although normalisation as kmick posted looks painful and like a lot of work, when it comes to scaling your application and bolting on extra bells and whistles you'll be very thankful for it.

    One handy method of having a questionnaire which I've found is to delimit the questions' availabilty by date. So you have two more columns in the problem table above - validFrom and validTo.

    This allows you to "deactivate" questions when they're no longer required and also allows you to pre-load in new questions, weeks or even months before they come on-stream. This can look very slick, particularly where the questions may change a lot.

    When doing questionnaire stuff, you have to think about what happens in the future - people will want to change the questions, but they don't want to lose the historical data.


Advertisement