user off website, remain in db

[designingamy]

Hello all!

I have been told for many reasons that when a user wants to end their account on a website, that you shouldn't delete the user out of the database for awhile. So if I need their information to be off the Internet, but still remain in the database, does anyone know how to go about doing this? It seems like it should be simple, but I can't seem to figure it out.

Thanks bunches,
~Amy

[dickersonka]

Add something like an active flag to the user. And in all queries when pulling users add where active_flag = 1 for active users, and the '0' user will not be pulled back. So this allows you to maintain the user still in the db, but not returned from queries to display in the website.

[designingamy]

Hmmm...okay...I'm working with php. Can you or someone give me an example on how that would work?

Thanks bunches!
~Amy

[dickersonka]

not necessarily any different from the php side except the queries, more from the mysql side

$query  = "SELECT username, password FROM users where active=1";

mysql table
users
username
password
active

[designingamy]

Well, that seems easy enough :) So, if I need to make the user inactive on the site, I just go into the database and change the active=1 to active=0? Or to make it even easier, I could probably go to the place where form where the person wants to delete their account and update the active part of it using php, I'm guessing?

~Amy

[dickersonka]

Yes, same sort of update query as normal if you want to delete. This way also allows you to keep your database in tact and not worry about fk constraints on deletes, or a good way to recover data if it was 'deleted' (just set inactive).

Just remember if you are doing any joins on the user table or based upon a used_id to always add the active column to the query as above.

[designingamy]

What exactly do you mean by joins? Do you mean any table that is connected to the user table should have an active column?

[dickersonka]

joins meaning left outer, inner joins

i mean to pull back the active column from the user table

for example lets says a user places orders

table orders
order_id
user_id
order_number

then in the query you can do something like this

select o.order_id, o.order_number, o.user_id from orders o
inner join 
users u
on o.user_id = u.user_id
where u.active = 1

like for example if you let a user to see orders without logging in, you would want to join on the user table to make sure that they are still active before showing the results

[designingamy]

Well, actually, they aren't placing orders, but instead having their homes posted online. So regardless if they try to log into their account to change anything on their home, if they are inactive, anything they change won't be added.

I guess the only problem I could run into is if they wanted to put another home online later down the road and can't use the same username/password. But I could always make it active for them again.

Also, out of curiousity, how long should user information be kept in the database without deleting it?

~Amy

[dickersonka]

Lol ok homes, I was just giving an example.

You could possibly tell them their account is inactive, and go through some sort of steps to reactivate and set their active flag back to '1' so their data won't be lost.

There is really no right answer on how long to keep the information. A lot of times I would never delete unless it became an issue of database size or critical information.

Also, assuming you have some sort of houses table with a user_id owning the house. If you physically delete the user row, then you will have to delete house row as well as long as constraints are in place. Therefore I would say its not normally that important to delete the data, but it can be under the right circumstances.

[designingamy]

Awesome. Thanks for your help!
~Amy