Table structures...

pearl2 · #1 (**permalink**) 12-03-03, 06:39

Hey,

(Note: I posted at the MySQL category before realising this is a more appropriate category to post my question.)

I'm at the stage of planning for a database to store scores of test marks for English, Math and Science for a class of students.

Among the tables I've planned are three tables corresponding to the three subjects to store the test id, the score, the question numbers in which wrong answers were given and the date the test is taken: (note that the member_id is a foreign key in the three tables below but a primary key in a student_tbl):

english_tbl
Member_id (FK)
Test_id
Score
Wrong question IDs
Date Taken

math_tbl
Member_id (FK)
Test_id
Score
Wrong question IDs
Date Taken

science_tbl
Member_id (FK)
Test_id
Score
Wrong question IDs
Date Taken

Please pardon me if my question sounds elementary. I'm new to databases and although I've read a couple of books on the subject, I'm still groping in the dark when it comes database design.

andrewst · #2 (**permalink**) 12-03-03, 07:53

This is a common mistake when new to database design. These 3 tables should be replaced by one like this:

table Test:
subject
Member_id (FK)
Test_id
Score
Wrong question IDs
Date Taken

The extra column "subject" is to indicate whether the test is for English, Maths or Science. It could either have a check constraint to limit the values, or it could be a foreign key to another table Subject.

There are a number of advantages to this approach:
1) You can easily handle new subjects (e.g. Spanish) without having to clone another table.
2) You can easily query across courses, e.g. what tests did member X take on date D?

Another issue is your multi-valued column "Wrong question IDs". This violates "first normal form", which means a column should not contain more than one value per row. Again you need another table:

table Test_error:
test_id (FK to Test)
question_id
primary key: (test_id, question_id)

This table should have a row for each question answered wrongly.

pearl2 · #3 (**permalink**) 12-03-03, 08:34

Thanks, andrewst!

I posted a modified one that I think is pretty similar to yours after I got some feedback from Matt.

May I refer you to the Test_error table below:

table Test_error:
test_id (FK to Test)
question_id
primary key: (test_id, question_id)

I need a way for the member to access the questions that he or she has done wrong for a particular test. Will inserting member_id into Test_error work?

table Test_error:
test_id (FK to Test)
question_id
member_id
primary key: (test_id, question_id)

andrewst · #4 (**permalink**) 12-03-03, 09:23

That isn't necessary, and would be another unwanted denormalisation, assuming test_id is the primary key of Test. To make that query, do this:

select e.test_id, e.question_id
from test_error e, test t
where e.test_id = t.test_id
and t.member_id = 123

pearl2 · #5 (**permalink**) 12-03-03, 09:44

Wow ...

There's so much I'm missing...

So we're able to use that query to find out the questions a member has done wrongly because we've a test_id value that's used to link up the Test table and the Test_error table. Without that linkage, it would not be possible to perform such a query.

Am I on the right track?

Thanks so much for your every enlightening advice

andrewst · #6 (**permalink**) 12-03-03, 09:49

Yes, linking up tables (aka "joining") in queries via a common column (or columns) - the foreign key - is the way to query a relational database.

pearl2 · #7 (**permalink**) 12-03-03, 10:06

Thanks once again, andrewst!

I'm reviewing my table over and over again as I want to be fairly certain I've got it almost there.

I've a main (parent?) table named 'profile' that's shown (partially) as follows:

profile_tbl
-member_id (pk)
-first_name
-last_name
-school
.
.
.

I've a dozen other (child) tables joined to the parent table via member_id. Is that a normal thing to do?

The one below is one of the several sub-tables:

login_trans_tbl
-member_id (fk)
-encrypted pass
-cookie text
-no. of failed logins

pearl2 · #8 (**permalink**) 01-12-04, 23:31

Quote:

Originally posted by andrewst
This is a common mistake when new to database design. These 3 tables should be replaced by one like this:

table Test:
subject
Member_id (FK)
Test_id
Score
Wrong question IDs
Date Taken

The extra column "subject" is to indicate whether the test is for English, Maths or Science. It could either have a check constraint to limit the values, or it could be a foreign key to another table Subject.

There are a number of advantages to this approach:
1) You can easily handle new subjects (e.g. Spanish) without having to clone another table.
2) You can easily query across courses, e.g. what tests did member X take on date D?

Another issue is your multi-valued column "Wrong question IDs". This violates "first normal form", which means a column should not contain more than one value per row. Again you need another table:

table Test_error:
test_id (FK to Test)
question_id
primary key: (test_id, question_id)

This table should have a row for each question answered wrongly.

Hi Andrew,

Hope you can help me with a question on the second table. How would I be able to tell which member did which question wrong? I was thinking of a situation where more than one member got the same question(s) wrong for the same test.

Thanks in advance

andrewst · #9 (**permalink**) 01-13-04, 05:05

Join the test_error table to the test table by the foreign key column(s) - e.g.:

select t.member_id, e.question_id
from test t, test_error e
where t.test_id = e.test_id;

