2

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.

  • 1
    What causes the rank to change? Can a SELECT be written to fetch the items in 'rank' order? – Rick James Mar 28 '16 at 16:32
  • The user should choose between two items. If he chooses the item that was previously not ranked or had a worse rank, then the chosen item will be placed one above the other. If it was not ranked and not chosen it will be put in the middle between the rank of the other item and last place. If both were ranked and the user chooses the better ranked, nothing changes. And yes, items should be able to get fetched in order of ranking. – sebastian Mar 28 '16 at 17:19
  • 1
    Clarification needed: "in the middle between the rank of the other item and last place" -- If the 'other item' is ranked 10 and 'last place' is 20th, is it to be ranked 15th? Higher? or Lower? than the current 15th-place item? – Rick James Mar 28 '16 at 18:40
  • it doesn't really matter, but it will be ranked 15th. so the last item on 15 will move one rank down. – sebastian Mar 28 '16 at 23:29
0

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 ...
      ...
      );
  • At the time of asking the question I was not aware that a PK can be built from two other PKs. This is of cause a great solution. Thanks. – sebastian Sep 10 '18 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.