Entity Deletion Strategy

Hi,
I'm wondering what the standard practise is for dealing with the
following very common scenario:
You have users who can use your application and they are identified by
email address. Sometimes you want to delete one of these users. You
want to keep some reference to them in the DB for auditing purposes.
Also, you want to be able to free up that email address so it can be
used again.
It seems to me there are two options:
Keep the user in the User table but set its "status" to "deleted". The
problem thought is that now many of the queries against the user table
will have to check status.
Delete the user from the User table, but have it stored in some other
table, a UserAudit table for example.
Is there a standard way of doing this? If so, how is it done? Thanks.Hi
As long as the email address does not have a unique index or is the primary
key you can use the status flag. Depending on how many deleted users you hav
e
(or if you see a significant degredation of performance) then you may or may
not want to partition the table (or create a partitioned view). To remove th
e
need to add the status check to every where clause you can create an active
users view (and keep table name) of the table. To implement a partioned view
would be the flip side of this (create an new table and transfer the table
name to the partitoned view), once you are using the active users table/view
there would be no T-SQL code change involved with changing over to the other
model.
John
"nickgieschen@.gmail.com" wrote:

> Hi,
> I'm wondering what the standard practise is for dealing with the
> following very common scenario:
> You have users who can use your application and they are identified by
> email address. Sometimes you want to delete one of these users. You
> want to keep some reference to them in the DB for auditing purposes.
> Also, you want to be able to free up that email address so it can be
> used again.
> It seems to me there are two options:
> Keep the user in the User table but set its "status" to "deleted". The
> problem thought is that now many of the queries against the user table
> will have to check status.
> Delete the user from the User table, but have it stored in some other
> table, a UserAudit table for example.
> Is there a standard way of doing this? If so, how is it done? Thanks.
>|||John Bell wrote:
[vbcol=seagreen]
> Hi
> As long as the email address does not have a unique index or is the primar
y
> key you can use the status flag. Depending on how many deleted users you h
ave
> (or if you see a significant degredation of performance) then you may or m
ay
> not want to partition the table (or create a partitioned view). To remove
the
> need to add the status check to every where clause you can create an activ
e
> users view (and keep table name) of the table. To implement a partioned vi
ew
> would be the flip side of this (create an new table and transfer the table
> name to the partitoned view), once you are using the active users table/vi
ew
> there would be no T-SQL code change involved with changing over to the oth
er
> model.
> John
> "nickgieschen@.gmail.com" wrote:
>
You can create a trigger and when you will delete rows deleted rows
will be inserted to history table. So you can create a primary key or
unique key on email address.
Regards
Amish Shah
http://shahamishm.tripod.comsql