Question 90505 in PostgreSQL Question posted in
The official documentation can be found here.

In SQL, what is the best way to store the record of an interaction between users that persists even after one user has been deleted? – Postgresql

I’m making a competitive two player game and after each game I store a record of that game that includes the score, player names and the date. I would like that if one of the users deletes their account the other user can still go to their game history and see that they played against the deleted player.

My solution, which I am not very happy with, is to use the user id as the foreign key and separately save the user’s name as part of the record. I don’t like this because I am now saving the user’s name in two locations and if the validation (24 length names) ever changes I would have to change it in both places.

CREATE TABLE game_records (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    player_1_id INTEGER FOREIGN KEY REFERENCES users(id) ON DELETE SET NULL,
    player_2_id INTEGER FOREIGN KEY REFERENCES users(id) ON DELETE SET NULL,
    player_1_name VARCHAR(24),
    player_2_name VARCHAR(24),
    player_1_score INTEGER,
    player_2_score INTEGER
);

I also thought about using the user’s name as the foreign key but then if a user deletes their account it would either also delete the game record or set it to null or a default value.

Is there any better solution to the one I came up with?

Tags:

3

Answers


  1. 0

    The canonical approach is to use "soft delete" for the profile removal.

    You’d add an "active" flag to your users table, and when a user removes their profile, set it to false. You’d also change the application logic so the login, "challenge to game", "see leaderboard" functions only work for users whose active flag is true.

    That way, you can maintain your foreign keys, but the application semantics are maintained.

    Login or Signup to reply.
  2. 0

    You can use something like the soft deletes from Laravel (PHP Framework), you’ll have a column that saves the datetime when the record is deleted, using like this you need to have an scope on your queries, like

    WHERE TABLE.DELETED_AT IS NULL

    accessing that information where you don’t want to the deleted records to affect the results, you just don’t use the scope.

    Login or Signup to reply.
  3. 0

    It is not nice to show user information after deletion, and might be illegal. What if the user is a kid that was using his real name, and whose parents closed and deleted the account?!

    A simple approach would be to display "deleted user" instead of a name or ID.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search