Database

Important

We use PostgreSQL as DBMS (Data Base Management System). You will find more information about it here.

Media object definitions

Application

Name	Type	Description
app_id	Integer	Application identifier
name	String	Application name
categorie	Integer	linked categorie (genre)
rating	Float	Average of user ratings
reviews	String	Number of reviews
size	String	Application size
installs	String	Number of installations by unique users
type	String	'Paid' or 'Free'
price	String	Price
content_rating	String	application classification (by age group)
last_updated	String	last update date
current_version	String	current app version number
android_version	String	minimum android version required
cover	String	cover url
popularity_score	Float	popularity score (see here)

Important

Unlike other media, applications are linked to only one genre.

Book

Name	Type	Description
isbn	String	Book universal identifier
title	String	Book title
author	String	Book author
year_of_publication	Integer	Year of publication
publisher	String	Publisher company
image_url_s	String	URL for small image size
image_url_m	String	URL for medium image size
image_url_l	String	URL for large image size
rating	Float	Average rating
rating_count	Integer	Number of user ratings
popularity_score	Float	popularity score (see here)

Episode

Name	Type	Description
episode_id	Integer	Episode identifier
imdbid	String	IMDB identifier
title	String
year	Integer	release year
genres	String	List of genres
serie_id	Integer	Serie identifier
season_number	Integer
episode_number	Integer
rating	Float	Average rating
rating_count	Integer	Number of user ratings

Game

Name	Type	Description
game_id	Integer	Game identifier
steamid	Integer	Steam game identifier
name	String	Game name
short_description	Text
header_image	String
website	String	URL of official game website
developers	String	developers company name
publishers	String	publisher company name
price	String
recommendations	Integer	Number of user recommendations
release_date	String
rating	Float	Average rating
rating_count	Integer	Number of user ratings
popularity_score	Float	popularity score (see here)

Movie

Name	Type	Description
movie_id	Integer	Movie identifier
title	String	Movie title
language	String	Main language
actors	String	List of main actors separated by '|'
year	String	Release year
producers	String	List of producers separated by '|'
director	String	Name of the director
writer	String	Name of the writer
imdbid	String	IMDB movie identifier
tmdbid	String	TMDB movie identifier
rating	Float	Average rating
rating_count	Integer	Number of user ratings
cover	String	Cover url
plot_outline	String	synopsis
popularity_score	Float	popularity score (see here)

Series

Name	Type	Description
serie_id	Integer	Serie identifier
imdbid	String	IMDB identifier
title	String
start_year	Integer
end_year	Integer
writers	Text	List of writers separated by ','
directors	Text	List of directors separated by ','
actors	Text	List of actors separated by ','
rating	Float	Average rating
rating_count	Integer	Number of user ratings
cover	String	Cover url
plot_outline	String	synopsis
popularity_score	Float	popularity score (see here)

Track

Name	Type	Description
track_id	Integer	Track identifier
title	String	Track title
year	Integer	Release year
artist_name	String	Artist name
release	String	Realse (album) associated with this track
track_mmid	String	Million Song track identifier
recording_mbid	UUID	MusicBrainz track identifier
rating	Float	Average rating
rating_count	Integer	Number of user ratings
spotify_id	String	Track spotify id
covert_art_url	Text
popularity_score	Float	popularity score (see here)

Other objects definition

Genre

Name	Type	Description
genre_id	Integer	Genre identifier
name	String	genre name
count	Integer	number of user
content_type	Enum	APPLICATION, BOOK, GAME, MOVIE, SERIE or TRACK

Group

Name	Type	Description
group_id	Integer	Group identifier
name	String	Group name
owner_id	Integer	User identifier

User

Name	Type	Description
user_id	Integer	User internal identifier
uuid	UUID	User public identifier
email	String	user email
username	String
password_hash	String	Hashed user password (Bycrypt algorithm)
preferences_defined	Boolean

External

Name	Type	Description
service_id	Integer	Service internal identifier
user_id	Integer	User identifier
service_name	String
access_token	String
refresh_token	String

Schema

User - Content relationship

All content is linked with users by meta_user_X table, it will define all metadata between a content and the user:

Example:

• Explicit metadata: rating
• Implicit metadata: play_count

Content similarity

All content tables have a "Many to many" relationship with itself, it stores the ratio of similarity between two contents (only the highest will be stored).

Content genres

Caution

• We do not have any data allowing us to define the genre of a book.
• The applications we have are linked to only one genre.

User social part

A user can create a group, and add other users to this group, the objective is to be able to make recommendations to the group (e.g. Playlist for a party with friends).

User Interests

A user can like or not a genre.

Content Recommendation for users

All recommendations calculated by Recofinement-engine are stored in this table. This service uses multiple algorithms (called engine) to recommend a user. Each engine calculates a score between 0 and 1 between a user and a content. The closer the score is to 1, the more relevant it will be.

Important

It is not relevant to compare scores between two different engines. That's why we have a column engine_priority

Content Recommendation for groups

The mechanism for the recommendation for a group is the same. We see a group object as a user object.