Instead of updating USERS, I want to soft delete users and add a new one (I want to save the history of the user).
I find it important to point out that this is a (mild) XY problem. You're not trying to soft delete users, you're trying to maintain past values from the user columns.
Soft deletion is not the way to go here. While it initially seems like it would work to "soft-drop create" updated rows, it's not a great solution as you've somewhat already discovered:
- You run into FK issues galore. The more tables rely on a user FK, the bigger this job becomes.
- Your users table grows much larger than your actual user base, which is going to negatively impact performance
- You open yourself up to bugs whenever someone forgets to filter the soft deleted entities.
There are better ways to track historical value changes of an entity. This answer just lists a few:
1. Event sourcing
This requires a big architectural change. Essentially, event sourcing doesn't store your user as is (i.e. with its current values). Instead, it creates a new "data change" entry whenever a change is introduced to the user's values. When you fetch the user object, your event sourcing "calculates" what the current user values are based on the events it stored.
This is a great solution for the problem, but it requires some architecture to set up. It may be overkill for your scenario.
2. Your own history table
Rather than "soft-drop creating" the user, how about you create two tables:
Users (same columns as you have now)
UserHistory (same columns as
Users, and also a FK to
Whenever a user is being updated, you first copy the existing user to the history table (making a new entry, preferably with an FK to the "real" user), and then you can safely update your "real" user in the
This solves the problems at hand:
- No FK issues as the "real" user's PK never changes.
Users table remains performant as it'll only be as big as your user base. The more bulky and performance-lowering historical entries are in a table of their own that don't effect the "normal" workings of the application (i.e. with the current user values).
- As there is no soft delete, there's no way to forget filtering the soft deleted items.
Depending on whether you think you need it or not, you could make a historical entry for each column that changes, or make one big entry for the whole user when it changes. The former is more data efficient but a bit more complex to puzzle the pieces together again. Pick whichever option suits your needs the best.
3. Other solutions
Depending on the reason for wanting to store the old values, different approaches are available.
For example, if the only reason you want to keep this information around is for debugging purposes, it may suffice to write these values to a log file rather than store them in the database. It keeps your database tidier and more performant.