Many-to-many relationship

Many-to-many relationship is used when entity in one model can relate to multiple entities in another model, and the other way round. A perfect example of that can be categories and products in an eCommerce shop.

There’s one caveat with many-to-many relationships and limitation of SQL, and that is the fact that there’s no way to create such relationship. To be able to create it, you have to create another table with a unique ID (could be concatenation of foreign keys), id from one model and id from the other model

CREATE table Product {  
	productId INTEGER PRIMARY KEY  
	name TEXT  
	description TEXT   
}  
  
CREATE table Category {  
	categoryId INTEGER PRIMARY KEY  
	name TEXT  
}  
  
CREATE table CategoryProductRelation {  
	productId INTEGER  
	categoryId INTEGER  
	PRIMARY KEY (productId, categoryId)  
	FOREIGN KEY productId REFERENCES Product(productId)  
	FOREIGN KEY categoryId REFERENCES Category(productId)  
}