database design for capturing trending keyword

Question

Hi I need some help on creating database design for capturing and scoring trending keywords. What i have so far is:

keyword_id int auto_increment
keyword varchar
description tinytext
date_trend_started datetime
mention_count int
ranking int
day_at_top datetime
days_at_top int

what else needs to be considered

Answer 1

I don't think this design captures data at the fidelity you may want. It would be more beneficial to keep data historically. Split your data into multiple tables:

• Keyword data (keyword, description, ID)
• Mention (keywordID, mention source, datetime)

You can use this to compile summary tables for querying, such as Ranking per Day, where you would have one record per keyword per day and summarize mention count, ranking, movement from previous day, etc.

Date      | KeywordID | Rank | Movement
"25/6/08" | 576426    | 17   | -3
"26/6/08" | 576426    | 15   | 2

This gives you enough to calculate the other things you want, such as trend started or days at top, between any two time periods.

The above is by no means comprehensive - just examples to show you should be thinking about your data in many dimensions.

Answer 2

Depending on what your ultimate goal is, you may want to store the previous several days' mention counts to get an idea of the rate of change, i.e the derivative.