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?