Results 1 to 5 of 5

Thread: MySQL dB design

  1. #1
    Join Date
    Nov 2002
    Posts
    2

    MySQL dB design

    Hi!

    I've just finished fixing a site which was designed by someone else and the way the database was designed really messed everything up and resulted in a LOT of problems. I'm now setting up an online assessment site and I'd appreciate it if people could give me a bit of advice on database design so I don't make the same mess as the guy did on that other project I had to fix!

    Basically, I have 2 tables in my MySQL dB:

    STUDENT
    id (int)
    firstname (varchar)
    surname (varchar)
    email (varchar)

    TEST1
    number (int)
    answer1 (int)
    answer2 (int)
    ...
    answer20 (int)
    totalscore (int)

    When a student starts a test their answers will be stored in TEST1. So when they answer question 1 their answer will be stored in answer1 and so on. Then their final score is calculated and stored in totalscore. Is this ok, if anyone could give me advice or point me in the right direction I'd be greatful!


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

    Re: MySQL dB design

    1) I'd expect to see a foreign key linking the TEST1 back to the STUDENT.

    2) Having columns answer1..answer20 seems rather inflexible - what if someone devises a new test with 25 questions? It may be better to break this down into a new table:

    ANSWER
    [FK to TEST1]
    Question_No
    Answer

  3. #3
    Join Date
    Nov 2002
    Posts
    2
    Oh sorry, yeah 'id' in the student table is meant to say 'number' (its the student number of each student and acts as the foreign key). Whoops!

    The test that I am creating will never have more than 20 questions, I am designing and creating it and once it has been created it won't need to be changed. I know this isn't really the best way to design my tables... I'm getting all messed up in my head and can't work it out any more! I've tried about 5 times to normalise it properly but it doesn't seem to be working!

    Is it better to have tables like this:

    STUDENT
    number
    firstname
    surname
    email

    TEST1 (there will probably be about 7 tests, so I have one table per test)
    number
    answer0
    answer1
    ...
    answer19

    TOTALSCORES
    number
    testid (test1 in this case)
    totalscore


    I'm doing this in mysql, which is harder coz at least in access you could look at diagrams of relationships and stuff.

    I appreciate the help!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by kmf
    TEST1 (there will probably be about 7 tests, so I have one table per test)
    number
    answer0
    answer1
    ...
    answer19

    TOTALSCORES
    number
    testid (test1 in this case)
    totalscore
    Why one table per test? Surely you want:

    TEST
    number
    testid
    answer0
    answer1
    ...
    answer19
    totalscore

  5. #5
    Join Date
    Nov 2002
    Location
    Belgrade, Yugoslavia
    Posts
    1

    Post

    I'm quite aware that the quality of design should be judged depending on what do you want to do with your data, but I still don't like the one you are suggesting. Maybe you'll find this one too complex, but it certainly has some advantages. I'm new to this forum (coming from comp.databases.*), and not familiar with it's culture, so I'm posting DDL...

    CREATE TABLE student (
    studentID INT NOT NULL PRIMARY KEY,
    firstName VARCHAR(??) NOT NULL,
    surName VARCHAR(??) NOT NULL,
    email VARCHAR(??) NOT NULL);

    CREATE TABLE test (
    testID INT NOT NULL PRIMARY KEY
    description VARCHAR(??) NOT NULL);

    CREATE TABLE question (
    testID INT NOT NULL REFERENCES test(testID),
    questionNO INT NOT NULL,
    statement VARCHAR(??) NOT NULL,
    CorrectAnswer INT NOT NULL,
    PRIMARY KEY (testID, questionNO));

    CREATE TABLE exam (
    examID INT NOT NULL PRIMARY KEY,
    studentID INT NOT NULL REFERENCES student(studentID),
    testID INT NOT NULL REFERENCES test(testID),
    examDate DATETIME NOT NULL,
    totalScore INT NOT NULL DEFAULT(0));

    CREATE TABLE givenanswer (
    examID INT NOT NULL,
    questionNO INT NOT NULL,
    answer INT NOT NULL,
    PRIMARY KEY (examID, questionID));

    Note: In this model, two different tests cannot share the same question. If you want to support this feature, you'll need another table.

Posting Permissions

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