# Many-to-many Mapping table
# 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.)
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.