Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: When coders don't see eye to eye

    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
    Last edited by pearl2; 10-28-06 at 02:31.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    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...)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    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..."
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    when a post in the post table is to be removed, copy it to the removed table and then remove it!
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, 'remove it' meant to delete the entire row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Got it, many thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •