One to many relationship on the same table

Question

Here is the situation:-

I have a table called Users. This contains user data for students and tutors as most of the data required is the same.

Having completed the system I am now told that the client would like to be able to assign students to tutors.

Is there a legitimate/ clean way I can create a one to many relationship within a single table, perhaps via a link table?

I've tried to think this through but whatever solution I come up with seems messy.

I would be grateful for any input.

Thanks

Phill

Solution

Have you tried the following approach?

Make a new table, for example TutorStudent (choose a more appropriate name if needed). It should have two columns:

• Tutor_ID
• Student_ID

Both columns shall be the (composite) primary key, each column will be a foreign key to your Users table User_ID (I assume this is what you have).

So, if you have a tutor named Newton that has two students, Tesla and Edison, your Users table will have something like this:

• User_ID, Name
• 1, Newton
• 2, Tesla
• 3, Edison

and your TutorStudent table will have following values:

• Tutor_ID, Student_ID
• 1, 2
• 1, 3

Relatively simple and doesn't require any modifications to your existing table.

Do take care when deleting users - use the delete cascade feature of your database system or do some maintenance work afterwards so your TutorStudent table doesn't go stale when updating/removing your users.

Comments

[Phill]

Excellent! Many thanks Darioo - this is exactly what I was hoping for ;-)

[Zanko]

Hello, I am actually currently having the same issue as OP! I am using this design but it appears it does not guarantee ONE-MANY relationship. For example, Table of (1,2) (1,3) (4,3) (2,2) is valid : (

[Yug Singh]

@Zanko any solution to above? IMO one solution can be to handle the above from application.