0

I'm rather newbish at managing databases but I have an idea about what I want to accomplish for my teaching needs.

I intend to store information about the level of English skills in a number of students. I plan to let every student log into the database. Once there they should be able to assess their own skills by checking a box related to a specific question.

I have 7 different categories that all contain a number of questions. On top of that each category is divided into 6 different levels. For instance, Hearing Comprehension have the levels A1, A2, B1, B2, C1, C2. Each level represents a different level of English skills. The categories all have a set of different questions. Like, "I can understand if someone speaks slowly and use a clear language."

Each question in each category also has three check boxes. One that the student can check if they can answer yes to the specific question, one for me (the teacher) to check when I confirm that they can, and one check box for the student to fill in if the question is a goal they would like to reach.

So far I've created a table for the students, a table listing all 7 categories and one table for each category containing a number of questions. I also have a "go between" table that is related to the student table and the category list, since linking them directly would be a many-to many relationship I assume.

Is this a sensible way of designing such a database?

The biggest issue I have right now is how to add the the check boxes to the mix. Should they be added as a separate table or should I put them together with the questions in the category tables? Obviously they should be connected to each student and also each question.

I haven't started on the different levels yet as I thought I should make it work with one level first and then add all the other levels.

Thank you for your patience reading all the way through. I hope I have been clear enough so that it is possible to help me.

Kenth Sundlöf
  • 1
  • This is a deceptively complex topic. In short, there are issues with your design. A table for each category and its questions is a design nightmare. As you add categories you will add tables...But rather than reading critiques of your design, your time might be better spent trying to familiarize yourself with solutions others have come up with: see Michael Durrant's answer http://stackoverflow.com/questions/540885/database-design-for-a-survey-system – gangreen Sep 01 '12 at 02:07
  • Ok, thank you for the comment. Would it perhaps serve me better to put all the questions in the same table and give each a unique category id? And as I add more levels I add a table with a list of levels with IDs that I link to each question in the bigger table. Is this making sense at all? – Kenth Sundlöf Sep 01 '12 at 10:40

1 Answers1

1

Perhaps against my better judgement I will post this as an answer...

I have created a simple database structure and inserted some data based on your requirements. You can view it here: http://www.sqlfiddle.com/#!3/54a2d/3

This structure is very rigid to the point where adding simple features would be quite difficult (where starting from scratch would be advised).

For example, without significantly modifying the schema, you could not receive a proper answer to the question "How many years have you been studying English.", you could not re-evaluate a student, the student could not clarify any answers, etc.

Remember - any time you start adding columns to accommodate new features, there should be a silent shout in your head telling you that you are (probably) doing it wrong. Read about Normalization.

Also, I don't know if checkboxes cause problems for many client/server combinations, but in MS Access with a SQL Server back end, you need to add a TIMESTAMP column so that Access can determine whether the record has changed before committing updates.

UPDATE

Added a visual representation of the tables created on the SQL Fiddle link

enter image description here

Community
  • 1
  • 1
gangreen
  • 849
  • 7
  • 9
  • Ok, I might be going about this all wrong I don't know. You talk about my design as being a nightmare so I have to assume I've designed it badly. I certainly don't mind critique as I consider it a chance to learn. What I want to accomplish is that each student can check each question in two ways. Either they check the "I-can-do-this-box", or the "I-want-to-learn-this" box. I, as the teacher, want to be able to check the "I-confirm-that-the-student-know-this" box. – Kenth Sundlöf Sep 01 '12 at 23:35
  • The aim is to be able to see each student's language level and what they want to improve so that I can create meaningful assignments targeted at each student. How would you go about creating this? – Kenth Sundlöf Sep 01 '12 at 23:43
  • Did you get a chance to take a look at the link I posted? I just added a visual representation of the schema. If I understand correctly, it is one of the simplest ways to meet your needs. If you are confident that your requirements will not change, then this design may very well work. You may never outgrow it. But if you do, I suggest you revisit this topic rather than struggle through adding features by modifying this design. As a start, try to understand why it would be difficult to add questions that do not have strict yes/no answers. – gangreen Sep 02 '12 at 00:08
  • Out of curiosity, what do you plan to build this database/application with? – gangreen Sep 02 '12 at 00:09
  • Thank you for your answers. The image above is exactly what is needed for me to understand the concept. – Kenth Sundlöf Sep 02 '12 at 14:02
  • Shouldn't the categories table be related to the questions table, though? I'm starting to think that the category table might not even be needed. Since I can just hard code the categories into the web site I intend to put the database on. Unless, of course, I want to be able to make a query for them. I was planning to make the database in mySQL and the web app in Python or PHP. – Kenth Sundlöf Sep 02 '12 at 14:08
  • The categories are related to the questions, just not directly. A category is a parent object to a categorylevel or "1:M" (1 category, many levels), a categorylevel is a parent object to a question (1:M). If you planned to reuse the categorylevels exactly as named (A1, A2, B1, B2, etc.) you could Normalize the schema a bit more by making a junction table between categories and categorylevels (M:M). Now that I think about it though, your categorylevels are probably in actuality QuestionLevels. So, it should be Categories 1:M Questions 1:M QuestionLevels (difficulty). – gangreen Sep 02 '12 at 16:53