One user affects all other users

database-design

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.

Best Answer

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

1. User Profiles and Team Player Nicknames

1.1 Business rules

According to my understanding of your description about the social gaming website, I consider that the following conceptual-level formulations are specially relevant:

A User may be a Player of zero-one-or-many Teams
A Team is made up of one-to-many Players, each of which must be a User

1.2 IDEF1X diagrams

Then, I derived from those business rules the two IDEF1X¹ diagrams that are presented in Figure 1.

Figure 1 - User Profiles and Team Player Nicknames IDEF1X Diagrams

1.2.1 Option A

As you can see in such a diagram, there is a many-to-many (M:N) association or relationship between the entity types Users and Teams, which takes effect in the associative entity type called TeamPlayer.

The UserProfile.UserId PRIMARY KEY (PK for brevity) property migrates² to TeamPlayer as PlayerId, a role name that I have assigned to UserProfile.UserId in order to make it more meaningful in the context of its corresponding entity type, and these properties must be connected via a FOREIGN KEY (FK) defined in TeamPlayer.

On the other hand, TeamPlayer.TeamNumber is associated to Team.TeamNumber by virtue of another FK reference.

With this arrangement, one given UserProfile can hold

one Username when acting as an individual, and
one Nickname when he or she is carrying out the role of a Player for each of the Teams in which the UserProfile in question has joined.

1.2.2 Option B

This diagram is quite similar to the one referred in Option A, but it has an important aspect that is different, since I have moved the TeamPlayer.Nickname property to a separate entity type, which is called Nickname. This configuration is useful in case the Nickname value of a Team Player is optional, i.e., TeamPlayers may choose to provide or not provide a Nickname.

1.3 Team Player Nickname uniqueness

The Option A daigram has an ALTERNATE KEY³ (AK) defined for TeamPlayer, which is composed of two properties: TeamNumber and Nickname. In turn, the Option B diagram exhibits an AK in the Nickname entity type that is made up of TeamNumber and Nickname, as well. In this manner, both methods would provide UNIQUEness for a Nickname value within the context of a determined Team instance.

If you want to prevent the same Nickname value from being repeated in several Team occurrences, then you should set up an AK comprised of only one single property, that is, Nickname. This approach is valid for option A and Option B.

Alternatively, if you want to have the Nickname property set as NULLable (i.e., optional), you could define a “filtered” (or “conditional”, or “partial”, depending on the platform of use) UNIQUE constraint in Option A for supplying Nickname uniqueness in relation to a Team but, as I see it, proceeding this way things would get less elegant, so to speak.

2. People, User Profiles and Usernames

I created some more IDEF1X diagrams (presented in Figure 2) depicting different scenarios in which a given Person can have multiple Usernames, aiming to broaden the scope of this post. They are shown in the following section.

2.1 IDEF1X diagrams

Figure 2 - People UserProfiles and Usernames IDEF1X diagrams

2.1.1 Option A

This option is representing the following conceptual business rules:

A Person may log in via zero-or-one UserProfile
A UserProfile has one-to-many Usernames

Thus, it is comparable to your first diagram, but I have added a Person entity type that is optionally connected to UserProfile via the UserId FK definition.

Another important addition is the Username PK, which is comprised of

a FK reference to UserProfile.UserId and
the CreatedDateTime property that represents the instant in which a given Username was inserted.

This allows you to get rid of the Username.UsernameId (or username.id, if you prefer) property.

In this regard, this excerpt from your question is very important:

So, in this design, only one row in username per user will have active set to true. This smells a bit to me. What if no rows for a given user have active set to true?

Yes, you should establish a policy that states how many Username instances can have the IsActive property set to 'TRUE' with respect to a precise UserProfile, whether exclusively one Username that is “active” at a given point in time, perhaps two, three, etc., or none at all. As you know, each possibility demands distinct validation methods.

But, if a certain User can have multiple Usernames, I do not think that there can be a problem if such a User does not have any “active” Usernames, since the log in credentials are the Email and Password. In this case, when no Username is “active”, you could display some sort of a “default message” indicating that situation.

2.1.2 Option B

This method is very alike to Option A, but it is less flexible, since I have integrated the UserProfile credentials into the Person entity type. By proceeding this way, you would force every Person to have a LoginEmail and a Password.

Conversely, Option A permits you to retain Persons (or People) that are not related to the User context in a clean and separate manner, without employing NULLable (i.e., optional) properties.

2.1.3 Option C

The diagram displayed as Option C describes the statements that follow:

A Person may log in via zero-or-one UserProfile
A UserProfile holds zero-one-or-many entries in UsernameHistory

This method implies notions pertaining to the subject known as temporal databases. You may find my take on Scenario A and my response to Scenario B of relevance in this respect.

With this approach —once the schema depicted in this diagram is built on a certain SQL environment— you could retain the “active” Username in a, say, UserProfile table and all the “past versions” in a, say, UsernameHistory table.

