title: Relational Database: DELETE versus "Mark for Deletion"
body: <p>Recently, I stumbled about the following problem: Given is a simple data model with "Books" and "Authors". Each "Book" has a reference to an "Author". Persistence is achieved with a relational database. Besides adding books and authors, it is also possible to delete them. Usually, if I want to delete an Author, i would perform a SQL <code>DELETE</code> operation and remove the corresponding row. However, I have seen in other projects, people don't call <code>DELETE</code>. Instead, they add some kind of active/deleted flag and mark the corresponding row as "deleted". </p>
  
  <p>My questions are: Is this in general best practice? What are the advantages? My best guess is:</p>
  
  <ul>
  <li>Setting a flag has a better performance than a <code>DELETE</code> operation</li>
  <li>If you run out of space, it is still possible to run a cleanup service which looks for deleted object and removes the corresponding rows</li>
  <li>Setting a delete flag is better for database consistency because a deletion of "Author" in the example above could destroy foreign keys in the corresponding "Book entries.</li>
  </ul>
  
  <p>Anyway, these are just guesses. Does someone know the answer?</p>