is this a well designed table?

[ariansman]

I am going to record student’s scores in a table like the following :
Student names are looked up from student_list table and subjects from subject_list table.

students_cores table:

ID, studentname, subject, score

1, john, mathematics, 80
2, john, physics, 87
3, john, history, 75
4, john, biology, 90
5, john, chemistry, 73
6, john, mathematics, 84
7, john, literature, 69
8, Jack, mathematics, 79
9, Jack, physics, 58
10, Jack, history, 80
11, Jack, biology, 93
12, Jack, chemistry, 79
13, Jack, mathematics, 71
14, Jack, literature, 76
.
.
.
.
Is it a good idea to have the above table? Is there a wiser way to record each student record for a given subject?
thank you

 

[MajP]

No. Unless those are foreign keys to the tables.

TblStudent_Scores
  studentScoreID ' automumber
  StudentID_FK ' foreign key to student PK
  SubjectID_FK ' Foreign key to subject PK
  Score

 

[strive4peace]

hi ariansman,

As Maj said, it is best to separate different types of objects into their own tables.

I write a monthly article about Access for a magazine. Last September, I started a mini-series using academic data. Each article has a download database. During the course of these articles, I changed table structures, relationships, and field names to the database for the November article. Each article has a download database.

September 2019
https://sfmagazine.com/post-entry/september-2019-access-undoing-updates/

October 2019
https://sfmagazine.com/post-entry/october-2019-access-data-structure/

November 2019
https://sfmagazine.com/post-entry/november-2019-access-using-a-subreport-multiple-times/

 

[GinaWhipp]

Hmm, this might help...

Data Model - Training with Attendance

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

 

[The_Doc_Man]

The only issue about which I would inquire is whether you need a date as part of the table. How often do you have to give out grades?

 

[ariansman]

The only issue about which I would inquire is whether you need a date as part of the table. How often do you have to give out grades?

they get the grade every 3 months. I missed another column. with the respective date

 

[ariansman]

No. Unless those are foreign keys to the tables.

TblStudent_Scores
  studentScoreID ' automumber
  StudentID_FK ' foreign key to student PK
  SubjectID_FK ' Foreign key to subject PK
  Score

thank you for the instruction.
well. when i look at the table i see a student name such as "John" or a subject such as "math" is repeated too many. These are foreign keys. I was still wondering if we could avoid having too many studentID and SubjectID in the table.
However i am also thinking since John/math/78/examdate is a unique chain of data, maybe we have to keep the repeated StudentID and SubjectID in the table.

P.S: i forgot a column for the exam_date. but it will be there.

 

[The_Doc_Man]

You ask (or at least imply) a perfectly valid question. You have observed that topics are repeated often, and names are repeated often. Since you did nothing about it, I must assume you are not intimately familiar with normalization. TECHNICALLY, you should have three tables here.

1. Students: StuID (autonumber OK), FirstName, MiddleName, LastName, Suffix, other info ONLY ABOUT THE STUDENT
2. Classes: ClsID (autonumber OK), ClassName, ClassLevel, other info ONLY ABOUT THE CLASS
3. Grades: GrID (autonumber OK), StuID, ClsID, ClGrade, ClDate

Then you would establish relationships between Students:StuID and Grades: StuID, and between Classes: ClsID and Grades: ClsID. Both relationships would be of the type: Every Student (or every class) and only those Grades matching that student (or class). Then if you built multi-table queries, the query builder could see your relationships and implement them in the query for you.

What I did there was called "Normalization." If you choose to read on this topic then do the following: When searching this forum for things to look up, seek "Normalization." BUT if searching the Internet, seek "Database Normalization" - because the word Normalization also applies to diplomacy, math, chemistry, and a few other topics as well. Further, if searching the general Internet, pick .EDU sides first because usually the .COM sites have something to sell you and will push THEIR version. The .EDU sites will have less bias in what they write.