Restructuring Tables

[Glenn E. White]

To Whom It May Concern

I need some advice or direction. I currently have a database that has one main table that contains test scores for various students. In addition, I have another table that contains the students and their assigned teacher

The database was not originally designed for the possibility of additional teachers. Since, this has occurred, I must now change the design to facilitate multiple teachers for each student. My goal is to have a record of each student’s test with the teacher that administered the test at that time

Please tell me if the following it the optimal way to restructure the data

Test Score Table
Test ID: P
Student Nam
Test Dat
Test Score
Test Comment

Students Table
Student ID
Student Nam
Teacher Nam
Active Status: Yes/N

Test History Table
Test History ID: P
Test I
Test Dat
Student Nam

Teachers Table
Teacher ID: P
Teacher Nam

In addition, the form that popluates the main table must retreive the student's current teacher. Any and all advice, help, or comments will be greatly appreciated

Sincerely
Glenn E. Whit

 

[PC Datasheet]

Glenn,

I recommend the following:

Students Table:
Student IDK
Student Name
Active Status: Yes/No

Teachers Table:
Teacher ID: PK
Teacher Name

Test Subject Table:
TestSubjectID: PK
TestSubject

Test Score Table:
TestScoreID: PK
TestSubjectID
StudentID
TeacherID
Test Date
Test Score
Test Comments


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

To Whom It May Concern:

I need some advice or direction. I currently have a database that has one

main table that contains test scores for various students. In addition, I have
another table that contains the students and their assigned teacher.

The database was not originally designed for the possibility of additional

teachers. Since, this has occurred, I must now change the design to facilitate
multiple teachers for each student. My goal is to have a record of each student
's test with the teacher that administered the test at that time.

Please tell me if the following it the optimal way to restructure the data:

Test Score Table:
Test ID: PK
Student Name
Test Date
Test Scores
Test Comments

Students Table:
Student IDK
Student Name
Teacher Name
Active Status: Yes/No

Test History Table:
Test History ID: PK
Test ID
Test Date
Student Name

Teachers Table:
Teacher ID: PK
Teacher Name

In addition, the form that popluates the main table must retreive the

student's current teacher. Any and all advice, help, or comments will be
greatly appreciated.

 

[Andrew Smith]

Each table should be about only one entity - so the student table is about
the STUDENT, and does NOT contain anything about the TEACHER. The TEST table
is about the TEST, and knows nothing about the STUDENTs etc.

Tables are related by key fields, so the StudentName, for example, is stored
ONLY in the Student table. Any related tables store on the StudentID.

Assuming that each test generates only ONE score for each student, I would
have thought something along the lines of:

tblStudent:
StudentID (PK)
StudentFirstName
StudentLastName
Active (Yes/No)

tblTeacher
TeacherID (PK)
TeacherFirstName
TeacherLastName

tblStudentTeacher
StudentTeacherID (PK)
StudentID (FK)
TeacherID (FK)
DateAssigned
(NB You could use StudentID and TeacherID as the PK, but only if a
student can never be assigned the same teacher more than once, which I doubt
you can guarantee. To find the current teacher, just create a query that
finds the teacher with the highest value of DateAssigned).

tblTest
TestID (PK)
TestDate
TestSubject

tblTestScores
TestID (FK, part of PK)
StudentID (FK, part of PK)
TestScore
TestComments

To Whom It May Concern:

I need some advice or direction. I currently have a database that has one

main table that contains test scores for various students. In addition, I
have another table that contains the students and their assigned teacher.

The database was not originally designed for the possibility of additional

teachers. Since, this has occurred, I must now change the design to
facilitate multiple teachers for each student. My goal is to have a record
of each student's test with the teacher that administered the test at that
time.

Please tell me if the following it the optimal way to restructure the data:

Test Score Table:
Test ID: PK
Student Name
Test Date
Test Scores
Test Comments

Students Table:
Student IDK
Student Name
Teacher Name
Active Status: Yes/No

Test History Table:
Test History ID: PK
Test ID
Test Date
Student Name

Teachers Table:
Teacher ID: PK
Teacher Name

In addition, the form that popluates the main table must retreive the

student's current teacher. Any and all advice, help, or comments will be
greatly appreciated.

 

[Glenn E. White]

Thanks for your advice

----- Andrew Smith wrote: ----

Each table should be about only one entity - so the student table is abou
the STUDENT, and does NOT contain anything about the TEACHER. The TEST tabl
is about the TEST, and knows nothing about the STUDENTs etc

Tables are related by key fields, so the StudentName, for example, is store
ONLY in the Student table. Any related tables store on the StudentID

Assuming that each test generates only ONE score for each student, I woul
have thought something along the lines of

tblStudent
StudentID (PK
StudentFirstNam
StudentLastNam
Active (Yes/No

tblTeache
TeacherID (PK
TeacherFirstNam
TeacherLastNam

tblStudentTeache
StudentTeacherID (PK
StudentID (FK
TeacherID (FK
DateAssigne
(NB You could use StudentID and TeacherID as the PK, but only if
student can never be assigned the same teacher more than once, which I doub
you can guarantee. To find the current teacher, just create a query tha
finds the teacher with the highest value of DateAssigned)

tblTes
TestID (PK
TestDat
TestSubjec

tblTestScore
TestID (FK, part of PK
StudentID (FK, part of PK
TestScor
TestComment

To Whom It May Concern

main table that contains test scores for various students. In addition,
have another table that contains the students and their assigned teacherteachers. Since, this has occurred, I must now change the design t
facilitate multiple teachers for each student. My goal is to have a recor
of each student's test with the teacher that administered the test at tha
time

Test ID: P
Student Nam
Test Dat
Test Score
Test Comment
Student ID
Student Nam
Teacher Nam
Active Status: Yes/N
Test History ID: P
Test I
Test Dat
Student Nam
Teacher ID: P
Teacher Nam

student's current teacher. Any and all advice, help, or comments will b
greatly appreciated

 

[Glenn E. White]

Thanks for your advice

----- PC Datasheet wrote: ----

Glenn

I recommend the following

Students Table
Student ID
Student Nam
Active Status: Yes/N

Teachers Table
Teacher ID: P
Teacher Nam

Test Subject Table
TestSubjectID: P
TestSubjec

Test Score Table
TestScoreID: P
TestSubjectI
StudentI
TeacherI
Test Dat
Test Scor
Test Comment


-
PC Datashee
Your Resource For Help With Access, Excel And Word Application
(e-mail address removed)
www.pcdatasheet.co

To Whom It May Concern

main table that contains test scores for various students. In addition, I hav
another table that contains the students and their assigned teacherteachers. Since, this has occurred, I must now change the design to facilitat
multiple teachers for each student. My goal is to have a record of each studen
's test with the teacher that administered the test at that time

Test ID: P
Student Nam
Test Dat
Test Score
Test Comment
Student ID
Student Nam
Teacher Nam
Active Status: Yes/N
Test History ID: P
Test I
Test Dat
Student Nam
Teacher ID: P
Teacher Nam

student's current teacher. Any and all advice, help, or comments will b
greatly appreciated

 

[Andrew Smith]

I should have put "StudentTeacherID" in tblTestScores, not StudentID.

You may be able to manage without tblStudentTeacher, and just put StudentID
and TeacherID in tblTestScores, which is the suggestion made by PCDatasheet.
If you do it this way you won't be able to look up the teacher assigned to a
particular student unless you have done a test - this may or may not be
important to you.