在数据库中存储考试数据的最佳方法[关闭]

Question

I am looking to implement a new database and store students exam data and marks in the database. There are an arbitrary number of students and each student has an arbitrary number of modules with corresponding marks. I need the database to be created so that I am able to query the database and return the students name followed by each of their modules and corresponding marks and then to move onto the next student. This is because I want to display it in PHP with a list of students names in bold and then a table of their modules and their marks.

I initially considered to organise the table like this:

| StudentName | Module | Result |

With a new entry for each module that the student takes and just store multiple students but then I do not know how I would then query the database and retrieve each StudentName individually and then be able to loop through their corresponding modules and results to store it in an HTML table.

Any ideas would be appreciated

Answer 1

• Give each table an id field that auto increments
• Where you need to link results to modules, for example, you'd add a moduleID field to the result table to tie the result to the module. You'd then do queries with joins to bring in both sets of data.
• To tie students to modules, you'd need another table that has a studentID field and a ModuleID field. You'd then query this table and join on StudentName and Module to get the respective data for each.