CODEWITHSUNDEEP
mysqldatabase
Gowtham
Gowtham

Reputation: 386

Many to Many relation between two tables

I would like to create a relational database in which there are two tables Users and products. Each item of each table can be related to many items of the second table.

My current implementation is as follows:

Two main tables-

->Users
User ID
UserInfo

->Products
Product ID 
ProductInfo

Two different lookuptables

->UserToProduct
UserID
ProductID

->ProductToUSer
ProductID 
UserID

Each time a relation from a user to a product is added, i just add an extra row to the first lookup table, and vice versa.

Is this the right way to do it? Are there any standard models for such scenarios that I can refer to?

Upvotes: 0

Views: 810

Answers (2)

Madbreaks
Madbreaks

Reputation: 19539

You don't need two lookup tables, all you need is users_products. As far as resources, there are zillions, just google "database many to many".

UPDATE

Consider this data:

products
------------
id     info
------------
1      car
2      flute
3      football

users
------------
id     info
------------
10     bob
20     tim
30     manning

Now, as a simple example, let's say manning owns a football and and a car. Let's say tim owns a flute and a football. Now here's your lookup table:

users_products
----------------------
user_id    product_id
----------------------
  20           2
  20           3
  30           3
  30           1

That's it. Now you can do queries like "give me all the users that have cars", or "give me all the cars that a user has", etc.

Cheers

Upvotes: 3

Brian Driscoll
Brian Driscoll

Reputation: 19635

You really don't need or want two different lookup tables. You should just have one (either of your tables, UserToProduct or ProductToUser, would be fine). The primary key of the lookup table should be a composite key consisting of both ProductID and UserID.

Upvotes: 1

Related Questions