CODEWITHSUNDEEP
phpmysqlalgorithm
PinoyStackOverflower
PinoyStackOverflower

Reputation: 5302

Creating recommendation algorithm base on user interests

I'm currently building an application that would recommend website base on their tag.

On my website when a user registers, it will fill out an interests. So this is a sample interest:

football, model trains, hockey

So this is separated by commas. So when the user clicks on register that will be saved in my database. This is the design of my database.

userID |  name     | interest
001    | John Doe  | sports, model trains, hockey

So on the other hand, I also have users in my sites who uploads website URLs and also creates a tag related to it. So this is my database design for that:

    postID  |  title           | tags
     001    |  techcrunch.com  | technology,softwares,startups
     002    |  nba.com         | basketball,sports,all-star
     003    |  tmz.com         | gossip, showbiz

So the logic for this one is that, I wanted to recommend NBA.com to user John Doe since NBA.com has a tag of sports and John Doe's interest has a sports tag.

Do you have any idea how to do that one? Just a follow up question, Is the database design correct or should I create a new table to store all the tags. Something like that (not sure though).

Your help would be greatly appreciated and rewarded! Thanks in advance! :)

Upvotes: 1

Views: 867

Answers (4)

Havelock
Havelock

Reputation: 6968

It would be much better to store the tags separately. So that you have a table for the tags and two more tables - one for the relationship between users and tags, and one for the relationship between posts and tags. 

users
----------------------------------------
userId |   name    |   password   | ....
  1    | John Doe  | $p$fgA       |  

tags
--------------------
tagId  | tagname
1      | basketball
2      | hockey

user_interests
----------------------------
id   |   user_id  |  tag_id
1    |  1         |  1
2    |  1         |  2

post_tags
--------------------------
id  |  post_id  |  tag_id
1   |  1        |  2

Then you use JOINs to get the required information

Upvotes: 1

Marcus
Marcus

Reputation: 8669

I would have normalized the database so that you have tags in a separate table and relationship tables to connect with it. As such:

User table:

UserId Name
001    John Does

TagUserRelation

UserId TagId
001     001

Tag table:

TagId TagName
001   Sports

TagUrlRelation

TagId Url
001   nba.com
001   nhl.com

To increase performance I would have continued by creating indexed views with the necessary joins and implementing stored procedures to work with them.

An alternative, as mentioned, is full text search but this will be much slower and generally not considered good database design in this case.

Upvotes: 2

Adder
Adder

Reputation: 5868

You should create two separate table which hold single tags, several for each person or post. You can create a multi-column primary key for it if you wish.

userID | interest
001    | sports
001    | model trains
001    | hockey
...

and the same way for posts:

postID | tags
003    | gossip
003    | showbiz
...

This greatly enhances your chances to write efficient SQL.

Upvotes: 1

solaimuruganv
solaimuruganv

Reputation: 29697

this can be done by using full text search

refer here

Upvotes: 1

Related Questions