I have an application in which users can rate items from a list by whether they like or dislike them (users may also choose not to rate an item).
There are millions of items which can be rated by users in this positive/negative/unrated way. I am looking for a solution to what sort of database and database design would be appropriate to store these ratings, assuming that there may be thousands of users, and that other data is being stored about both the users and the items.
I would like to be able to (preferably efficiently) query the database to find:
- a list of all items two or more specific users have both liked
- a random item that a user has not rated before
- a random item that a user has not seen before (not allowing inclusion of those that the user left unrated)
- a random item which has some specific properties, e.g. only items with a specified location of Europe
Randomness does not have to be very random, just not the same order for every user.
An additional potential future requirement would be to have a feature where a user can be presented with items that they are expected to like, based on what they have liked before and what else other users with similar tastes have liked.
I have used mostly document based databases before, and the requirements suggest storing lists of liked and unliked items against the users, but this seems very inefficient. I am happy to use a relational database if it would be better suited, or to learn to use any other type of database. I suspect my lack of database experience is preventing me from seeing an obviously better design.