Hi all,

I'm currently involved in a web project with a friend.

It's the first time I'm jointly coding a website with another person. We sometimes have quite different approaches to the same problem, and we fail to see eye to eye.

Here's is one and I hope to seek your advice and opinions.

We are divided over the how to treat the "removal" of a post from a forum. The questionable post will still be somewhere in database, but we need a way to indicate that action was taken against it.

To make things concrete, we've two MySQL tables (simplified) as follows:

posts_tbl
post_id author_id message deleted_flag

removed_tbl
post_id message deleted_by_id date

Let's say we have too approaches A and B.

When a message that is deemed inappropriate is removed via a button click, Approach A goes likes this:

In posts_tbl, the "message" field is updated with the replacement text "Removed by USERNAME" and the "deleted_flag" column with a value of 1 (the default is 0) (Note: the username is available at the point of update, so it's updated into the message filed as part of the replacement text).

In removed_tbl, the original message is inserted into the message field, together with the post_id, the date, the member id of the person carrying out the deletion.

Approach A's points are:

a) When displaying posts in a web page, all posts can be directly retrieved from posts_tbl, including messages that contain "Removed by USERNAME".
b) Saving of processing time because there's no need for additional code to check whether a message was removed.
c) Greater flexibility because different pieces of data are stored in two different tables.
d) Greater expandability. When a different reason for removing the post is needed, a new column can be added to removed_tbl to store that reason.

The sql to retrieve messages from posts_tbl will be straightforward without having to link to removed_tbl to retrieve the username of the person who deleted the post (assuming the other information in removed_tbl is not needed for display). The code will be simpler too.

Approach B is:

removed_tbl should be changed to:
post_id deleted_by_id date

When a removal is carried out:

In posts_tbl, the "message" field remains unchanged while the "deleted_flag" column is updated with a value of 1.

In removed_tbl, the necessary information is inserted (post_id, member id of the person deleting the message, date).

Approach's B points are:

a) The original message should not be touched in posts_tbl.
b) There's duplication of data if the message field of posts_tbl is updated with the string "Removed by ...".
c) There's also duplication of data because "Removed by ..." and "deleted_flag" both indicate the particular post has been removed.
d) The string "Removed by ..." should be added in the code for outputting to a webpage, so that it doesn't get stored anywhere. When a different wording is needed e.g. changing "Removed by ..." to "Deleted by ...", only the hard-coded string needs to be changed.
e) posts_tbl stores all original posts, so it's easier for retrieval.

Cons of Approach B:

a) The sql to retrieve messages will require an additional condition to link posts_tbl to removed_tbl to retrieve the username of whoever deleted the post.
b) The code will need to check for whether a post has been removed so it can output the string "Removed by ...".

There are other ways for sure. But between the two outlined above, which is a better solution?

It's a bit longish but I need to paint as accurate a picture as possible.

Would appreciate your enlightment

Thank you and I look foward to hearing from you

Approach C:

when a post in the post table is to be removed, copy it to the removed table and then remove it!

none of this deleted=1 nonsense

Thanks, r937

Does that mean the removed table will store the following information:

post_id message deleted_by_id date

(where: post_id is the id of the message from post table, message is the same message from the post table and so on...)

yes

the removed table would have all the columns of the posts table plus a few more, for things like removed_by, removed_date, removed_reason, etc.

can you see the pros and cons of this method too?

There's less duplication - removed_tbl is only touched whenever a post is removed. It's also much neater.

On the other, it adds a small overhead to the sql because a join is needed. You also need to have the code to check for a removed post to add the string "Removed by..."

Many thanks

no, not a join

think about it -- the id is gone from the posts table, so there's nothing to join to

that's why all the columns have to be in the removed table too, plus those extra ones

most of the time your app will need only the posts, and completely ignore the removed table

thus, most efficient sql

occasionally you will want to see posts and removeds, and in that case you use a UNION query

I misunderstood that earlier - I had thought "remove it" meant removing the message from the table, but not removing the entire row that contains the mesage. Am I on the right track?

Many thanks

yes, 'remove it' meant to delete the entire row

Got it, many thanks