One to many relationship on the same table

sql database database-design erd
Phill picture Phill · Aug 26, 2014 · Viewed 9.3k times · Source

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

Answer

darioo picture darioo · Aug 26, 2014

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.

Related questions

Database design for a recursive relationship

Consider this case where I'm trying to model a database for a company: Entities: Employees, Managers, Departments. An Employee works in only 1 Department while a Department may have many Employees working in it. A Manager may manage only 1 Department and …

Read More
How to create a new schema/new user in Oracle Database 11g?

I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the DDL file. I have installed Oracle database 11g Express …

Read More
SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

If I have two relations in a database, like this: CREATE TABLE Courses ( CourseID int NOT NULL PRIMARY KEY, Course VARCHAR(63) NOT NULL UNIQUE, Code CHAR(4) NOT NULL UNIQUE ); CREATE TABLE BookCourses ( EntryID int NOT NULL PRIMARY KEY, BookID int …

Read More