Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Which apprach is better to to introduce an ID field or combine other fields?

    I have the following problems when i am designing my database for an online exam system:-
    1. I have a question table and answer table; where each question contains many answers and each answer have one question.
    Now i have two approaches for designing the answer table, either by having:-

    Answer_Id (PK),
    Answer_Description,
    Is_right.
    Question_ID (FK)

    Or

    Answer_description
    Is_right.
    Question_ID (FK)
    Having PK (Question_ID & Answer_description).

    2. Same issue applies for this scenario; I have an article table and a comment table, where each article can have many comments and each comment can belong to an article.

    Now i have two approaches for designing comment table, either by having:-
    Comment_ID (PK)
    article_ID (FK)
    Comment_Desc
    Created_BY
    Created_time

    Or

    article_ID (FK)
    Comment_Desc
    Created_BY
    Created_time

    Having PK(Created_BY & Created_Date), since it s not possible to be able provide two comment at the same time!by the same user!!

    So is it better to introduce the ID field for both (comment & answers) tables or having my PK by combining the available columns
    Thanks in advance for any help.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I prefer your "first" options.

    For example, in the first case, ANSWER_DESCRIPTION may be "GROUP BY clause is used to sort records returned by the SELECT statement" (which is wrong - ORDER BY clause is to be used here - but it might be an option in your "answers" list). Having that string as part of a primary key is, well, not the right approach. Answers should have their IDs (numerics, I suppose), and they should be used as primary keys.

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    I think ANSWER table has answers of multiple choice questions.

    Go for first approach with this change.
    In the case Table should have PK on (Question_no,Answer_id).
    So it will be easy to validate the answers. By selecting description there may be issue of performance in case of large question database.

    Please let me know if I am on wrong track.

Posting Permissions

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