Greetings, dBforums. I have what is probably a stupid question, but I'm new at this, so please accept the pre-emptive apologies of an application developer who's been forced into dB architecture design largely against his will. (Though, it's quite interesting thus far!)

I'm developing a SQL Server 2005 dB structure designed to hold data for exams served online through ASP.NET pages. I'm also designing the pages, so I have total control over what the dBs look like. I currently have a database of student info tables, keyed to a "StudentID" identity, and a database of exam info, keyed to an "ExamID". This works well for inputting, tracking and updating lists of students, exam and course numbers, objectives, question text, answer text, and the like.

Where I'm running into a mental block conceptually is with the information stored for a specific instance of a student taking an exam. I need to track values such as DateTime of the beginning and end of the session, time spent on each question, which questions were displayed, etc.

Knowing what I know of dB structure, which I fully admit is next to nothing, I can't think of a way to do this without dynamically generating a new table for each instance of a given exam. If I do this, then once I hit production, I'll end up with an ExamInstances database with hundreds of tables keyed to ExamInstanceIDs, and more being generated every day. I don't know if that's likely to cause problems, but it seems like a very inelegant way to do things.

And therein lies my question: what's the best way to do this? Is there an accepted standard for this sort of thing, or can anyone offer any advice? Being new at this, I'm crossing my fingers that there's something obvious I'm missing. If so, please don't be shy in letting me know.

Thanks in advance for any advice.

I would go for an intermediate table between the STUDENTS and EXAMS, call it STUDENTS_EXAMS.

This intermediate table would store the studentid FK and the examid FK and any other related data like exam start date, end date etc etc.

What this table allows for is a student can take 1 or many exams and an exam can be taken by 1 or many students.

This way you can store all of your different students exams in one table.

Hope this helps

Matt

Two fkeys? Er...you can do that? That makes life rather easier. Told you I was new at this. My eternal gratitude, jedimatt.

When someone gives you a book, do you get a new shelf to put it in?

The same idea applies to tables. You put the exams in the exam table. Putting it in a separate table is called table splitting by exam type.

Right, I get that now. The point I was missing was that it's possible to include both ID keys in a combined table of results. As it stands, I won't have to split them at all; exam type is stored as a column value in the exam data tables, and the exam types aren't different enough to require a separate structure.

Thanks for the advice, I'll keep it in mind for future applications.

No worries, glad it helped you out

Ok, so I've created a new table called ExamResults. It has a primary key of ExamRes_ID, and foreign key columns for StudentID and ExamID. In designing the rest of the table, though, I remembered another reason I thought I needed multiple tables in the first place: each exam has a different number of questions, with various values that need to be tracked for each.

So, to track data for each question —answer chosen, answered correctly, time spent viewing that question, and so on — it seems like I would either need to create a different ExamResults table for every possible number of questions (so I can have Q1Response, Q1Correct, Q1Time . . . Q[X]Response, etc)...or else leave it as a single table, create a number of columns to match the maximum number of questions, and pass in nulls for exams that don't have that many questions. Once again, neither of these seems optimal.

Would someone be kind enough to tell me what I'm missing now?