Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Table structures...

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Table structures...

    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.

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    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)
    Last edited by pearl2; 12-03-03 at 10:32.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Thumbs up

    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

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    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

  8. #8
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Re: Table structures...

    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

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Table structures...

    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;

  10. #10
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Re: Table structures...

    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.:

  11. #11
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Re: Table structures...

    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

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Table structures...

    The key point is this:

    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.

  13. #13
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Re: Table structures...

    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?

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Table structures...

    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.

  15. #15
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Re: Table structures...

    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
    Last edited by pearl2; 01-16-04 at 11:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •