2

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?

2
  • Is this SQL, or something else like NoSQL? ORM implies SQL, but it would help to be explicit.
    – user22815
    Feb 15, 2017 at 22:50
  • yes, SQL. I've added that to the question, thanks! Feb 15, 2017 at 22:53

1 Answer 1

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.

2
  • Thanks for your input, I am working with an off-the-shelf ORM that has the soft delete behavior built-in. The ORM will try to set the deleted_at field in certain cases, so it may be hard to replace the table with a view. I've updated my question to reflect this. Feb 15, 2017 at 23:22
  • Although I do see what you are saying, I will investigate the possibility of this... Thanks again Feb 15, 2017 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.