Getting started with MySQL Limit and Offset Creating databases Backup using mysqldump Table Creation INSERT Error codes Stored routines (procedures and functions)Reserved Words String operations DELETE VIEW Indexes and Keys Date and Time Operations Comment Mysql LOAD DATA INFILE PREPARE Statements ALTER TABLE Joins UPDATE Change Password JSON MySQL Admin TRIGGERS Pivot queries Configuration and tuning Converting from MyISAM to InnoDB SELECT Create New User Group By UNION Drop Table Data Types Performance Tuning Events ENUM Install Mysql container with Docker-Compose Arithmetic Character Sets and Collations MyISAM Engine Many-to-many Mapping table Using Variables Log files Partitioning Clustering Security via GRANTs Backticks mysqlimport MySQL LOCK TABLE MySQL Unions ORDER BY MySQL client Mysql Performance Tips Temporary Tables Transaction Customize PS1 Dealing with sparse or missing data Dynamic Un-Pivot Table using Prepared Statement NULL Replication Connecting with UTF-8 Using Various Programming language.SSL Connection Setup Handling Time Zones Time with subsecond precision Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ...Full-Text search Extract values from JSON type Regular Expressions Recover and reset the default root password for MySQL 5.7+One to Many JOINS: Join 3 table with the same name of id.Server Information Recover from lost root password

Many-to-many Mapping table

Remarks:

Lack of an AUTO_INCREMENT id for this table -- The PK given is the 'natural' PK; there is no good reason for a surrogate.
MEDIUMINT -- This is a reminder that all INTs should be made as small as is safe (smaller ⇒ faster). Of course the declaration here must match the definition in the table being linked to.
UNSIGNED -- Nearly all INTs may as well be declared non-negative
NOT NULL -- Well, that's true, isn't it?
InnoDB -- More effecient than MyISAM because of the way the PRIMARY KEY is clustered with the data in InnoDB.
INDEX(y_id, x_id) -- The PRIMARY KEY makes it efficient to go one direction; the makes the other direction efficient. No need to say UNIQUE; that would be extra effort on INSERTs.
In the secondary index, saying just INDEX(y_id) would work because it would implicit include x_id. But I would rather make it more obvious that I am hoping for a 'covering' index.

You may want to add more columns to the table; this is rare. The extra columns could provide information about the relationship that the table represents.

You may want to add FOREIGN KEY constraints.

Typical schema

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X
    INDEX      (y_id, x_id)             -- When starting with Y
) ENGINE=InnoDB;

(See Remarks, below, for rationale.)

Contributors

Topic Id: 4857

Example Ids: 17121

This site is not affiliated with any of the contributors.