MySQL – Ranking Items on a Per User Basis

database-designMySQL

I have a MySQL DB with 20k-50k items and I want various users (let's say 1k-10k) to rank these items for themselves, so this is not going to be a global ranking but a per user one.
All items are identified by their 7 digit id.
Since I'm guessing that there will be a pretty sparse ranking (perhaps about 1k items per user), I'm not sure how to store these rankings.

As I see it, there are two obvious solutions:

  1. Add one column per user to the items table where I note each rank. –> Problem: If one rank changes, there will have to be a lot of updates.
  2. Store the entire ranking of one user as an ordered list in one textfield. –> Then I can do all the reordering and inserting in my code and could easily query a few top ranked items. But I always have to read a whole bunch of data.

What would be the more efficient way and am I missing another solution.

I am well aware of a few other questions regarding the same general topic but they have a global ranking of the items (also all items actually have a rank which is not the case in my problem) and usually handle 100x more data.

Best Answer

Suggest to record the rankings, as they occur, in a third table where the primary key links the user and the item. Table only records as many rankings as are made available. Each user's rankings are kept separate.

This records a ranking as an ordered triple: (user, item, rank).

    CREATE TABLE `user_item_rank` (
      `fk_user` ... NOT NULL,
      `fk_item` ... NOT NULL,
      `rank` ... NOT NULL,
      PRIMARY KEY (`fk_user`,`fk_item`),
      KEY `fk_user` (`user_table`),
      KEY `fk_item` (`item_table`),
      CONSTRAINT `...` FOREIGN KEY (`fk_user`) REFERENCES ... 
      CONSTRAINT `...` FOREIGN KEY (`fk_item`) REFERENCES ...
      ...
      );
Related Question