Algorithm suggestions for handling CRUD Soft Deletes for delete and restore with multiple owning models

Question

I have an active record based ORM with soft deletes (Laravel Eloquent to be specific) in an SQL database.

The 'soft delete' behavior that is built in to the ORM is: If the record is not deleted, then deleted_at is null. When a record is soft deleted, the deleted_at field is populated with a timestamp.

The ORM uses the 'deleted_at' field internally in its logic as well as creates the field. It is difficult to change the type of deleted_at.

I have the situation where I have three models (tables), A, B, and C. Model C belongs to both A and B. Neither A or B belong to each other. So table C contains columns to hold foreign keys to A and B.

For C to be valid, both of its parents must not be deleted. If either A or B is deleted, then C must also be deleted.

However, if A or B is deleted, then restored (i.e. deleted_at column set back to null), then C should also be restored.

I'm trying to figure out an algorithm to determine when to restore C, if A and B are restored.

I'm leaning toward a reference counting algorithm? So when either A or B is deleted, it deletes C and increments C's deleted_count. If A or B is restored, then decrement C deleted_count and if deleted_count is 0, then restore C?

Does this sound like the best approach for this type of situation? Any obvious drawbacks?

Answer 1

Avoid reference counting or other workarounds. That requires either manually modifying C, or adding triggers. The first option is error-prone, the second harms performance.

Since C has foreign keys for A and B, simply add a "fake" field to C's query: perhaps do this through a view. SQL pseudocode below:

CREATE VIEW c_view AS
SELECT C.*,
  (CASE WHEN A.deleted_at IS NOT NULL
         AND B.deleted_at IS NOT NULL
        THEN MAX(A.deleted_at, B.deleted_at)
        ELSE NULL
   END) AS deleted_at
FROM C
INNER JOIN A ON A.id = C.A_id
INNER JOIN B ON B.id = C.B_id

Just to reiterate, this is pseduo-SQL, not something that is guaranteed to be correct syntax or tested. Hopefully it illustrates the overall idea: do not copy data, look it up directly when querying C. If you have proper indexes on the primary and foreign keys, it should be lightning-fast and have zero risk of having data recorded incorrectly in the database.