vendredi 27 novembre 2015

What would be a suitable database schema for the following scenario?

I have newsid and keyword. Now i have a mapping of keywords to newsid's in a many to many fashion. for eg

**keyword**     **Newsid**
FIFA           221,123,133
Stack          12,2
dragon         23,577,33

Also i have a mapping that should store priority for each keywords for a particular user for e.g. Lets suppose user "mark" has the following keywords with priority for him:-

**Keyword**     **priority**
apple           0.5
jeff jones      0.9 
sugar           0.3



Use cases 
1)to get all the news id that are mapped to a specific keyword 
2)to get all the keyword that are mapped to a specific news id
3)To increase the value of priority for keyword x by value y for user z
4)Retrieve the list of all keywords
5)add a new keyword r for user s with priority f

Note that keywords are not predetermined and can increase to any number,So is the case with News and users.

Flawed solution thus far I have a database with the following structure

Table -News(newsid,story)
Table -Keyword(keyword,[list_of_news_id seprated by comma])
Table -User_preference(userid,[(keyword1 -priority1),(keyword2-priority2)...])
Table -User(userid,name)

The problem with my solution is that to query keyword mapped to a specific id i need to process all tuples of Keyword table.

Moreover to update the priority of a certain keyword for a certain user , I would have to first retrieve all keyword priority pair for him(since they are in a common column) and then select my desired keyword and manually change it using some text processing and replace the value with new one.

Please suggest me a suitable database schema that fulfills all my use case requirements and that doesn't need to alter the database schema each time with addition of new keywords/news/users.

Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire