I am working on an application which requires to show different posts on user's timeline(something like Twitter).
A User's timeline consists of following:
Reports which user created
Reports which other user created and fall under his/her 'alert location' radius
- Reports posted by other users which he/she follows (Even if that report does not fall inside 'alert location' radius)
- Report Shared/Re-tweeted by user he follows (Even if that report does not fall inside 'alert location' radius)
- Reports which other user created and fall under his/her 'current location' radius when a user is travelling.
- Follow notification- When someone started following him/her
- Message notification- When someone sent a message to him/her
Note:- Once a report appear in user's timeline then it will not disappear even if user move out of location radius or changes 'alert location'. It will only disappear when it is deleted or flagged as inappropriate.
Requirement: I need an optimal DB schema(with SELECT query) so that I can show above mentioned posts on user's timeline.
Here is my current DB structure:
Table: users
UserID (PK)
email
password
Table: alert_locations
alertLocationID (PK)
user_id (FK)
latitude
longitude
radius
Table: followers
follower_id (FK)
following_id (FK)(follower_id + following_id) = PK
Table: reports
reportID (PK)
reported_by_id (FK)
location
latitude
longitude
parent_id (FK- For retweet relations)
Now, taking into the account the above cases of timeline I am thinking of timeline
table with structure shown below:
Table Name: timeline
timelineID (PK)
reference_id (Can be report_id or follower_id or message_sender_id)
user_id (FK)
title
type (1:For Report, 2:Follow, 3:Message)
So, when a report is created, for each user a new row in timeline
table is inserted who is eligible to view that report. Using this table I can query all types of posts for user's timeline.
But as I see, this approach seems to have scaling issues and it does not look wise to insert 'n' amount of rows in timeline
table every time a new report is created.
Is there any other better solution to achieve this?
SELECTs
to judge the schema.timeline
table solution I mentioned is just one of the solution I am thinking about. Sorry, I don't understand when you say "some kind of archiving might be needed" Can you please elaborate what do you mean by "archiving"?