CODEWITHSUNDEEP
sqldatabasemany-to-many
theateist
theateist

Reputation: 14399

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?

Upvotes: 1

Views: 184

Answers (5)

onedaywhen
onedaywhen

Reputation: 57023

The problem with option 2 is that your new data structure is not compatible with SQL's operators. For example, the value "4,12,34," should be considered the same as "4,34,12,": in absence of being able to overload SQL's operators, you'd need to a) write user defined functions and b) train users to use the UDFs rather than SQL operators. Repeat for every operator.

The same applies to constraints. For example, you would need to prevent the value "4,12,4," because it contains duplicate members. Again, you'd need to roll your own constraints, presumably using SQL CHECK constraints.

In the process of writing these operators and constraints, you'd always need to split apart the members, operator on them, then concatenate them again. Which would beg the question: why not just keep them apart? Then we are back at option 1!

Upvotes: 0

socha23
socha23

Reputation: 10239

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.

Upvotes: 5

everag
everag

Reputation: 7672

For the sake of the model, alternative 1 should be used. Alternative 2 is an ugly workaround.

You couldn't even make an FK if you use alternative 2.

Upvotes: 1

justcompile
justcompile

Reputation: 3542

Option 1 is by far the better and most commonly used as you can setup your link table (ProductToStatus) with foreign keys to both tables which will maintain data integrity.

Upvotes: 3

Wiktor Zychla
Wiktor Zychla

Reputation: 48250

The 2nd approach sounds rather clumsy. How you are going to perform queries over such model? The 1st approach is correct, this is how people usually model it.

Upvotes: 2

Related Questions