Many-to-many Mapping table
suggest changeRemarks
- 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 allINTs
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-negativeNOT NULL
– Well, that’s true, isn’t it?InnoDB
– More effecient than MyISAM because of the way thePRIMARY KEY
is clustered with the data in InnoDB.INDEX(y_id, x_id)
– ThePRIMARY KEY
makes 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.
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents