Search code examples
databasedatabase-designinnodb

a question on many-many relationships

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?

Solution

  • 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.