I'm a newbie in database design.

Can anybody look at this design:

• Initially I created these tables - m2m relation first. It was simple:

  • students
  • contacts
  • contact_types

• But new table teachers was added into database after that.

• ...and each teacher may have contact too.

• I just added new column into contacts "junction" table.

  • But I don't like this design, because there are empty values in columns of teacher_id and student_id columns.

• Is there better way to create many-to-many relations for that situation?

• Main goal is to get ability of adding multiple contacts for one student/teacher.

The former variant of my design, but I don't know if it is optimal or not.

CodePudding user response：

To allow your design to be M:M you need something like this:

table teacher { id, .... }
table student { id, ...}
table contact { id, typeId, value }
table contactType {id, ....}
table contact_teacher {contactId, teacherId}
table contact_student {contactId, studentId}

Then you have your M:M relationship between student and contact and teacher and contact

CodePudding user response：

The right way to create a many to many relationship between teachers and contacts is to create a "joining table" that table would look like this:

 contactteacher table

 name      type
 id        int - auto increment 
 teacherid int
 contactid int

I like to call the tables by the things they join ordered alphabetically that is why I suggest the name contactteacher

There might be additional information for contact -- like where you met someone or when you met them.

There could also be meta data in the table -- when the record was created when it was edited, who created it -- etc.