1

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.

3

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.

  • If I do that, then how would I update the Person table if there is a change that needs to be made? For example, let's use the easy example of someone getting married and their last name changes. If the user is active, but I have no link between the Person and Users table, how would I know which Person's last name to update? If I create the link, then I'm in the same boat I am now unless I set the UserId value to null or some default value. – Zachary Weber Apr 12 at 18:53
  • 1
    @ZacharyWeber I'm not sure that the example is necessarily a good one. If the user isn't a user in the system anymore, why would you care about a name change? You're just keeping the person around for historical context aren't you? Nevertheless, if you think you need to update a person record and your key to finding them will be their user ID, even though they don't have one anymore, you could link the Person table to the user account table via the user ID. It's a candidate key in either table so you can use it for a foreign key as well as any other unique column. – Joel Brown Apr 12 at 19:15
  • No what I mean is, assuming the user is still in the system. If I add a new user, I would also add a record to the Person table. How would I know which record to update if the person's name changed and they are still an active user in the system? – Zachary Weber Apr 12 at 20:09
  • @ZacharyWeber that depends on how you link Person and the ASP.NET user tables. You have options like using the value from the aspnet_users.UserId column as a foreign key in Person. This is independent of the name of the person and of the login used by the user, so you don't have to worry about one or the other changing. – Joel Brown Apr 13 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.