Physical vs. logical (hard vs. soft) delete of database record? [closed]

by

Advantages are that you keep the history (good for auditing) and you don’t have to worry about cascading a delete through various other tables in the database that reference the row you are deleting. Disadvantage is that you have to code any reporting/display methods to take the flag into account.

As far as if it is a common practice – I would say yes, but as with anything whether you use it depends on your business needs.

EDIT: Thought of another disadvantange – If you have unique indexes on the table, deleted records will still take up the “one” record, so you have to code around that possibility too (for example, a User table that has a unique index on username; A deleted record would still block the deleted users username for new records. Working around this you could tack on a GUID to the deleted username column, but it’s a very hacky workaround that I wouldn’t recommend. Probably in that circumstance it would be better to just have a rule that once a username is used, it can never be replaced.)

More Related Contents:

  1. Are soft deletes a good idea? [duplicate]
  2. How to implement a many-to-many relationship in PostgreSQL?
  3. How to create multiple one to one’s
  4. What are best practices for multi-language database design? [closed]
  5. Strings as Primary Keys in SQL Database [closed]
  6. Database Design for Revisions?
  7. A beginner’s guide to SQL database design [closed]
  8. Storing money in a decimal column – what precision and scale? [closed]
  9. single fixed table with multiple columns vs flexible abstract tables
  10. Primary key or Unique index?
  11. How to design a database for User Defined Fields?
  12. Composite Primary key vs additional “ID” column?
  13. Database EAV Pros/Cons and Alternatives
  14. Max length for client ip address [duplicate]
  15. Name database design notation you prefer and why?
  16. MySQL – how to front pad zip code with “0”?
  17. Use email address as primary key?
  18. Composite Primary Keys : Good or Bad?
  19. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  20. SQL: Using NULL values vs. default values
  21. What does ON [PRIMARY] mean?
  22. What is the best way to implement soft deletion?
  23. Relational Data Model for Double-Entry Accounting
  24. Temporal database design, with a twist (live vs draft rows)
  25. Historical / auditable database
  26. Single or multiple databases
  27. What is a Covered Index?
  28. What are the benefits of using database cursor?
  29. What is semi-join in database?
  30. Is there a severe performance hit for using Foreign Keys in SQL Server?

Leave a Comment