Is this good/bad database design and why

database-design

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.

Best Answer

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.

Related Question