MySQL Many-to-many Mapping table
Remarks
- Lack of an
AUTO_INCREMENTid for this table -- The PK given is the 'natural' PK; there is no good reason for a surrogate. MEDIUMINT-- This is a reminder that allINTsshould 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-negativeNOT NULL-- Well, that's true, isn't it?InnoDB-- More effecient than MyISAM because of the way thePRIMARY KEYis clustered with the data in InnoDB.INDEX(y_id, x_id)-- ThePRIMARY KEYmakes it efficient to go one direction; the makes the other direction efficient. No need to sayUNIQUE; that would be extra effort onINSERTs.- In the secondary
index, saying just
INDEX(y_id)would work because it would implicit includex_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.
