CODEWITHSUNDEEP
database-designms-access-2007database-schema
Edison
Edison

Reputation: 4281

Many to many relationship in my database design

I have a very simple database design which I have made so far.One problem I am facing now is following scenario.

A Student can have multiple subjects and a subject can be taken by multiple students.I am not being able to join the following tables

1.Student
2.Student_Subject
3.Subject

Where I am mistaking here...........Class DataBase Design

Upvotes: 0

Views: 1165

Answers (1)

Abozanona
Abozanona

Reputation: 2285

You must connect Student>ID with Student_Subject>St_ID and Subject>ID with Student_Subject>Sub_ID.

You will need to do this because the relation between Student table and Subject table is many-to-many. and you must split it into 2 relations 1-to-many and many-to-one. That is one of the main rules in making SQL relations.

and in this case you won't need Student>st_Id since Student>Id will store the student's ID.it's the same with Subject>Sub_id.

Now, when a student has many courses you will need to add a new field to Student_Subject with the ID of the student in St_ID and the ID of the course in Sub_ID. the same will happen for a course with many students.

In both cases, you will only add fields to Student_Subject table.

you can google more about SQL relations.

Upvotes: 1

Related Questions