How to properly design a database for friend tape?

Question

Database

Good afternoon!

Tell me how best to organize the user's news feed on the site.
The following events should be shown in the feed:
- adding to friends
- creating entries by friends (posts, questions)
- commenting on my entry
- replying to my comment
- commenting on entries added to favorites

Options:
1. Create a UserEvent table with fields:
- id_user - identifier user for which to display the event
— actionType — action type
— id_rec — link to the event source record
— createDate — event date
— id_author — event author
In this table to write all events for each user. Those. the user created a post, for each friend an entry was created in this table. Sampling by table should be done with a filter by the id_user field.

Cons:
- creation load (create entries in UserEvent for each friend. If there are 2000 friends, then create 2000 entries in UserEvent each time a post is created?)
- rapid growth in database size
Plus:
- simple selection, faster
2. Create a UserEvent table with fields:
— actionType — action type
— id_rec — link to event source record
— createDate — event date
— id_author — event author

Create one entry per event in the table. Sampling is carried out on two tables UserEvent + a table with data about friends and data about favorite posts and questions.

Cons:
- More complex selection
Plus:
- The database does not grow relatively fast
- There is no load when creating a record (you do not need to create a record in the UserEvent table for each user)

Advise how best in terms of performance? Maybe there is some other option?

Answer 1

We did it based on the second option, while caching the id of friends (i.e., as a result, there was a simple request like select * from feed where author_id in (1,2,3). And everything was simple until the following requirements:
1. It was necessary display only those
friend posts that were created after adding a friend (this was later abandoned due to excessive overhead and dubious benefits) if at least someone commented on it.As a result, they simply entered one more field with the date of the update and sorted by it)
3. Privacy for entries that link to another entry. It seems that your friend liked the post of his friend, who is not your friend. In this case, if the original post is available only to friends (and you are not friends with him), then it should not have been shown. (In this case, the original author always remained the author, and the message was marked as a link and the user who retweeted it was recorded in a separate field).
There were also specific requirements:
1. The user could be ignored for N days. During this time, his messages were to be ignored.
2. The post could be marked as favorite, and it constantly hung in the top (it was necessary for monitoring)
PS By the way, in the first variant it is more profitable to create 2 tables. One is with messages. Second EventId,UserId.

Answer 2

NoSQL - as an option, why don't you consider it?

Answer 3

From a performance point of view, writing a daemon in C/Java/.NET is more profitable than torturing the database.