multiple values in one field

theBlack8

I hope the following makes sense.

I have the following data structure:

Heading Name
Section # , Section Name
Topic #, Topic Name

Where there can be multiple Sections and multiple Topics under each section. For Example:

Heading Name (Photography)
Section 1 - Lenses
Topic 1 - topic name 1.1
Topic 2 - topic name 1.2

   Section 2 - Lighting
         Topic 1 - topic name 2.1
         Topic 2 - topic name 2.2
         Topic 3 - topic name 2.3

The number of sections and topics will vary depending on the heading. This all ends up being used to build a template form for grading students. I am using three tables to create the course template mark sheets and a fourth to record the students scores.

My problem is with the fourth table. Because the course mark sheets will vary (sections, topics), I need a solution to record the scores.

Can one field for the scores do the trick and how? or am I taking the wrong approach?

Any help/advice is appreciated.

Cheers,
Frank

eoghain

I'm not sure I understand your setup, but if you have a table that holds all of the topics then you can just link that to the scores table. If you are giving out a score for each topic that is.

Scores
-----------------------
score_id int not null
topic_id int not null
student_id int not null
score float

Simple enough now each student can have a score for each topic.

sfullman

Frank,

I'm the second guy taking a stab at this (trying to understand you) but the main points are:

1.) you DO want to have a column for Heading and then topic number, actually you might want to add a semester/quarter ID so this one table could span time effectively, and I suggest a primary key value for each one (for next point).

2) I assume you have a list of students with a primary key. You need a pivot table or "joiner" table called e.g. TopicsStudents.

The idea is you have many topics and many students. Not all students get a grade for all topics, and not all topics may apply to a group of students. But when A student takes A test on A topic, you want to record the grade

Table TopicsStudents:

Student_ID Topic_ID TestScore [DATE] [Comments,..]
1 2 100

1 2 100

1 2 100

1 2 100

2 2 100

2 2 100

2 2 100

2 2 100

Make Student_ID and Topic_ID a split primary key:

PRIMARY KEY (Student_ID, Topic_ID)

Since no student would have two grades for the same topic test.

I hope this helps, this is the only real way to handle a many to many relationship.

Sam Fullman
Compass Point Media

theBlack8

sorry about the confusing post.

thanks for the replies -you sparked some thought and I think I was on the same wavelength as Samuel prior to posting my original question.

Cheers,
Frank