根据用户兴趣创建推荐算法

Question

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! :)

Answer 1

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.