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:
- add 3rd table ProductToStatus that will match ProductId with ProductStatusId
- 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?
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.