Sql-server – How to handle removing ASP.NET users where their UserID is tied to rows that should be maintained even after a user is deleted

application-designdatabase-designsql server

I have a website that uses ASP.NET membership and so there are multiple aspnet_... tables in our database.

We are creating a service for the users that allow them to essentially create laboratory testing orders for an employer. An employer can have multiple users that can order tests.

If someone effectively loses access to the service (they get fired, quit, retire, or get their permissions revoked by their employer) the laboratory testing orders that they created should remain in the system and so should the user's user name. This way when someone views a list of all the laboratory orders, they know what user ordered what testing and for who.

Right now, when a user loses access, we "physically" remove their user data row from the aspnet_users table and the aspnet_membership table. This removes their username, email, profile, password etc… We can't do this if we create a relationship between the aspnet_users table and the table that holds the laboratory orders. If we do, we either need to:

  • cascade the deletion (which will remove the orders that a user created)
  • set the value in the table that holds the laboratory orders to either "NULL" or some sort of default value. If we do that, we lose all of the user's information, such as their username etc… which we want to maintain.

If we opt to change to a "soft-delete" approach and just mark a user as not being allowed to log in (not approved), but keeping their user row in the aspnet_users and aspnet_membership` tables, is there any issue that might arise from this that I can't see? Security issues? Performance issues?

I generally think leaving user data in a system when the user cannot access the system is just… wrong. It leaves a bad taste in my mouth.

What is the best way to handle storing data like this that must be "persisted" even if the "parent" row is removed? Should I not use a relationship and just copy the user data to another table? If I do that there's a chance for inconsistent data and updates would be difficult. I can easily see it growing into a monster and impossible to maintain. There's also the chance of conflicts if a username is reused in the future.

Any advice would be very much welcomed.

Best Answer

What I recommend is separating out the concern of "Who are people we've heard of?" from the concern of "Who can get into the system now and how do they do that?"

What this means in practice is that you should not refer directly to your aspnet_users table to note who did what and when, etc.

Instead, you should have a Person table into which you dump every new user once you add them to the system. Your transactions should point at this Person table instead of your aspnet_users table.

Now, if you think it's important for any reason, you can have the Person table point to the aspnet_users table. You can also do things like keep a status column on the Person table to note current status, etc.

However, when you no longer permit access to a given user, you should take them out of the ASP.NET access control tables (and update Person, if appropriate). Doing this will not interfere with the values in your transaction tables.

Related Question