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

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

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

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

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.

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.

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