Why should not we use soft deletes in the models/tables used for authenticating into the application

The concept of soft-delete is very tempting. You might be aware about soft-delete concept, if you are into application development with a database. In this concept, you don’t need to physically delete the data from the database. Instead you set a flag on the record which needs to be deleted and when you query for the results filter out records with that flag. The advantage of soft-delete concept is, as you never physically delete the data, there is no risk of loss of data when something goes wrong (with the delete action, not with your code). It’s easy to get back the record by just changing the flag. Around 8 years ago, when I came to know about this concept, I was thrilled. This feature tempts me so much that I use this feature almost everywhere, wherever I need to delete a record from any table/model.

I am using Laravel since more than a year. It’s Eloquent ORM is really eloquent. And it makes soft-deleting very easy. So, I implement this feature to almost every model, where I need to delete the model. This includes User model. User model also used to login into the application. Recently one of my client complained that they are unable to add users back into the application, whom they have deleted earlier. While adding them back, they get an error that the email ID is already exist. When I heard about it, I thought “Ok, the email ID is already exist in the table, but it’s (soft) deleted. Why does the validation system show that error?”. Initially, I started to blame the validation service of Laravel. Why does it take a soft-deleted row to consideration while validating whether the email ID is unique or not. Then I realised that only Eloquent knows that the row is soft-deleted, but not the validation service. So, I modified the code so that it will not consider soft-deleted rows while validating. Everything seems fine at that point.

When I tested it again, I received a real exception. As the ‘email’ column is a unique column, it can’t add another row with the same email ID. Again, poor databse does not know soft-delete. So, can’t blame it as well. For few seconds, I thought I will remove unique from the column. But realised that it’s a bad idea. Instead, I should not use soft delete on the model which I use to login to the application. I also realised that it’s not much necessary to keep a copy of those data from the users table, which needs to be deleted.

But if you are keen to keep copy of deleted users data, then I would recommend a couple of database design ideas.

• Before I was not in the favour of creating multiple tables for a single row of data. I feel that it creates a lot of unnecessary one to one one relationships. But now I think, it’s better to create at least two tables for users and have an one to one relationship. On the first table, you store everything related to authentication (primary key, username/email, password (hassed), remember token, timestamps). On the second table, store everything else possible with the one to one relationship. And you can add soft-delete feature to this table. I would also recommend to store email in this table as a copy. And in more complex applications, you can store username on this table too, depending on how you use username on the application. So, when you delete the user hard-delete (delete physically) the record on the first table. You don’t need to delete anything from the second table. With the absence of its parent in the first table, the record on this table will be considered soft-deleted.
• Even though it’s not necessary to keep a copy of the authentication data like hashed password, remember token etc., but if you are interested to keep it for the future, then you can have another table for the copy. Before you need to delete the record from the primary table, copy the record to the backup table and then delete.