This table should contain foreign keys to allow sensible links to be made with the other two tables, together with the dates of each exam.
So what am i being asked to do exactly? All the other tables have been populated.
4
- 1They are wanting you to create a junction table between subjects and students as many students will have many subjects.whitwhoa– whitwhoa2015-11-20 14:19:49 +00:00Commented Nov 20, 2015 at 14:19
- Thanks for the help.MrInterBugs– MrInterBugs2015-11-20 14:21:52 +00:00Commented Nov 20, 2015 at 14:21
- Stackoverflow is not here to solve your homework. Use the search engine and search for many-to-many relationshipXavjer– Xavjer2015-11-20 14:22:37 +00:00Commented Nov 20, 2015 at 14:22
- Hi again so I have done what you suggested but what about the date of each exam as I can only think of adding it by using a select command and I am not even sure if that is right.MrInterBugs– MrInterBugs2015-11-27 14:13:28 +00:00Commented Nov 27, 2015 at 14:13
Add a comment |
3 Answers
This looks like a classic Many To Many relational problem.
One student can study more than one subject
A subject can be studied by more than student
This cannot be readily modelled relationally using just the two tables you have given.
The traditional way to model this is to introduce a third table which contains the details of which students are studying which subjects.
The table would generally contain the id's of the tables that participate in the many to many relationship, so in your case it would have student_id and subjects_id. These would be defined with foreign key constraints back to the student and subjects tables.
This page may go a long way to helping you understand this.
By the way - the primary key constraint on the subjects table looks a bit suspect to me ...
4 Comments
MrInterBugs
Thanks for the answer, ignore the primary key I copied an old file but I have not fixed it.
MrInterBugs
Hi again so I have done what you suggested but what about the date of each exam as I can only think of adding it by using a select command and I am not even sure if that is right.
DaveH
I'm not sure what you should do with the date of the exam - as far as I am concerned it makes no sense to put it on the link table - that would imply that different students could do the exam on different dates.
MrInterBugs
Oh okay I will just have to find a way as i cant just leave it out but thanks anyways.
First of all, your PRIMARY KEY of the subject table should be subjects_id, because you may have 2 final exams the same day.
Comments
CREATE TABLE IF NOT EXISTS entries( entry_id INT UNSIGNED NOT NULL AUTO_INCREMENT,( PK) student_id INT UNSIGNED NOT NULL,(FK From students table) subject_id INT UNSIGNED NOT NULL,(FK From subjects table ) ); You need to make (student_id,subject_id) combination unique since it shouldn't be duplicated.This is rough draft.Assume that this table is a bridge which connects students and subjects.
