a question on many-many relationships

Question

I have the following tables

1. items
2. similars
3. items_similars = pivot table

items->similars has a many-many relationship

the items_similars has the following field

1. item_id
2. similar_id

if I am using InnoDB as my engine, do I need to create a relation between items.id and items_similars.id? or between similars.id and items_similars.id? or even both?

are there advantages in doing or in not doing so?

Answer 1

Many-to-many relationships, AFAIK, can only be implemented via transition tables (pivot tables) in RDBMS. The "items_similar" table should have at least "items_id" and "similar_id" which are foreign keys from the "items" and "similars" tables' primary keys.

Answer 2

CREATE TABLE item_similar
  ( 
   item_id int NOT NULL
  ,similar_id int NOT NULL
  ,PRIMARY KEY ( item_id, similar_id )
  ,FOREIGN KEY ( item_id ) REFERENCES item ( item_id ) ON UPDATE CASCADE
    ON DELETE NO ACTION
  ,FOREIGN KEY ( similar_id ) REFERENCES similar ( similar_id ) ON UPDATE CASCADE
    ON DELETE NO ACTION
  )