What are the potential pitfalls of using a separate table for storing "deleted" records in PHP?

Potential pitfalls of using a separate table for storing "deleted" records in PHP include increased complexity in querying data, potential for data inconsistency between the main table and the "deleted" table, and increased storage requirements. To solve this issue, a common approach is to add a column in the main table to mark records as deleted instead of moving them to a separate table.

// Example of adding a 'deleted' column in the main table
$sql = "ALTER TABLE your_table ADD deleted BOOLEAN DEFAULT 0";

// Example of updating a record as 'deleted'
$sql = "UPDATE your_table SET deleted = 1 WHERE id = 123";

// Example of querying non-deleted records
$sql = "SELECT * FROM your_table WHERE deleted = 0";