need help with database creation

Question

Hi!

I need to develop a database for A level students, and I'm having problems categorising their subjects. They have 13 subjects altogether and each student can choose as many subjects and any combination from 13 subjects as he likes.
How can I cope with this problem? How can I create a database for keeping the record of each student's subject combination?

Can anybody help me?

Answer 1

Make a table with a list of students and make a table with a list of subjects. In another table insert rows with the ID of the student and the ID of the subject they are taking.

id | student

id | subject

id | subject id | student id

Answer 2

php and mysql would be a great start

Answer 3

table: subject

columns: id, subject_name

table:student

columns:id, student_name

table:student_subject_map

columns:id, student_id(student.id - foreign key), subject_id(subject.id - foreign key)

Answer 4

You'd have three tables: a Subjects table, where the subject information is stored; a Students table, where the student information is stored, and an Enrolment table, which contains two columns, a student id and a subject id.

To get a list of the subjects a student's doing, you perform a query something like this:

SELECT Subjects.name FROM Subjects INNER JOIN Enrolment ON Enrolment.subject_id = Subjects.subject_id WHERE Enrolment.student_id = '(student_id)' ORDER BY Subjects.name ASC

Answer 5

And to see what all subjects a student has selected,

select subject_id from student_subject_map where student_id == < student-id >;

Replace < student-id > with the id of the student. The next level would be to get the subject name instead of the subject_id using the id, subject_name match in table_subject. (Clue: a nested select statement)