CODEWITHSUNDEEP
mysqlsqlrelationship
Jessie
Jessie

Reputation: 493

Many to Many relationship or fallacy?

I have a table Products:

id | name | price
__________________
1    Rose    40

Besides there is another intermediate table that contains information for whom product is intended:

id | product_id | for_whom
___________________________
1     1              1
2     1              2

The third table is ForWhom:

id | Name
__________
1    Mom

Any product can contains one or more parameters for_whom.

Otherwise, using table ForWhom I can find one or more products which are associated with ForWhom table.

I think I need to use many-to-many relationship, but I am doubt.

What can you say?

Upvotes: 1

Views: 49

Answers (1)

flip
flip

Reputation: 555

You can answer this by talking yourself through the relationship as you have above.

In the example you've given, you can express it as follows.

One Product Can Belong To One or More ForWhom. One ForWhom Can Own One or More Product.

One of the the aims of database normalization is to prevent duplication of data. So if you have a Product which belongs to more than one ForWhom, and you have a for_whom column in the Product table, then every time a Product belongs to more than one ForWhom, you will duplicate the product record. Likewise, if you have a ProductID in the ForWhom table, then every time a ForWhom owns more than one Product, you will duplicate the ForWhom record.

The solution is as you've suggested above. You have an intermediary table that allows you to hold the relationship without duplicating any data. In your solution above, every record in Product, ForWhom and ProductForWhom (intermediary table) would be unique, therefore you would not be duplicating data.

Upvotes: 1

Related Questions