-1

Say I have a database with 3 tables: Stream, Tag and Post

  • Stream has one tag. Multiple streams can have the same tag.
  • Stream is owned by one user.
  • A tag has many posts.
  • Different tags can have the same post.
  • Each user that opens his stream gets it's posts (via the tag) and can delete (or hide) the posts he doesn't want to see (but only for him, not for others).

What would be the appropriate way to achieve this? Obviously I'll need some kind of soft-delete flag on posts. I currently have 2 ideas in mind:

  1. Create another table that will act as the view of the stream with columns stream_id, post_id and is_deleted
  2. Add deleted column to Stream that holds a JSON array of deleted posts' ids. The problem with this approach is querying based on is_deleted state.

Are there any better ways?

Note: Currently, I need Tag to stay in it's own table, rather than storing it as text in a column in Stream

tamir
  • 3,207
  • 2
  • 33
  • 51
  • The second "solution" brings more problems than it solves. Forget about it. – axiac Jul 17 '16 at 21:32
  • check out my [Junction Tables](http://stackoverflow.com/a/32620163). They connect tableA to tableB. In yours, have your tailored columns. – Drew Jul 17 '16 at 21:33
  • Why do you want to delete a post? You say *"can delete (or ban) the posts he doesn't want to see (but only for him, not for others)."* The user doesn't delete a post, they only remove the post from their stream. A stream has many posts and a post can appear in more than one stream. This is a many-to-many relationship that has to be implemented using a link table. When the user deletes a post you only need to delete a row from this table. You can also add an "is_deleted" column to this table and set "is_deleted = 1" for soft delete if you want to allow the user to undo the deletion... – axiac Jul 17 '16 at 21:37
  • ... A better solution for this situation (when the deletions can be undone) is to move the row into another table (that contains only the posts deleted by each user). – axiac Jul 17 '16 at 21:38
  • @axiac My bad. I've tried to simplify my real use case by omitting a mid-table. Sorry about that! Actually, `Stream` has `Tag` (currently just one per stream, so many-to-one) and `Tag` has posts.. so the many-to-many relationship is between the `Tag` and `Post`. – tamir Jul 17 '16 at 21:55
  • @Drew so basically another many-to-many table – tamir Jul 18 '16 at 05:00
  • It would seem to suggest that :p – Drew Jul 18 '16 at 05:01

1 Answers1

0

You should have a new table with the columns post_id, is_deleted & user_id. In this way you can manage it easily or else it will be hectic work as per your options are considered. The table name will be like cross reference table ex: user_deleted_posts_xref

vvr02
  • 611
  • 2
  • 12
  • 22