pearl2 · #10 (**permalink**) 01-13-04, 06:50

Quote:

Originally posted by andrewst
Join the test_error table to the test table by the foreign key column(s) - e.g.:

select t.member_id, e.question_id
from test t, test_error e
where t.test_id = e.test_id;

Thanks

I'll try that out and keep you posted.:

pearl2 · #11 (**permalink**) 01-16-04, 09:42

Quote:

Originally posted by andrewst
Join the test_error table to the test table by the foreign key column(s) - e.g.:

select t.member_id, e.question_id
from test t, test_error e
where t.test_id = e.test_id;

Hi Andrew,

I'm a little stuck here...

I've pasted the table definitions for scores and wrongs below:

Code:

CREATE TABLE scores (
      member_id MEDIUMINT UNSIGNED NOT NULL,
      subject ENUM('English', 'Mathematics', 'Science') NOT NULL,
      test_id TINYINT UNSIGNED NOT NULL,
      score TINYINT UNSIGNED NOT NULL,
      date DATE NOT NULL,
      PRIMARY KEY (member_id, subject, test_id),
) TYPE=INNODB;

CREATE TABLE wrongs (
      test_id TINYINT NOT NULL,
      question_id TINYINT NOT NULL,
      PRIMARY KEY (test_id, question_id),
) TYPE=INNODB;

Here's what I'm doing with the database:

member_id 1 takes English Test 1 and scores 80% (question 2 wrong)
member_id 1 takes Math Test 1 and scores 60% (question 1 and 3 wrong)
member_id 2 takes English Test 1 and scores 80% (question 3 wrong)

The information is stored in the two tables as follows

Code:

table_scores

member_id subject test_id score date 
1               English  1         80     2004_16_01
1               Math     1         60     2004_16_01
2               English  1         80     2004_16_01

table_wrongs

test_id question_id
1         2
1         1
1         3
1         3

Given the above, how do I query the wrongs table if I want to know which member did what questions wrong in a particular test? Or let's say the member can retake the test and if he scores 100% the second time round, how do I delete his wrong question_ids from the wrongs table? Note that in my implementaton, tests for Science and Math also start from test_id 1.

Hope you can enlighten me...

Many thanks in advance

andrewst · #12 (**permalink**) 01-16-04, 10:01

The key point is this:

Quote:

Note that in my implementaton, tests for Science and Math also start from test_id 1.

This means that test_id is not a unique key for a test as I had thought it was. You need the subject as well, so add subject to the table_wrongs table and make it part of the foreign key. Then join on both columns.

pearl2 · #13 (**permalink**) 01-16-04, 10:23

Quote:

Originally posted by andrewst
The key point is this:

This means that test_id is not a unique key for a test as I had thought it was. You need the subject as well, so add subject to the table_wrongs table and make it part of the foreign key. Then join on both columns.

Cool, thanks!

Code:

CREATE TABLE wrongs (
      test_id TINYINT NOT NULL,
      question_id TINYINT NOT NULL,
      subject ENUM('English', 'Mathematics', 'Science') NOT NULL,
      PRIMARY KEY (test_id, question_id),
) TYPE=INNODB;

But what if I need to do a delete from the wrongs table when the person scores 100% (no wrongs) the second time round? How would the delete query looks like?

The following is my attempt at a query to delete the wrong questions done by member_id 2 during the first attempt at the test:

Code:

DELETE FROM wrongs
  WHERE test_id='1'
  AND subject='English'
  AND member_id='2'

which requires having member_id in the wrongs table as well. Am I on the right track?

andrewst · #14 (**permalink**) 01-16-04, 10:26

Yes, you are on the right track. The "wrongs" table must contain sufficient information to link it back to the "scores" table - via a foreign key.

pearl2 · #15 (**permalink**) 01-16-04, 10:31

Quote:

Originally posted by andrewst
Yes, you are on the right track. The "wrongs" table must contain sufficient information to link it back to the "scores" table - via a foreign key.

Just a confirmation...

My modified scores and wrongs tables are as follows:

Code:

CREATE TABLE scores (
      member_id MEDIUMINT UNSIGNED NOT NULL,
      subject ENUM('English', 'Mathematics', 'Science') NOT NULL,
      test_id TINYINT UNSIGNED NOT NULL,
      score TINYINT UNSIGNED NOT NULL,
      date DATE NOT NULL,
      PRIMARY KEY (member_id, subject, test_id),
) TYPE=INNODB;

DROP TABLE IF EXISTS wrongs;
CREATE TABLE wrongs (
      member_id MEDIUMINT UNSIGNED NOT NULL,
      test_id TINYINT NOT NULL,
      question_id TINYINT NOT NULL,
      subject ENUM('English', 'Mathematics', 'Science') NOT NULL,
      PRIMARY KEY (member_id, test_id, question_id),
) TYPE=INNODB;

Is that right?

Thanks man! I appreciate your help greatly

Internet Services

Web Development

Digital Marketing

Consumer Tech