Most databases I used have a Status column we could mark as active, inactive, or deleted. That way, you can see what records were marked as deleted and change them back in case of accidental deletion.

Keep record retention with the Date_Modified column so you can use SQL delete to remove those deleted records that are older than a year or so.

▲

arielcostas 7 months ago | parent | next [-]

I do something similar, but instead keep a "date_deleted" column null by default, and the "active" column as a boolean.

That way, I kill two birds in one stone by having a dedicated column for last deletion (instead of updating a record that is supposedly deleted) and the status just as a boolean instead of some enum, or integer or string.

▲

alexanderscott 7 months ago | parent | prev [-]

this is a “soft delete”. as the author notes, depending on the nature of the data being stored a soft delete does not meet the requirements of many data privacy laws and compliance regulations (like GDPR’s right to erasure).

	▲	kijin 7 months ago \| parent \| next [-]
		There are different kinds of soft delete. I've had cases where the rows in question absolutely could not be hard deleted, because of legacy foreign key relations. But the PII in those rows had to go. So we did a kind of "firm delete" by setting all columns (except the PK and a few necessary flags) to their default values and/or null.
	▲	isbvhodnvemrwvn 7 months ago \| parent \| prev [-]
		And in postgres soft delete is more expensive than a regular delete because it's effectively an insert and update, while delete is just an update.