1

I am building a database for a small project. I don't have much experience so I am unsure, but I get the feeling I am not approaching this correctly.

I have a system which allows users to create courses, which contain sections of questions which they answer. I have separated out courses, sections and questions as separate entities, but the questions table requires foreign keys from:

  • The Users table to know who created it.
  • The Courses table to know what course it is part of.
  • The Sections table to know what section of the course it is in.

enter image description here

Question: In terms of the Questions table requiring foreign keys from everywhere, is this layout acceptable? Is there any improvements that could be made?

Thanks for any help.

Improve this question
0

3 Answers 3

Reset to default
6

Bad. Here are some random notes which come from experience dealing with this sort of system. Take as you will:

  • A big offender is xyz_N columns (i.e. answer_2 and a5_comment). When you see this, think incorrectly normalized data. What happens if there are 6 questions? 100 questions? (Oh, yes, I develop this sort of software and you wouldn't believe how many questions people are expected to answer!)

  • Another bad design is trying to "share" a table: Question also serves as "Answers"? No; again, de-normalized data! How will multiple people answer without duplicating data? It's not possible because each "Answer" is a Question! Think in terms of actions/queries and ask if they can be fulfilled.

  • Keeping the above in mind, there are very crucial missing relationships for Surveys/Exams (which "contain Questions") and Responses (which "contain Answers"). These relationships are very important for normalization (no xyz_N columns) and being able to ask questions like: "Which questions should I show now?"

  • There are no "Users" relating to Courses. There are Teachers (Faculty) and Students. Students can not "own" courses. This is easily and cleanly separable and allows better DRI. Also, different information needs to be captured for different "user" types - trust me. Also, sometimes a Teacher is a Student as well!

  • Redundant relationship from "Answer"/Question to Course; this relationship exists Question to Section to Course and does not need to be duplicated.

Improve this answer
3
  • Sorry, I should have mentioned: answer_1 etc and a1_comments aren't user answers, they are non-changing multichoice answers to the question. The design will also include user actions at some stage, I was just worried about the foreign keys being shared in 'loops'.
    – T May
    Jul 12, 2013 at 5:08
  • @TMay The only "wrong" FK from above is the redundant one from Questions to Courses. There really are no "loops"; at least none that can be eliminated without changing the DRI. The model still has a nice flow, and cascades can be setup to make sense.
    – user2246674
    Jul 12, 2013 at 5:15
  • @TMay (The FK from Questions to Users is required because it is [likely] the case that Questions will be created by some User/Administrator who might not be related to the course; in this aspect the admins columns on Courses seems suspect, but not necessarily incorrect. The course administrator might not be the User/Administrator who [originally] created the Questions. Adjust as required.)
    – user2246674
    Jul 12, 2013 at 5:47
4

It seems you can drop the fk to courses, since you can get there via section. If the creator of the question is always the owner or admin of the course, then the same logic applies and you can remove the creator as well.

You should also split your Answer and comments out to another table, foreign keyed back to questions.

Improve this answer
1

It's true a Course has many Questions, but if all questions are contained in a Section, then you can get to them through section.

    SELECT
    q.QuestionName
    FROM
    Question q
    LEFT JOIN Section s
    ON q.SectionId = s.SectionId
    LEFT JOIN Course c
    ON s.CourseId = c.CourceId
    WHERE
    c.CourseName = "MyCourse"

I'd also advise you need to separate out your users into Teachers and Students. That way, you could have relationships as such

    Teacher --< Question   (Creates Many)
    Teacher --< Course     (Teaches Many)
    And
    Student --< Course      (Does Many)

Also, I would definitely separate out Answers from Questions

    "StudentAnswer"
    PK StudentAnswerId
    FK StudentId
    FK QuestionId
    Answer

    Student --< StudentAnswer >-- Question

If you want a question to have a set choice of answers there's always to option to add a new table. This way there is no limit on how many option there are to choose from.

    "QuestionAnswerChoice"
    PK QuestionAnswerChoiceId
    FK QuestionId
    Choice

    Question --< QuestionAnswerChoice

It all depends on how normalised you want to go. If your questions were all multiple choice and all had 4 options; then yeah - have them in your question table. But if there's only 1-2 multi-choice questions out of 100, then think of all the nulls this will produce.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.