CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designmany-to-many
SharpBlade
SharpBlade

Reputation: 57

Is it antipattern to create a junction table with more than two foreign keys?

I am designing a database for school management.

Here instead of a single school, there are multiple schools with their own students, classes, teachers and subjects.

Here is the requirements.

• A school can have many classes

• A class can have many sub classes

• Many Students can belong to a section of a class

• Many Teacher can belong to a section of a class

• Many Subjects can belongs to a section of a class

• School wants to manage classes

• School wants to manage sections

• School wants to manage subjects in a section of a class

• School wants to assign a teacher for a subject in a section of a class

• School can assign a student a section of a class

Also, some school can have classes as named One, Two, Three and Some may have First, Second and Third. Also same for sections or sub classes

For entities School and Teacher things are straight forward. Below is my approach for them.

School
--------------
id 
name
...

Teacher
--------------
id
school_id
name

But I am facing problem while mapping classes and sections for a school.

I tried to make two other entities classes and sections and give them a foreign key of the school

SchoolClass
-----------------
school_id
class_name
...


SchoolSection
----------------
school_id
section_name
...

For mapping a school's class to all its section I created a junction table, as it will be many to many relationship.

SchoolClassSection
---------------------------------------------------------
school_class_id
school_section_id

But as I mentioned above I also need to add subjects and teachers to a section of the class so tried something like below.

SchoolClassSections
---------------------------------------------------------
school_class_id
school_section_id
subject_id
teacher_id

But now I ended up having four foreign keys in the junction table.

Is there no issues in having more than two foreign keys in a junciton table?

Also, I need to add students too, but I can't now figure it out How to move further for students relation for classes, sections?

One thing I could thing of like below

student
-----------
school_id
school_class_section_id // but if SchoolClassSections can have subject_id and teacher_id too it will become confusing

Upvotes: 1

Views: 1552

Answers (3)

Rick James
Rick James

Reputation: 142366

Some concepts, especially OO concepts, do not fit well with SQL.

SQL has "only":

  • 1:1 relationships (generally this should not be used)
  • 1:many (implement via a link in one table to the other)
  • many:many (requires a separate 'junction' table)

In each case "1" can degenerate to "0" and "many" can degenerate to "1" or "0". Usually "0" is not represented explicitly but by the absence of rows.

To create schemas:

  1. Think of what "entity" tables to have. (Student, Class, School, etc.)
  2. Create relations, thinking in terms of 1:many and many:many.
  3. Optionally, establish FOREIGN KEYs for the links. (For referential integrity; implicitly creates an INDEX)
  4. Make sure the appropriate columns are INDEXed. (For performance)

Later...

  1. Write the SELECTs, UPDATEs, etc.
  2. Use those queries to decide whether other indexes are needed.

Your question is mainly about the many:many tables.

  • The basic many:many table has exactly 2 column (links to two other tables)
  • The basic has two composite indexes (PRIMARY KEY(a,b) and INDEX(b,a))
  • Sure, make it many:many:many (Not common, but reasonable)
  • Add columns for attributes about the relations (also not common, but OK)
  • Add composite indexes for the queries that depend on those attributes (for performance)

More discussion of many:many: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

I didn't analyze your schema, due to time constaints here.

But there's no problem from either the relational point of view or the SQL point of view with a table or junction table having more than two foreign keys. In fact, that's pretty common.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

No, it is not an "antipattern". There are many examples where a junction table might have more than two foreign keys:

  • A doctor's appointment might have a doctor, a patient, and a clinic.
  • A retail order might have a customer, a location, and a payment type.
  • An online subscription might have a customer id, product, payment type, and source.

Those are just some examples from other domains (and they do not even include a date dimension). It is not uncommon for a table to have multiple foreign keys.

Upvotes: 2

Related Questions