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:
- 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.
- 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.