CODEWITHSUNDEEP
mysqlrelationship
Boots
Boots

Reputation: 224

Database Relational - Many to Many

Ok, so I'm not the greatest with databases (MySQL in this case), but I'm getting there.

I've run into a problem relating 2 tables within my database. I have a Students table and a Class table. The relationship (on paper) would be many-to-many. IE: A student can be part of many classes and a class can have many students.

At the moment, I just have the 2 tables, Students and Class, but this doesn't seem to be enough. The only solution I currently have is the following:

Add an extra table, Classes with 2 fields: ClassID (relating to Class.ClassID) and StudentID (relating to Student.StudentID)

Now I'm fairly certain this will work, but when it comes to adding/retrieving/updating records, I'm worried I'm going to get a bit lost. Would there be some sort of joins when doing those queries?

If this isn't the best approach, what would be? If possible, I would like to keep all table manipulations to the GUI in PHPMyAdmin.

Thanks!

Upvotes: 0

Views: 120

Answers (2)

LuigiEdlCarno
LuigiEdlCarno

Reputation: 2415

A junction table is exactly how it is done. You are right in your approach.

To retrieve all student records for a class simply do:

Select s.* from students s, class c, studcls sc where s.id=sc.student and c.id=sc.class and c.title='Math';

Upvotes: 1

KaeL
KaeL

Reputation: 3659

As far as I know, dealing with tables that have many-to-many relationship needs an extra table, they call it JUNCTION TABLE. Take a look at it, your design seems fine.

Upvotes: 2

Related Questions