- 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.
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
- 点赞 评论 复制链接分享
First, you need several tables. One table cannot do it. Create a table for each type of objects. In this case, you need a table for students and one for modules.
Both tables need an id column to identify the rows. Create a primary key on them.
Students table: Id, StudentName
Module table: Id, ModuleName
There is a many to many relationship between students and modules (each student can have more than one module and each module can be chosen by many students). A many to many relationship requires a separate table.
You were not clear if there is one grade per student and module. If there is only one grade, the best solution is to include the grade in this relationship table.
Grade table: Id, StudentId, ModuleId, Grade点赞 评论 复制链接分享