Describe how you would implement soft deletes in an address table and the benefits.

Describe how you would implement soft deletes in an address table and the benefits.

• A. Physically delete the row on delete to simplify storage.
• B. Only set a boolean is_deleted flag and never filter on it in queries.
• C. Add a deleted_at timestamp and filter on is_deleted=false for most queries; soft deletes preserve history, allow undelete, and aid auditing.
• D. Move deleted rows to an archival table.

Answer: (C)

Soft deletes mark a row as deleted rather than removing it from the table. By recording the deletion with a timestamp, typically called deleted_at, you can tell at a glance when the row was deleted. For most operations, you then filter out records where deleted_at is set, so you only work with active data.

This approach preserves the full history of changes, which is valuable for auditing and reporting. It also makes it possible to undelete a record by clearing the timestamp, and it helps maintain referential integrity since the row still exists for any related records or historical queries.

If you relied on a simple boolean flag without consistently filtering on it, deleted rows would still appear in queries, defeating the purpose of soft deletes. Moving deleted rows to an archival table is an alternative, but it adds complexity and separate storage management. Physical deletion discards history entirely.