Best way to store user activities

Hello!

I'm creating a website where users can put like to a post, where they can write a post, where they can put dislike to a post and so on. I'm writing also the "timeline" algorithm. I must write an algorithm that selects foreach user the posts to append to the "wall", like facebook.

So I thought to store the user's activities, like the like action, the dislike action, the comment action and so on. So which is the best practice? I thought about creating a table and store in it all activities, but:

EXAMPLE: I've an array of user's id like that: 

PHP Code:

$ids = array(2, 3, 4); 


suppose that my id is 1. I follow the user number 2, number 3 and number 4. So foreach user that I follow I'll search some activities in a time range and then I'll insert this data to my timeline. So, If I had millions/billions of data? Could there be speed problems?

Maybe you can reccomend me something that does not come to my mind ;P

Thank you!

Your method is correct. You would have a table of user_id, user_following_id, and then select the timeline for user_id in a single query, gathering the latest posts of all the users in your array of user_following_id's including the actual user_id too of course. This should be fine on a normal single server database for a large amount of data.

As for 'billions', yes you will have lots of speed problems. Dealing with big data sets like that is a massive task, one I have never had the pleasure to work on, except once on a very big pensions project long ago, but I was just a junior doing tiny parts of a massive code base.

But working on users to get up to millions is quite a task in itself, and by then you would be in a position to not be working on the project alone in all probability, with hopefully the investment to put into place distributed data servers. For now, I would put the problem of billions away for a while, you may never get anywhere near that problem.

Paul.

Your method is correct. You would have a table of user_id, user_following_id, and then select the timeline for user_id in a single query, gathering the latest posts of all the users in your array of user_following_id's including the actual user_id too of course. This should be fine on a normal single server database for a large amount of data.

As for 'billions', yes you will have lots of speed problems. Dealing with big data sets like that is a massive task, one I have never had the pleasure to work on, except once on a very big pensions project long ago, but I was just a junior doing tiny parts of a massive code base.

But working on users to get up to millions is quite a task in itself, and by then you would be in a position to not be working on the project alone in all probability, with hopefully the investment to put into place distributed data servers. For now, I would put the problem of billions away for a while, you may never get anywhere near that problem.

Paul.

Thank you for your reply!
Yeah, I know that maybe I'll never work on billions data, but I want to learn also some "hard" technologies. 

However, I created a MySQL table "users_actions" with this fields: user_id, post_id (that is NULL when the action is about following user), action (an enum with the type of action [like, dislike, comment, follow, creation post]), action_at (a timestamp), user_two_id (when I follow an user I insert the id of this user).

To not overload the data in the table, I update the action_at for action like dislike or like. Instead: user has put like to a post, and the day after he puts dislike to the same post. I update only the past row (I update action's field and action_at's field), without creating new one. Is it a good thing?

Another question. I want to add also the emoji in my post. I know that there is some replace ("" => replace to image.gif"). Actually I filter the messages like that:

PHP Code:

$message = filter_var($this->input->post('message'), FILTER_SANITIZE_STRING) 


so if I write a message like "<script>alert("hello");</script>" I'll have only "alert("hello");". Now: there is some way to add the emoji (instead the emoji in iPhone's keyboard or some android phone)? Maybe editing the charset? (now it is utf-8)..

The last question. I want also to create a server to manage the notification foreach user. In the past I used NodeJS with socket.io, but I don't like it so much. Maybe you can reccomend me something about that also . I was looking for something in python, some websocket in .py..

However, thank you =)!

CodeIgniter had an emoji files and helper I have them still but they were posted some place on here,
cannot remember where.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

I you have to deal with millions of records, consider a comparison between MySQL and PostgreSQL. Maybe you would need to make an experiment.

I you have to deal with millions of records, consider a comparison between MySQL and PostgreSQL. Maybe you would need to make an experiment.

Thank you for the idea. Actually I'm using MariaDB.

About emoji: I changed my charset to utf8mb4 and utf8mb4_unicode_ci the collation and now the emoji work fine

Watch out for your field lengths, the newer character sets use 4 bytes compared to 3 bytes.

Effects index's etc;

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

I am following this thread as I am also planning to open a similar websites. Although I am not a developer but it will help to have the solution.