Mysql – create foreign keys for pivot tables

foreign keyMySQLpivot

I'm trying to optimise and speed up a mysql 5.6 datatabase.

I have two tables with a many to many relationship:

Reservation and Resource. Each Reservation can have many Resources and each Resource can have many Reservations. I have a pivot table between the two tables which simply joins the two via their respective Id's

Should I be creating foreign keys within the pivot table for each of the ID's? Will this speed up queries or does this make little difference with a pivot table.

My initial thoughts are that I should but don't want to create a mess!

Best Answer

Foreign key are constraints for data consistency, and they should be added always as a security measure. In order for the foreign keys to be fast, MySQL also creates its corresponding indexes unless the columns are already indexed. That is independently of the performance question: create the foreign keys to avoid storing incorrect data.

In most cases, you may want to index the pivot table because the most typical queries that you will be doing are along these lines:

SELECT * 
FROM Reservation RV
JOIN pivot p ON p.reservation_id = RV.id
JOIN Resource RS ON p.resource_id = RS.id
WHERE filter(column_on_Reservation_or_Resource)

In that case, you will traverse the pivot table in Reservation->Resource direction or viceversa. For that, the best index would be pivot(reservation_id, resource_id) (and/or viceversa if you are using it in the other direction), as that way you can get advantage of the covering index optimization. One of the directions can be the primary key, if the pairs happen to be unique (relationships cannot be multiple among the same pair of records from reservation and resource).

Related Solutions

Mysql – Joining Three Tables by using IDs as Foreign Keys

You are on the right track. Don't let perceived complexity derail the design, as it is basically the standard way to represent many-to-many relationships. These tables are also a good place to store metadata or attributes of the entity relationship.

Your reasoning is mostly correct: there are data integrity benefits to using a DBMS-generated key in the intermediate table approach, but an integer isn't necessarily faster on key comparisons because ideally the joins would use indexes, not the column values.

Composite primary key from multiple tables / multiple foreign keys

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

Best Answer

Related Solutions

Mysql – Joining Three Tables by using IDs as Foreign Keys

Composite primary key from multiple tables / multiple foreign keys

Related Question