I have to clarify first that the status column is not intended to reflect the status of a real-world item represented by the record (row) in the table. Rather, it is intended to show the status of the record itself.
It can be as simple as Active/Inactive or complicated like Approved/Deleted/Locked/Pending/Rejected, etc. The status can be stored on a boolean/short integer column or a single-character column, with mappings like
1 = Active or
A = Approved.
The basic idea is to have a recycle bin/trash-like recovery support in the application (and simulate it in the database). If there is a front-end GUI or other interface that can supposedly let a user "delete" records, it doesn't actually delete the record in the table, but simply changes the record status to Inactive or Deleted. When the interface fetches records, it always gets the records that only match the condition that the status is Active or Approved.
If the user makes a mistake and the "deleted" record (in user's perspective) needs to be recovered, a DBA can easily patch the record back to being Active or Approved, which would be better than searching for backups and hopefully finding the original record there. Or the interface itself can let the user view deleted records in a separate view, and restore them as needed, or even permanently delete them (deleting the actual record).
- Is this a good practice, or a bad practice?
- Does it affect normalization of the data?
- What are the potential pitfalls?
- Is there any alternative method of achieving the same goal? (see note)
- How can you have the database enforce unique constraints on the data for a certain status only (but allow any number of duplicates for other statuses)?
- Why don't databases provide a "recycle bin"-like feature or table-tracking/recovery natively, so we can let interfaces delete the actual records without worry?
Note: I read about maintaining a separate history table but that seems worse in terms of storage and having to generate triggers and keep the triggers up-to-date with the tracked table's schema.