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?
CollectedPoints
ofDavid
changed, I haven't updated row in place, instead I deleted an old one (whereCollectedPoints
was 10) and inserted a new one with updated cell value (WhereCollectedPoints
is 2). So, I marked row as deleted and added a new row corresponding to the same user. – ABC Dec 1 '19 at 5:26