What is the common way to create “Many-To-Many” relationships?

I have 2 tables Product that contains products of the shop and ProductStatus that indicates if product is new, sold, etc. One product may have many statuses.

I need to link Product and Status. I thought about two ways:

  1. add 3rd table ProductToStatus that will match ProductId with ProductStatusId
  2. add Status column to Product table that will contain the IDs of the statuses separated with commas("4,12,34,");

What the pros and cons of each solution above or maybe there is another common way to do this?

Answers


First way is correct, and more common. A table used to model many-to-many relationship is called a join table, relationship table or junction table, among other names.

Its main adventages over second way you propose are:

  • faster database operations - for example, if you want to find all the products with certain status, the query using join tables will be pretty fast with right indexes created, while the second way you propose would mean searching for a given substring in every product (which would be slow).
  • constraints checking - you can declare ProductId andProductStatusId columns as foreign keys, and the database will stop you from assigning a non-existant status for a product.

Main disadventage of a join table is that the code for adding / modifying Products can be a little harder to write, as you need to manipulate more database records. Fortunatelly, modern ORM frameworks take care of all that work behind the scenes.


Need Your Help

SOLVED: Mysql select from 2 different tables and merge result

mysql join merge

I'd like to ask you for help with mysql. I want to merge 2 absolutely different tables which has date column. Tables looks like that:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.