I have two questions.
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.
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?
how can I stop a duplicate row entry?
e.g if the table data is:
It won't duplicate another entry of John, 102.
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
Edit: curses, sniped again!
One more question. How do I remove notifications when the user has viewed them?
could you please be a bit more specific? how does this relate to duplicate rows?
Hello, I speak about my notification system.
"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?"
I am working on a php/mysql driven website.
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.
oh, i see
well, to remove a notification, you would delete that row in the table
yes, that's efficient