Search code examples
mysqlsqloracle-databasesoft-delete

Different views for different users

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

Solution

  • 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