As per the data manipulation factor, basically, every time that a UserProfile UPDATEs his or her Username, you save a copy of the last Username version in the UsernameHistory table. Each History Version is uniquely identified by a combination of the Date and Time datum in which such row was modified and, or course, the UserId FK that links the row in question with its respective UserProfile.

As in any relational database project, you should seriously consider using ACID TRANSACTIONS in order to guard your data integrity and consistency.

2.1.3 Option D

Let us suppose that, in your domain of discourse of interest, a Person can perform two roles: Administrator and Author. This is where Option D comes into play, and the next assertions are useful in delineating our informational diagram:

A Person may be zero-or-one Administrator
A Person may be zero-or-one Author
A Person may login via zero-or-one UserProfile

In this way, a Person holds his or her particular “active” Usernames in his or her specific UserProfile and, also, can possess a Handle in case said Person is an Administrator and another different Handle when carrying out the role of Author.

3. Possibility with the common ASP.NET Users and Roles approach

You mentioned via comments that you are using ASP.NET MVC as your application development platform and, although I am not sure if you are as well utilizing the common (or default) ASP.NET course of action for handling Users and Roles, this section can serve as a good reference assuming you have to deal with the typical conditions.

3.1 ASP.NET Users and Roles approach diagram

The approaches suggested in Figure 3 appear feasible since you would not need to modify the tables that are usually created in ASP.NET apps. As you can see, you would only have to create an additional table for retaining the Handles (or Nicknames, or Aliases, if you prefer) that a given User may (or may not) have in regard to a certain Role assigned to such User in your application program. In other words, the same User could hold multiple Handles, and the context for each Handle instance would be an exact Role performed by the User at issue.

Attaching a Handle column directly to the UsersInRoles table could be possible too, but it may have unfavorable consequences in the ASP.NET authentication and authorization processes, aspects that, as you know, are of the greatest importance for the security of any app, so this method would require especial research and testing.

Possibility with the common ASP.NET Users and Roles Arrangement

3.1.1 Option A

This method depicts a Handle table that is connected to the UsersInRoles table by virtue of a composite FK that consists of the UserId and the RoleId columns. Said Handle table has an AK set up for, precisely, the Handle column, which implies that a specific handle value* could only be INSERTed in one single row.

3.1.2 Option B

In this option I have portrayed a Handle table that also makes a reference to the UsersInRoles table by means of a FK composed of the UserId and the RoleId columns, but it shows a slightly different approach that displays an AK constituted by the RoleId and the Handle columns, which would open the chance to have the same handle value repeated in multiple rows, but only in combination with disctinct RoleId values.

Notes

_{¹ Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.}

_{² IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.}

_{³ An ALTERNATE KEY is a property (or a combination of properties) that holds values that uniquely identify an entity type occurrence but was not chosen as the PK of the pertinent entity type; each entity type can have zero, one or more ALTERNATE KEYs. In an IDEF1X diagram, they are indicated as “AK” plus its respective number, e.g., AK1, AK2, etc. They are usually declared in a logical-level SQL DDL design via (i) a single- or multi-column UNIQUE constraint along with (ii) one or more NOT NULL constraints.}

Postgresql – Organizing Relational Database Logical Model with Varying Categories

Something like this,

CREATE SCHEMA catalog;

CREATE TABLE catalog.item   ( item_id  serial PRIMARY KEY, item_name  text );
CREATE TABLE catalog.color  ( color_id serial PRIMARY KEY, color_name text );
CREATE TABLE catalog.size   ( size_id  serial PRIMARY KEY, size_name  text );
CREATE TABLE catalog.item_color (
  item_id int  REFERENCES item,
  color_id int REFERENCES color
);
CREATE TABLE catalog.item_size (
  item_id int  REFERENCES item,
  color_id int REFERENCES size
);

Now, we need orders

CREATE SCHEMA orders;

CREATE TABLE orders.shipment (
  shipment_id serial PRIMARY KEY,
  customer_name text,
  customer_address text
);

CREATE TABLE orders.item (
  item_id serial PRIMARY KEY
  item_id int    REFERENCES catalog.item,
  color_id int   REFERENCES catalog.color,
  size_id int    REFERENCES catalog.size,
);

CREATE TABLE orders.shipment_item (
  shipment_id int REFERENCES orders.shipment,
  item_id     int REFERENCES orders.item
);

to query the catalog,

SELECT item_name, color_name, size_name
FROM catalog.item
LEFT OUTER JOIN catalog.item_size USING (item_id)
LEFT OUTER JOIN catalog.item_color USING (item_id)

Then you just have a shipment schema which includes different order items.

Now for your categories, you can either create a category table that has (1) Clothing (2) Footwear (3) Other. in the same style, or you can just add a tags column to catalog.item and tag them with clothing and footwear or whatever. Whatever you want to do..