CODEWITHSUNDEEP
mysqldatabasemany-to-many
Timonvw
Timonvw

Reputation: 65

Many-to-many relationship database, which to pick with a lot of users

We are currently working on a game with a database where the player can have certain items who are stored in the database. I always learned at school and on the field to use option 1 but my colleague is saying option 2.

We have to pick one options and we are asking the question now: Which of the two options is the best and fastest?

And also which one is the best and fastest with 50K users?

Table info

Upvotes: 0

Views: 36

Answers (1)

O. Jones
O. Jones

Reputation: 108676

Option 1. You are correct.

If you use option 2 you'll be sorry. It's denormalized. Updating those comma-separated lists of itemID values is ridiculously difficult. You'll have to use transactions to read the value string, change it, and write it back.

Also, option 1 can exploit database indexes for much more efficient searching. Ask your friend how you will find all users with itemId = 15. Ask him to write that query for you. With Option 1 you can use

  SELECT UserId
    FROM tbl
   WHERE ItemId = 15

You can use a query to generate option 2 for display. Display is all it's good for.

     SELECT UserId, GROUP_CONCAT(DISTINCT ItemId ORDER BY ItemID) ItemId
       FROM linktable
      GROUP BY UserId

Upvotes: 1

Related Questions