Soft-delete user table? Or is there another alternative?

My head's spinning from reading all the pro's & con's articles about soft-delete. But that's the only way I know to achieve this:

maintain the foreign keys & user info (history data) Even if the user is deleted/inactive, there are foreign keys in comments, attachments and stories tables. So that it can still be identified that it was him who wrote this comment e开发者_JAVA百科tc.

Other info:

deactivated users cannot login, won't be included in lists.

But if I use soft delete, it's not really good adding an extra column in the WHERE in sql statement every time I query for that table.

What to do? Hope you guys can give some inputs.

Note: I use mysql and ROR

If you "soft delete" users, you don't add an extra column in the WHERE clause of every SQL statement. Instead, build one view that returns only active users, and the queries that need to know about active users use that query. They don't use the base table.

It might look something like this.

create view users_active as
select * from users
where deleted = false;

Depending on how much development you've done so far, you might want to rename the users table, and name the new view "users" instead. Something along these lines . . .

alter table users rename to users_all;

create view users as
select user_id, user_name from users_all   -- All columns except "deleted"
where deleted = false;

Now any query that used to hit the table "users" will instead hit the view "users". Logical data independence--this is why views and tables share the same namespace in the relational model.

There are so many ways to achieve things in this world, don't think what someone said is always true :)

First off, when it comes to comments - yes, if you are joining users table to obtain username for the person who sent the comment, using soft delete - yes, it's much, much easier to obtain who wrote the comment. It's also easy to check whether they're deleted so you can render it in HTML too. On the other hand, you can also store username in the comments table so you can avoid joining the users table. However, it's difficult to know whether the user was deleted or not since you're not receiving that information from users table.

Adding additional clause in WHERE won't kill anyone. SQL was made so you get the data you want, computers conform to users' needs, not the other way around. So, if you think soft-delete is what you need, use it. The only "real" con is that you keep the record in the db, so someone might say that soft deletes inflate databases.