1

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?

  • Is this SQL, or something else like NoSQL? ORM implies SQL, but it would help to be explicit. – user22815 Feb 15 '17 at 22:50
  • yes, SQL. I've added that to the question, thanks! – Josh Petitt Feb 15 '17 at 22:53
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.

  • 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. – Josh Petitt Feb 15 '17 at 23:22
  • Although I do see what you are saying, I will investigate the possibility of this... Thanks again – Josh Petitt Feb 15 '17 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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