many-to-many relationships

[kelvinq]

Hi there. I'm a beginner in sql. I am very much concern in database design and have read that learning Database Normalisation will help in efficient design.

But I have a problem in elimating many-to-many relationships.

I'm designing a database which I hope can be a matchmaker between tutors and students.

The many-to-many relationship occurs here.

Each tutor can coach one or many subjects.
Each subject can be taught by one or many tutors.

Can anyone suggest a way out of this problem?

To illustrate my situation more, each tutor can coach different subjects at different levels, like Physics, Chemistry and Economics and A-levels and O-levels.

Thanks!

 

[Mile-O]

Using another table enables the simulation of a many to many relationship.

In your case, a third table called tblTutorsToSubjects

It should have two fields: SubjectID and TutorID which are the primary key fields of your respective Subjects and Tutors tables. These foreign keys become this table's primary key.

Relate them accordingly a one to many and you now have a many to many relationship.

 

[kelvinq]

Thanks for the really quick reply.

It sounds like a really elegant solution, simple and effective. But I don't seem to get it. Let me show you what I understand from your reply.

Previously,

Tutor (table)
tutor_name
tutor_id (primary key)
subject_id (forgein key)

Subject (table)
subject_name
subject_id (primary key)
tutor_id (forgein key)

Since each tutor can coach many subjects
and each subject can be taught by many tutors,
we have a many-to-many relationship.

Now, (after normalisation)

Tutor (table)
tutor_name
tutor_id (primary key)

Subject (table)
subject_name
subject_id (primary key)

TutorToSubject (table)
tutortosubject_id (primary key)
tutor_id (forgein key)
subject_id (forgein key)

Is that what you meant?
Thanks.

 

[Mile-O]

Sort of.

The new table only has two fields. These are foreign keys to the two other tables. Select both these foreign keys as one, and select them as the primary key.

 

[kelvinq]

I see. I'm using mySQL, not Access. I'll try to achieve that. If I succeed, I'll inform you guys. Thanks. This forum rocks because of guys like ya!