is this a well designed table? (1 Viewer)

ariansman

Registered User.
Local time
Today, 04:32
Joined
Apr 3, 2012
Messages
157
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

You've got your good things, and you've got mine.
Local time
Today, 07:32
Joined
May 21, 2018
Messages
8,463
No. Unless those are foreign keys to the tables.
Code:
TblStudent_Scores
  studentScoreID ' automumber
  StudentID_FK ' foreign key to student PK
  SubjectID_FK ' Foreign key to subject PK
  Score
 

strive4peace

AWF VIP
Local time
Today, 06:32
Joined
Apr 3, 2020
Messages
1,003
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/
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:32
Joined
Feb 28, 2001
Messages
27,001
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

Registered User.
Local time
Today, 04:32
Joined
Apr 3, 2012
Messages
157
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

Registered User.
Local time
Today, 04:32
Joined
Apr 3, 2012
Messages
157
No. Unless those are foreign keys to the tables.
Code:
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

Immoderate Moderator
Staff member
Local time
Today, 06:32
Joined
Feb 28, 2001
Messages
27,001
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.
 

Users who are viewing this thread

Top Bottom