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:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 8:49 AM To: JC; Brian Menke Cc: mysql@lists.mysql.com 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:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 11:22 AM To: Brian Menke Cc: mysql@lists.mysql.com 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
