MySQL table layout

paranoid times · 05-23-2012, 05:03 PM

So this is a design question. I am building a program that will have students grades in it. Each student should be able to provide the program a unique id, then will be presented with all the assignments and assosiated scores they have thus far completed.

Keep in mind I am very new to rational database layout. Which is what the question is about. My idea was to create several tables. One for tests, one for homework, and one for lab assignments. The reason being that each of these groups has a different weight to the overall grade (Tests are 60% Labs 30%, and homework 10%) thus in presenting the student with their overall grade each section needs to be multiplied down accordingly. Then have another table which notes the percentage value of each type of assignment. Finally there would be a table connecting the student name with their id.

I believe I have a misunderstanding in how SQL databases work. Is it the case that there is no inherent order to the database. Which is to say that the "first" row is arbitrary? I ask because I was looking for a way to link the unique id field from the user information table over to the other tables. Such that if there was another student that added, they could simply be added in one table, and the database would automatically know about them in the other tables. I could not find such a way to do this. Would it be better to simply have the id of each student added to be copied over to each of the relevant tables?

My theory of operation is a student provides their id. The script first collects their scores from the test table, giving them their scores of each test, then their average score for their tests (in %). This would be repeated for the homework table, and the lab table. After this each % value would be multiplied down (by .6 for tests by .3 for labs and by .1 for homework) the result added together and then presented as the total score overall for the class.

Is this a reasonable method of doing this? Or am I missing part of the idea and making decisions that will doom me as I work on this or modify it later?

Thank you for any input.
Michael

CTM · 05-24-2012, 04:34 AM

Quote:

Originally Posted by paranoid times

I believe I have a misunderstanding in how SQL databases work. Is it the case that there is no inherent order to the database. Which is to say that the "first" row is arbitrary? I ask because I was looking for a way to link the unique id field from the user information table over to the other tables. Such that if there was another student that added, they could simply be added in one table, and the database would automatically know about them in the other tables. I could not find such a way to do this. Would it be better to simply have the id of each student added to be copied over to each of the relevant tables?

Relational databases are underpinned by set theory, so no, you shouldn't rely on the default ordering of rows in tables, just as you shouldn't in sets. (If you need a specific ordering on the rows in a table, use ORDER BY.) What you're looking for is a way of linking a row in your student table with rows in the marks tables: that can be achieved with the use of foreign keys. Assuming student IDs are unique, your foreign key here would be the student ID, as you correctly identified.

The rest of your plan seems sensible.

bertlef · 05-24-2012, 11:05 AM

You can sort the results and present them as you wish, that is not a problem.
I did not understand how your tables are structured, you need at least 2 (but you can work with more), one is for the students information and another one for their assignments, you can set the respective values (by .6 for tests by .3 for labs and by .1 for homework) either in another table or in the programming code.

Can you please post how are your tables structured?

paranoid times · 05-24-2012, 11:53 AM

bertlef: My idea of how to do this would be to have a minimum of three tables. Those would be as follows:
1: student_info this would be the parent table of the marks tables. I suspect it would contain only two columns the unique id of each student (As CTM explained this would be the parent column of the foreign keys for the marks tables), and their name (It could potentially contain other student specific information)
2: A table listing the marks tables, and their relative weights. This would also probably only have two columns. The first having the names of the various marks tables (exams, homework, labs, etc). The next having how much each section is worth to the class. (Which is to say exams might be worth 60% of the overall grade, so scoring 100% on all of the exams, but 0% for the rest of the class content would only yield a 60/100 for the final class grade)
3: There would have to be at least one marks table for this to work, but there could be as many as the professor wants. My approach to this was just to setup the program such that more tables could be added as wanted by the professor (user).

Does that make sense for how I am planning on laying the tables out. As mentioned earlier, while I think this will all work fine, I'm mostly just asking because I haven't worked much with this and might be missing a terrible error that I am making which will haunt at a future time.

There is one more thing that I thought of. Each assignment will have an individual maximum score. All the exams would be listed in the exam table. Their overall weight is determined from the weights table. However each individual exam might be worth different amounts. eg. Exam one is worth 40 points, exam two is worth 50 and exam 3 is worth 60. The percentage scores of each exam still need to be presented to the student user. Given that there is no single maximum. Each exam (homework, lab, etc) assigned needs to have its own maximum score associated with it. My solution to this was to create a special "student" that would have their score listed as the maximum. The scripts would then take the user's mark and divide it by the perfect "student" mark to give a percentage grade. This however feels messy to me, it seems to me that I shouldn't have a table where some rows have one kind of data, and other rows have metadata describing the rest of them. Is there a cleaner way to do this? (I suppose the other option that comes to mind is I make a table with the name of each assignment in one column, and its maximum score in another column. Then link the assignment names to the column names of the various mark tables? That sounds kind of messy too.)

