Database design for comparing large lists of opinions

Question

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.

Answer 1

This might be better on DBA
A relational database

user:
id int PK
fName string
lName string

item:
id int PK
description string

rating:
id int PK
description string unique constraint

userItemRating:
userID int PK FK to user
itemID int PK FK to item
ratingID in FD to rating

two or more

select item.description 
from item 
join userItemRating 
on item.ID = userItemRating.itemID 
where item.Rating > 3 
group by item.description 
having count(*) > 1 

how to do random is going to be database specific - this is SQL Server this is item not rated for user 5

select top (10) item.description 
from item 
left join userItemRating 
on item.ID = userItemRating.itemID   
where userItemRating.userID = 5 
and userItemRating.itemID is null  
order by newid()

Answer 2

To get you started, you are going to need a database table with both users and items as foreign keys and another field indicating like/dislike/neutral.

UserId (FK User table), ItemId (FK Item table), Rating (signed byte)

I suggest the Rating field be a signed byte with a value of zero representing neutral, -1 representing dislike, and 1 representing like. This will allow you to quickly determine the number of likes and dislikes for a given item, and the average rating.