I am working on a php/mysql driven website. It allows users to make posts and other users to comment on them.
I am currently working on a notification system where
the user will be notified whenever somebody makes a post.
OR
if the user has commented on a post, the user will be notified of further new comments.
My current method is a subscription based structure, where when somebody makes a comment or post, their user_id and post_id will be inserted into a subscribed’ table. Is this an efficient method?
In this case (these are the only 2 columns of the table, right?) I’d make them the primary key.
For columns that are not the primary key of the table, you can add a unique index.
ordinarily with a UNIQUE constraint but in your case i would recommend the PRIMARY KEY
since you said the table consists of only user_id and post_id, that should be the PRIMARY KEY
CREATE TABLE subscriptions
( user_id INTEGER NOT NULL
, post_id INTEGER NOT NULL
, PRIMARY KEY ( user_id, post_id )
, INDEX rev ( post_id , user_id )
);
the PRIMARY KEY ensures uniqueness, and is used for searches for posts where the user_id is known, while the “rev” index is used for searches for users where the post_id is known
“My current method is a subscription based structure, where when somebody makes a comment or post, their user_id and post_id will be inserted into a subscribed’ table. Is this an efficient method?”
A user can make posts and other users can comment on them.
What is the best way to build a notification system, where a user is notified of when somebody makes a comment and when a user that has commented receives new comments.
Something similar to facebook notifications, but a lot more basic.