Register user updates
While building a small community networking site, but I was looking for guidance on how best to create a table that will hold the data to be pulled as a stream that shows like:
User X has added a friend!
User Y has commented on a post!
User X changed their profile picture!
User X has changed their motto!
This is currently my setup, but before I continue I want to know if I was on the right track
update_id int PK
member_id int FK
friend_id int FK
update_action text
update_time datetime
update_hidden tinyint
At the moment I was planning to run an additional insert query to update this table on every action when the user does it, not sure if this is the best or in my case a good way to do it. Any advice or advice would be greatly appreciated for your time.
source to share
You can have a table for all the different actions your system should suggest.
CREATE TABLE activity
(
id MEDIUMINT UNSIGNED PRIMARY KEY UNIQUE NOT NULL AUTO_INCREMENT,
description NVARCHAR(126) UNIQUE NOT NULL
);
For example: commented on a post, has a new friend, etc.
And then log all the actions that happen depending on the activity id (so you can manually figure out where in the code which activity to use) and save it something like this:
CREATE TABLE activityLog
(
id MEDIUMINT UNSIGNED PRIMARY KEY UNIQUE NOT NULL AUTO_INCREMENT,
userId UNSIGNED NOT NULL,
friendId UNSIGNED DEFAULT NULL,
acitivityId UNSIGNED NOT NULL,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
hidden TINYINT(1) DEFAULT 0,
FOREIGN KEY(userId) REFERENCES users(id),
FOREIGN KEY(friendId) REFERENCES users(id),
FOREIGN KEY(acitivityId) REFERENCES activity(id)
);
Assuming your users are stored in a table named "users".
Just make sure it easily creates new actions, easily binds events, etc.
source to share