CODEWITHSUNDEEP
mysqldatabaserelational-database
user2727704
user2727704

Reputation: 645

Database design for user and top 25 recommendations

I got a MySQL database and need to store upto 25 recommendations for each of the users (when user visits the site), here is my simple table that holds userid, recommendation and rank for the recommendation:

userid | recommendation | rank
  1    |    movie_A     |   1
  1    |    movie_X     |   2
  ...
  10   |    movie_B     |   1
  10   |    movie_A     |   2
  ....

I expect about 10M users and that combined with 25 recommendations would result in 250M rows. Is there any other better ways to design a user-recommendation table?

Thanks!

Upvotes: 0

Views: 306

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562398

250 million rows isn't unreasonable in a simple table like this:

CREATE TABLE UserMovieRecommendations (
  user_id INT UNSIGNED NOT NULL,
  movie_id INT UNSIGNED NOT NULL,
  rank TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, movie_id, rank),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (movie_id) REFERENCES Movies(movie_id)
);

That's 9 bytes per row. so only about 2GB. 

25 * 10,000,000 * 9 bytes = 2250000000 bytes, or 2.1GB.

Perhaps double that to account for indexes and so on. Still not hard to imagine a MySQL server configured to hold the entire data set in RAM. And it's probably not necessary to hold all the data in RAM, since not all 10 million users will be viewing their data at once.

You might never reach 10 million users, but if you do, I expect that you will be using a server with plenty of memory to handle this.

Upvotes: 0

Kalyan Chanumolu-MSFT
Kalyan Chanumolu-MSFT

Reputation: 1143

Is your requirement only to retrieve the 25 recommendations and send it to a UI layer for consumption? if that is the case, the system that computes the recommendations can build a JSON document and update the value against the Userid. MySQL has support for JSON datatype.

This might not be a good approach if you want to perform search queries on the JSON document.

Upvotes: 3

Related Questions