1

I'm completely new to working with databases (other than retrieving data from them as a front end developer.) and have an idea I was toying with. However, I'm struggling to find exactly what to Google due to my lack of knowledge of DBs and the terminology surrounding them.

I have a database that houses user's ratings of various items. One user can have one rating per item and can rate as many different items as they want. The goal would be to find other users who have similar items rated similar ratings and use their data to recommend new things to the first user. This means any single rating has some level of reach to every user in the database. (A similarity score of some kind). I.e. when I say I like item 1, the ratings of anyone else who liked item 1 are now of higher value to me. And anyone who didn't like it, their ratings are of lesser value to me.

I know for sure people do these things (Netflix, Amazon, Google) but I'm honestly not sure where to begin learning how. Any tips are appreciated.

  • Have you got a particular RDBMS in mind? SQL Server has Graph tables, for example, which might be the solution you need. – HandyD Apr 8 at 3:46
  • Hey @HandyD! I was actually thinking about using a nosql db such as simpledb, mong or Dynamo. But I can definitely look into graph tables, thanks! – josh roberts Apr 8 at 10:23
0

A Graph Table in SQL Server should suit this purpose. You can use a graph table to create complex many-to-many relationships using Nodes and Edges then perform complex association queries with relative ease. Below is a rough example of a 'Netflix-style' recommendation based on previously liked movies:

Setup:

USE master
GO
IF EXISTS (SELECT * FROM sys.databases WHERE [name] = 'RatingsDB')
    DROP DATABASE RatingsDB
GO
CREATE DATABASE RatingsDB
GO
USE RatingsDB
GO
CREATE TABLE Users (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    UserName NVARCHAR(255)
) AS NODE
GO

INSERT INTO Users (Username)
VALUES ('Bob'), ('Carol'), ('Frank'), ('Beth'), ('Jan'), ('Marco')
GO

CREATE TABLE Movies (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    MovieTitle NVARCHAR(255)
) AS NODE
GO

INSERT INTO Movies (MovieTitle)
VALUES ('Red Dawn'), ('Titanic'), ('Jurassic Park'), ('Clerks'), ('Four Weddings & A Funeral'), ('Saving Private Ryan'), ('Mallrats')
GO

CREATE TABLE Likes AS EDGE
GO

CREATE TABLE SimilarMovies AS EDGE
GO

INSERT INTO SimilarMovies
VALUES (
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Red Dawn'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Saving Private Ryan')
), (
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Four Weddings & A Funeral'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Titanic')
), (
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Clerks'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Mallrats')
)


INSERT INTO Likes
VALUES (
    (SELECT $node_id FROM Users WHERE UserName = 'Bob'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Red Dawn')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Carol'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Titanic')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Frank'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Mallrats')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Beth'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Jurassic Park')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Jan'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Four Weddings & A Funeral')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Marco'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Clerks')
)

Query Example:

SELECT Users.UserName,
    RecommendedMovie.MovieTitle AS Recommendation,
    WatchedMovie.MovieTitle AS BasedOn
FROM Movies WatchedMovie, Users, Likes, Movies RecommendedMovie, SimilarMovies
WHERE MATCH(Users-(Likes)->WatchedMovie -- Find movies that a user likes
    AND WatchedMovie-(SimilarMovies)->RecommendedMovie) -- Find similar movies to the one the user liked
UNION
SELECT Users.UserName,
    RecommendedMovie.MovieTitle AS Recommendation,
    WatchedMovie.MovieTitle AS BasedOn
FROM Movies WatchedMovie, Users, Likes, Movies RecommendedMovie, SimilarMovies
WHERE MATCH(Users-(Likes)->WatchedMovie -- Find movies that a user likes
    AND WatchedMovie<-(SimilarMovies)-RecommendedMovie) -- Find similar movies to the one the user liked

Results:

UserName    Recommendation              BasedOn
-----------------------------------------------------------------
Bob         Saving Private Ryan         Red Dawn
Carol       Four Weddings & A Funeral   Titanic
Frank       Clerks                      Mallrats
Jan         Titanic                     Four Weddings & A Funeral
Marco       Mallrats                    Clerks

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.