mysql queries

[Jaynesh]

Hello,

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.
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?

Second question:

how can I stop a duplicate row entry?

e.g if the table data is:

John, 102

It won't duplicate another entry of John, 102.

[guido2004]

Second question:

how can I stop a duplicate row entry?

e.g if the table data is:

John, 102

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.

[r937]

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?

yes

how can I stop a duplicate row entry?

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!

[Jaynesh]

One more question. How do I remove notifications when the user has viewed them?

[r937]

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?

[Jaynesh]

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?"

[Jaynesh]

Hello

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.

[r937]

oh, i see

well, to remove a notification, you would delete that row in the table

yes, that's efficient