I plan to use the following formula to calculate trending messages:
Trending Score = (p - 1) / (t + 2)^1.5
p = votes (points) from users. t = time since filing in hours.
I am looking for advice on how to structure my database tables so that I can query trending messages with DynamoDB (Amazon's nosql database service).
DynamoDB requires a primary key for each table element. A primary key can consist of two parts: a hash attribute (line or number) and a range attribute (line or number). The Hash attribute must be unique for each item and required. The Range attribute is optional, but if DynamoDB is used, it will create an index of the sorted range in the range attribute.
The structure that I had in mind is as follows:
TableName: users
HashAttribute: user_id
RangeAttribute: NONE
OtherFields: first_name, last_name
TableName: Messages
HashAttribute: post_id
RangeAttribute: NONE
OtherFields: user_id,title, content, points, categories[ ]
TableName: Categories
HashAttribute: category_name
RangeAttribute: post_id
OtherFields: title, content, points
TableName: Counters
HashAttribute: counter_name
RangeAttribute: NONE
OtherFields: counter_value
So, here is an example of the types of queries that I would make with the following table setup (example: user_id = 100):
User Action 1:
The user creates a new message and places a message for two categories (baseball, football)
Request (1):
Check the current value for counter_name = 'post_id' and increment + 1 and use the new post_id
Query (2): Insert the following into the Messages table:
post_id=value_from_query_1, user_id=100, title=user_generated, content=user_generated, points=0, categories=['baseball','soccer']
Request (3):
Insert the following into the category table:
category_name='baseball', post_id=value_from_query_1, title=user_generated, content=user_generated, points=0
Request (4):
Insert the following into the category table:
category_name='soccer', post_id=value_from_query_1, title=user_generated, content=user_generated, points=0
, :
1.
2.
3.
- , , ? , , DynamoDB?