Tables & Forms John Vincent, Doug, Rick

[Guest]

Hello all,

I recenty designed a school database with three tables.

tstudent table
StudentID PK
Lname
Fname
etc...

tclass table
ClassID PK
StartDate
EndDate
etc...

tstudentclass table
StudentID PK
ClassID PK
FundingSource Combo box
Enrolled YES/NO datatype

Now, I need to decide if I should put the grade's fields into the
studentclass table or a seperate table called grades(the grades are
Letter(A, B, ..). Here is the example:

I have Gr1, Gr2, Gr3.................Gr18 and a FinalGrade. Gr1 to Gr18
are tests and homeworks. However, FinalGrade is the final grade for the
course. Also keep in mind that the Gr's will vary on each class. For
example Math 101 might only require the fields Gr1, Gr2, Gr3. But Math 102
might require Gr1 to Gr6. Can you please tell me in detail how I should
design the structor of the table too. Step by step would be great! Thank
you.

 

[Douglas J. Steele]

John, have you looked at any of the references I gave you to learn database
normalization?

You need a fourth table to store the grades.

tstudentclassgrade table
StudentID PK
ClassID PK
GradeCD PK
Grade

GradeCD will be your G1, G2, G3 for as many as you need. Grade will be the
actual grade for that assignment/test/whatever. If there are 6 grades for a
particular course, you'll have 6 rows per student in tstudentclassgrade. If
there are 10 grades, you'll have 10 rows per student.

One approach would be to store the final grade as an additional field in
tstudentclass, another would be to store it in a fifth table. Others might
disagree, but I don't feel it belongs in tstudentclassgrade because it
represents a different type of grade than the rest of the grades being
stored in that table.

 

[Douglas J Steele]

Another resource I can point you to is
http://www.databaseanswers.org/data_models/index.htm

http://www.databaseanswers.org/data_models/student_assignments/index.htm
seems applicable to your situation.

 

[Guest]

Doug,

I got the idea of having the fourth table. But now, I'm still confused
about the gradecd, and grade. I dont understand how this will link to my
other tables. Can you demonstrate this by some kind of drawing or in
details. Thank you.

For example:
tstudent table
StudentID PK
Lname
Fname
etc...

tclass table
ClassID PK
StartDate
EndDate
etc...

tstudentclass table
StudentID PK
ClassID PK

tstudentclassgrade table
StudentID PK
ClassID PK
GradeCD PK
Grade
Now, how can I join them in the relationship window?

 

[Guest]

Doug,

Currently I created 18 fields Gr1 Gr2 ....Gr18 in the tstudentclass. I
don't get the picture of why I cannot use this procedure???

StudentID ClassID Gr1 Gr2 Gr3
.........................Gr18
1 History101 A B C
D
2 Biology101 C B A
A

 

[John Vinson]

Doug,

Currently I created 18 fields Gr1 Gr2 ....Gr18 in the tstudentclass. I
don't get the picture of why I cannot use this procedure???

StudentID ClassID Gr1 Gr2 Gr3
........................Gr18
1 History101 A B C
D
2 Biology101 C B A

You can...

until a class needs 20 grades. What do you do then? Redesign your
table, all your queries, all your forms, all your reports?

Or until you want to find grade point averages. It's very simple to
total *down records*, it's much harder to total *across fields*.

Or until you want to find all the F grades in any subject for any
student, so you can intervene and help the kids. You now have 18
fields to search rather than one.

You CAN use this spreadsheet approach; it's legal, it won't cause the
computer to crash, it's even convenient for data entry.

But it's very limiting and inefficient. Forty years of database
research have proved over and over again that "fields are expensive,
records are cheap".

John W. Vinson[MVP]

 

[Douglas J. Steele]

http://www.accessmvp.com/djsteele/john.gif