Hi all, i am setting up a database using sql to store details for students modules results, i have 3 tables
students
modules
results
the issue is i cant understand how to store data in the results table, as students have completed many modules, how would i store this?
storing student module records
Moderator: General Moderators
Re: storing student module records
First of all, are you saying that you are using Microsoft SQL Server?ma5ect wrote:Hi all, i am setting up a database using sql to store details for students modules results, i have 3 tables
students
modules
results
the issue is i cant understand how to store data in the results table, as students have completed many modules, how would i store this?
Specifically, since I don't know what you mean by "modules" and "results", it is impossible to answer your question. You will have to explain what these mean. Either define what each entity is, or show the list of fields in each table.
If you define each entity, it could be like this: "students entity is the collection of students, their names, addresses, birthdates, etc."
If you list the fields, the recommended format is like this:
Code: Select all
[b]students[/b]:
sID Int(10)
sFname varchar(20)
sLname varchar(20)
sDOB Date
...Re: storing student module records
students table...
student ID
surname
forename
course
year
module table(subject)...
module ID
module name
results table..
student ID (FK)
module ID (FK)
completed
attempts
in the results table i want to store data of many student who has taken many modules and store there results...
student ID
surname
forename
course
year
module table(subject)...
module ID
module name
results table..
student ID (FK)
module ID (FK)
completed
attempts
in the results table i want to store data of many student who has taken many modules and store there results...
Re: storing student module records
OK, that's a good start. I would suggest that you remove the Course and Year fields from the students table and put those in the results table, because they are attributes of a "result", not of a student. If the same student takes 2 courses or takes courses in different years, your structure won't allow it.ma5ect wrote:students table...
student ID
surname
forename
course
year
module table(subject)...
module ID
module name
results table..
student ID (FK)
module ID (FK)
completed
attempts
in the results table i want to store data of many student who has taken many modules and store there results...
So then your question, I think, is how to populate the results table, right? Basically, you need a script that displays 2 dropdown selection boxes, one to select a student and one to select a course, and whatever other input controls you may need, such as year, completed and attempts. When the form is submitted, the script that processes it (either the same script or another one, depending on how you want to program it) will obtain the values of the student ID and module ID and whatever other data you need, and insert a record into the results table. So the trick is, how do you get those ID's? That's done in the first part, when you generate the form and its dropdown controls. In general, this is the kind of syntax you would use:
Code: Select all
...
while($row=mysql_fetch_assoc($result)) {
...
echo "<option value=".$row['studentID'].">".$row['surname'].">";
...
}