List:General Discussion« Previous MessageNext Message »
From:Brian Menke Date:April 12 2006 1:35am
Subject:RE: Student question answer schema
View as plain text  
Mikhail, thanks for your insight on this. I received one other email that
was very close to the same approach you are suggesting. Thanks for the time
and effort that you spent on this detailed email. It makes sense to me and
gets me on the right track. Thanks!! I think I have been doing a pretty good
job with normalization of my other tables for this app, but was having
mental problems with the student question answers tables. 

-Brian Menke

-----Original Message-----
From: Mikhail Berman [mailto:mberman@stripped] 
Sent: Tuesday, April 11, 2006 8:49 AM
To: JC; Brian Menke
Cc: mysql@stripped
Subject: RE: Student question answer schema

Brian,

Sound like you need to normalize your data. Quick review of what you
sent to the list shows that you need 5 tables:

* student table - each student description
* module table - each module description
* question table - each question description
* module-to-question table - what questions belongs to what table.
* student-to-question table - what student answered what question and
was it a right answer.

Unless there are NO questions that DO simultaneously belong to more than
one module, you may not have student-to-module table, because knowing
what question belongs to what module could tell you what student has
taking what module. There might be variations to this dependently on
different set of facts. For example, from your description it is not
clear if you storing data only about tests, as answered questions, or
you storing data that tells you what classes (modules) a student has or
is taking.


Best,


Mikhail Berman

-----Original Message-----
From: JC [mailto:hiep@stripped] 
Sent: Tuesday, April 11, 2006 11:22 AM
To: Brian Menke
Cc: mysql@stripped
Subject: Re: Student question answer schema

On Mon, 10 Apr 2006, Brian Menke wrote:

> Does anyone happen to know where a basic schema for tracking questions

> and answers from tests that a student has completed. I don't know why 
> I am having difficulty with this, but I can't seem to figure out how 
> to set up the tables correctly to store this information.
>
> The basics
>
> N number of students
> N number of learning modules
> Each learning module has multiple questions Each question has multiple

> answers.
>
> I need to figure out the tables to track when a student has taken a 
> module (easy)and which questions they got wrong in each module and 
> then be able to run various kinds of reports on questions that 
> students got wrong in various ways. It seems like this should be 
> simple, but I'm struggling with it. Does anyone know where an example
of this type of schema would be?
>
> Thanks for your help in advance!
>
> -Brian
>
>
>

to be efficient, you need to break down into multiple tables. otherwise
u'll end up something like this:

tblID|studentID|moduleID|questionID|answerID|
1    |111111111|aaaaaaaa|xxxxxxxxxx|yyyyyyyy|
2    |111111111|aaaaaaaa|xxxxxxxxx2|yyyyyy10|
3    |111111111|aaaaaaaa|xxxxxxxxx3|yyyyyy20|
4    |333333333|aaaaaaaa|xxxxxxxxxx|yyyyyyyy|


you get the idea, a lot of data will be repeated.  not a good idea.

jc

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Student question answer schemaBrian Menke11 Apr
  • RE: Student question answer schemaTim Lucia11 Apr
  • Re: Student question answer schemaJC11 Apr
RE: Student question answer schemaMikhail Berman11 Apr
RE: Student question answer schemaBrian Menke12 Apr