Thank you both very much for your help. It is all starting to make sense to me.

bertlef · 05-24-2012, 12:58 PM

You are in a very good path, but it will take a few more tables.
What are you using for the programming interface php?

You could do this in an excel or calc sheet actually.

This would be like the pseudoCode for the database (at least a first draft):

students: A list with all the students
- sid: unique and auntoincrement
- name: string with the students name

assignmentTypes: The types of assignments
- atid: integer, unique id for each assignment type
- name: string with the name of the assignment type

assignments: A list of all the assignments like homework 1, lab 2 and so on
- aid: integer with the unique id for this assignment
- name: string with the name of this assignment
- atid: comes from assignmentTypes
- weight: integer with the amount of points this assignment is worth (maximum weight)

marks: Possible marks and their weights
- name: a string with the name of the assignment type (labs, exams and so on)
- weight: a numeric value with the possible options (0,6, 0,8 and so on)

registry: Official registry record of the assignments and scores for each student
- rid: integer with the unique identifier for this record
- atid: Comes from atid in assignmentTypes
- sid: Comes from sid in students
- aid: comes from assignments
- sid: comes from sid in students
- score: official score received

The final results may be retrieved either with an additional table where each calculation is done and registered or in the programing part of it.

Did I get it right? Too messy?

paranoid times · 05-25-2012, 06:44 PM

Thank you again for your help. I've modified your suggestions to what I think I need. Currently I have the following tables:

Code:

mysql> describe students;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe assignments;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| assignment_ID | int(11)     | NO   | PRI | NULL    | auto_increment |
| name          | varchar(30) | YES  |     | NULL    |                |
| type          | int(11)     | NO   |     | NULL    |                |
| max_points    | int(11)     | NO   |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> describe assignment_types;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| type_ID | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(30) | YES  |     | NULL    |                |
| weight  | int(11)     | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe scores;
+---------------+---------+------+-----+---------+----------------+
| Field         | Type    | Null | Key | Default | Extra          |
+---------------+---------+------+-----+---------+----------------+
| sid           | int(11) | NO   | MUL | NULL    |                |
| assignment_ID | int(11) | NO   | MUL | NULL    |                |
| type_ID       | int(11) | NO   |     | NULL    |                |
| score         | float   | NO   |     | NULL    |                |
+---------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

The way I am going about populating them from the php side is to have the professor go in and add students, then add some assignment types, and finally the assignments. Along with all the other data required along side these entries (unless it is autoincriminted). Well so far so good. Until I get down to the part where they actually enter some grades. I have it setup where one page generates a list of ever assignment. upon selecting one, every student, and their grade (if any) is presented for that one assignment. So:

Student 1: [textbox with grade]
Student 2: [textbox with grade]
[Submit]

Since there is only one assignment that can be selected, the assignment_id is set in the background. Then I use an array packed with all the student ids that I scroll through after submit is pressed to assosiate each entered grade with each student. And this gets the right information into the database. However it will put a new entry in every time submit is pressed. This is where I'm getting caught. The uniqueness of each of the rows in the "scores" table is due to no two rows share the same "sid" and "assignment_ID". Looking around there are some good solutions when uniqueness of rows is defined by a single column. But I'm not finding much for when uniqueness is defined by a collection of rows. This really boils down to another question of form. Is it bad form to do something like this:

Code:

foreach(Student_ID_array as current_student){SQL_CALL="SELECT * FROM scores WHERE sid=current_student AND assignment_ID=3
SUCCESS=call_database(SQL_CALL)
if (SUCCESS){SQL_CALL="UPDATE..."
} else {SQL_CALL="INSERT..."
}
}

I don't see why it wouldn't work. It really just seems like it is a lot of database calls. Does a better way come to mind? Or is that solution kind of par for course, and fine to use?

Thank you again. Your help has been invaluable.

bertlef · 05-27-2012, 04:20 PM

I'm glad I've been able to help

I see, so you are not giving 'scores' its unique key, but a multiple key that comes from 'sid + assignment_ID', correct?
This is a valid solution and probably the only problem you will get is the fact that you have to generate the key in each situation by joining two values, which is not really a mayor problem.

The fact of having a lot of database calls will also occur if you have a unique id, so that one we can ignore, also, since this will not be a high traffic database, this problem is not really big.

You are doing great actually.