Having unique identifier for user while Updating user entity (by deleting and adding new one)

Question

I am adding authentication to my web app and I consider the following case:

I have USERS table which look like this ^{simplified version}

Id {int} | Username {nvarchar(100)} | CollectedPoints {int} | DateDeleted {datetime(2)}
_______________________________________________________________________________________

1        |          David           |      10               |     10:57 PM 11/30/2019
_______________________________________________________________________________________

2        |           Ann            |      15               |          null
_______________________________________________________________________________________

3        |          David           |      2                |          null      
_______________________________________________________________________________________    

I have other tables pointing to USERS through foreign keys.

Instead of updating USERS, I want to soft delete users and add a new one (I want to save the history of the user).

In this case, I can't use Id column of users table as the foreign key in other tables, because these records stay attached to 'deleted' user.

In my app, the username of the user will never change, so one way is to use username as a foreign key.

Another way is to split users table in two tables, where immutable data (such as Id and username) will be in first table ("Users") and other data (password, email, etc.) in second table ("UserInfo"), now referencing the first table with Id column.

What will be a better approach?

Answer 1

It will be cleaner if you keep the user table to just represent users (with a unique ID per user) for each user. Separate out the history as a separate "date effective" table (PK is user and date effective).

Most joins to the users will not require the full history - if you keep the full history in a single table the join to that will get complicated (and not perform as well). You can join off to the history table when you need the additional information - or even create a view which provides the history and joins both tables for ease of use.

You may want to denormalize certain key information to the user table - for example current points, and whether they are a current active user.

Answer 2

I recommend to use the Id column as a foreign key and I will explain why:

Let's say you want to know how many deleted users you have in your system. If you have two tables, you can go and query the 'deleted users' table for the count however, in this case you lose context to the original users table that you started from the beginning - What if the user deleted his profile and then decided to 'reactivate' the profile?

When you have one table, you can always group by your column and with a simple having clause you can query for the deleted or activated users in your system - this way context to the original table is kept.

---

Two table means double maintenance, you will have to maintain duplicate code for the tables in some way or another - Whether by conditions or by separate methods. It can be the source for huge coding mistakes and misunderstandings, It can open the gate for developers to necessarily make those mistakes.

Maintaining on table is easier as you have just one table to deal with.

For example - if you need to create new index on the activated users table, you will have to create the same index on the deleted users table and so on.

---

On the other hand, if you maintain two tables you will have cleaner data for each user, you will have one table for deleted one table for activated which you can relate through a shared foreign key. It might cause additional JOIN clause when querying for the users, however you will not have to query for all of the users.

For example - if you need data from the activated users only, you will join only the activated users table rather then working on all of the users - deleted and activated - and use condition to filter the records which might effect performance.

Answer 3

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 Users)

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 Users table.

This solves the problems at hand:

• No FK issues as the "real" user's PK never changes.
• The 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.