Thread: db schema

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Posts
    8
    Rep Power
    0

    db schema


    I'm developing database schema and here's the task:

    Database should accomodate user info (name and address), test they took and answers they selected.
    So far, there are 3 tests, each has different length and format. There will be more tests, never the same in length or format!

    Here's my schema, but i'm stuck at the end:

    table user: contains user info
    UserID (pri key)
    Name
    Address

    table exams_taken: contains UserID linked to examName taken
    etID
    refUserID (foreign key UserID)
    ExamName


    //here's the question: should i create a table for each exam with name corresponding to ExamName? This doesn't seem to be normalized form.

    table exam1:
    e1ID
    refUserID (foreign key UserID)
    answer1
    answer2
    ...
    TimeSubmitted

    table exam2:
    //similar to exam1 to account for different structure.

    any help is highly appreciated,


    steve
  2. #2
  3. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    24
    One of those moments when I would say store the exam as XML and store all that XML in the exams table in a BLOB field. Essentially a table within a table.

    Unfortunately this requires even more complications....
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Location
    Washington State, USA
    Posts
    2
    Rep Power
    0
    I have a similar design problem and would like to learn more about storing XML as BLOB fields.

    I am familiar with developing XML but have never heard of BLOB or storing XML in a table field

    Could you elaborate or point me towards a resource that would help me?

    Thanks,

    Brian
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Posts
    8
    Rep Power
    0
    i've looked into blob and i'm storing entire exam in html format in a single blob field. i'm reading blob field into a string, then outputing it to the browser.

    from mysql.com:
    "A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold"


    i'm using mysql, so take a look at www.mysql.com, search for blob.

    hope this helps,
    steve
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by binky
    One of those moments when I would say store the exam as XML and store all that XML in the exams table in a BLOB field. Essentially a table within a table.
    XML documents don't at all behave like tables. They are strictly hierarchical structures.

    Anyway, this has nothing to do with the question of this thread:

    ------------------------------------------

    slava

    From what little I know of your system, I would suggest it is a mistake to create a separate table for each exam. Also, you might want to re-think the question of exactly how you represent the exam itself in the database. If you are indeed storing individual answers for all test results, isn't it better if you can also relate those answers to individual questions on any exam?

    This is exactly the sort of thing a relational database excels at, which is very difficult to deal with properly in XML.

    If I were you, I would actually have a single table listing all exams (a name and a description would be enough, unless you also want to relate them to a 'professors' table), and I would relate each exam with a foreign key to a table of exam questions. Thus, you have one big master table that has all questions to each exam, perfectly indexed. Then, as each student answers an exam question, you store the answers in a separate 'answers' table, which has the following foreign keys: student_id, exam_id, question_id. Now, you can gain meaningful statistics on all your exam data.

    Now, the question of whether you also store an HTML representation of the exam is a big question. I would consider it far wiser to output the exams dynamically, reading from the list of questions in the database. This way you can make sure that the exam the students see can never be out of sync with the answer->question_id information that you store in the database.

    If you want to be able to customise the look of each exam, then use an HTML templating system, and some CSS, or some method of separating the HTML shell from the dynamic output of the actual questions.

    Furthermore, for something like this, where people's grades (and hence, their futures) can depend on the data, I would seriously recommend the following:

    1. Do some serious study on database design fundamentals, especially in the area of normalization, integrity, data independence, etc... It is obvious from your first question that you are a little unsure about these concepts. Time to hit the books! If you really want to get serious, I would recommend studying Introduction to Database Systems, by C.J. Date. But if that is too heavy for you, at least read Database Design for Mere Mortals by Michael J. Hernandez, as well as a good book on SQL.

    2. Use a database system that can really handle serious data integrity, as well as more advanced logical abililities, such as views, stored procedures, constraints, and triggers. I (and many others) find MySQL to be seriously lacking in these areas. Yes, it's fast, but can you really make your data do exactly what you want? Can your trust your data? (by the way, watch out for MySQL's date/time types. Dates are important in this sort of context, and MySQL doesn't really have proper date/time constraints.). I would recommend you look into such DBMS systems as PostgreSQL, Interbase/Firebird, or go with a commercial DBMS such as SyBase, DB2, etc... (even Microsoft SQL server is better)

    I don't mean to sound too severe here, but as a former teacher, and as a serious database designer, I think you owe it to those involved to do this right. The database is the most important part of any application environment. Nothing else in the system matters if your data itself can't be trusted.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    24
    Theoretically a table is something organised in rows under a set of column headings. Some XML that works as:

    <messages>
    <message id='1' poster='binky' />
    <message id='2' poster='rycamor' />
    </messages>

    Could be read as a table called messages with two fields:
    id,
    poster.

    A while back I started an XMLDB system that could handle an XML file as a DB, using SQL commands, 'tis attached.
    Attached Files
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Posts
    8
    Rep Power
    0
    i've done some research and primary development. Here's what i have:

    user info table:
    user_id (pri key)
    user_pass
    user_name
    ... (other personal info)


    exam info table:
    exam_id (pri key)
    exam_name
    exam_answers (string of the answers which can be tokenized for grading)
    exam_questions (html representation of the exam. exam will NEVER change, and i need a working model right away. i will eventually separate questions into a table.... and apply css)


    user exam table:
    user_id (foreign key)
    exam_id (foreign key)
    user_answers (answers collected from user input)
    user_grade (grading is done by middlewear)
    submit_time (timestamp)



    any suggestions?

    thanks,
    steve

IMN logo majestic logo threadwatch logo seochat tools logo