title: Database - Soft-delete or separated log table
body: <p>I'm building a REST API which has the followings endpoints:</p>
  
  <pre><code>GET /api/v1/categories - Get all categories
  DELETE /api/v1/categories/$id - Delete a specific category
  </code></pre>
  
  <p>When the client requests all categories, the server has to return all categories with the HTTP header <em>Last-Modified</em>.</p>
  
  <p>Once the a category has been deleted and the client requests all categories with HTTP header <em>If-Modified-Since</em> older than the deletion time, the server shall returns all categories, without the deleted one, but with the HTTP header showing the last modified time, even if this time was the time of the deletion.</p>
  
  <p>I'm thinking about in two approach to implement this:</p>
  
  <ol>
  <li>Add a column with a flag indicating if it was deleted (soft-delete);</li>
  <li>Create a table to record tables modifications.</li>
  </ol>
  
  <p>In the first approach, I'll add a column in the category table that will indicate if the record has been deleted or not. In this way, with this column and the column that stores the last modification timestamp, I can check if any modifications happened after the given timestamp.</p>
  
  <p>In the second approach, I'll add a table that records the last modification in any table. Each line of this table is the last modification which happened in any other table of the system. In this way, when a category is deleted, the record in the log table which holds information about category table is updated indicating that the last operation was a delete with the timestamp of the operation. In this way, when I have to check if any changes happened in the category table after a specific timestamp, first I check the log table and decide if I'll query the category table.</p>
  
  <p>Even though I've used the category as the example, this problem happens to other resources of my API and the solution shall be used to them in the same way.</p>
  
  <p>What do you guys think about the presented approaches to solve this problem?</p>