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.