Re: best practices in many:many relationship

[Rick James]

re table c(cid int pk, aid references a, bid references b)
Skip cid, assuming the table has only the two fields.
CREATE TABLE AB (
aid ...
bid ...
PRIMARY KEY (aid, bid),
INDEX(bid, aid)
) ENGINE=InnoDB;

Rationale:
* Adding cid adds nothing.
* (aid, bid), or the opposite order is the 'natural PK'.
* A PK is UNIQUE, so PK(aid,bid) prevents dups.
* (bid, aid) lets you to go the other direction efficiently.
* All uses will say "Using index" in EXPLAIN, hence be efficient.
* Because of the differences in InnoDB and MyISAM indexes, InnoDB is more efficient than MyISAM.
* Even if aid and bid are bulky fields (ie, not simple INTs), there is no advantage of further normalization of such values.
* You can add FOREIGN KEY constraints if you like (I don't like).