Forms and Tables new questions

[Guest]

I have realized that I need 3 tables for my school database.
My goal for this database is to be able to allow a student to take more than
one class (different class, not repeating) and not wanting to enter the
student's personal information again. Currently, If I add a student to a
class I would see the student as another record in the form. That means I
have to re-enter his or her info again. This is What I am planning to do.
Please let me know if it's correct.

1. Student table
StudentID PK
other personal info

2. Class table
ClassID pk
other class info etc..

3. stduentclass table
StudentID PK
ClassID PK

Now, I need to know if I should put the grades field into the third table or
the class table? Why?

Also, can you comment on structor the database. Is there another better
solution?
Thank you very much.

 

[Michael J. Strickland]

I think you only need two tables.

1. "Students" table with fields StudentID, Name, Address,Phone, etc...
This table has one record for each student.

2. "Classes" table with fields: ClassID, StudentID,
Grade1,Grade2,Grade3,Grade4 etc....

This table has one record for each student in each class (e.g. with 10
students each taking five classes, # records = 10 x 5 = 50 records).


Then make a query with the StudentID fields of each table joined.
By adjusting your query criteria, you may select:

1. List of all students with info on each student.
2. List of all students in any one class( w/grades)
3. List of all classes any one student is taking.
4. List of all students in all classes.


This will allow you to select

 

[Pieter Wijnen]

Nope, as a student belongs to several classes & classes are attended by
several students you do have a many to many relation. I would also put the
grades in he join table as one student in one class gives one grade.
The form grade1..gradeX is a denormalization & would make a nightmare to add
grade fields...

1. Student table
StudentID PK
other personal info

2. Class table
ClassID pk
other class info etc..

3. stduentclass table
StudentID FK
ClassID FK
Grade (value)



HTH
Pieter