I want to let the user "delete" a record, so it no longer shows up in

searches through the web, but it stays in the database.  I know I could have

the delete action change the value in a "status" field, then test this value

in the search page.  But I'm looking for alternatives that would reduce

server load.  Any ideas?



Happy Trails

I'm not sure of your intentions.  If the user "deletes" a record,

is it to be "deleted" in the db? If so, the "deletion" could set

a nullable field to null (or not null). Then you subsequent queries

could look like this:



SELECT field1, field2 FROM table1 WHERE field3 IS NOT NULL



OR (depending on the logic used for the phantom deletion)



SELECT field1, field2 FROM table1 WHERE field3 IS NULL



Gone are the XBASE days when one could have use the

SET DELETE ON command to hide deleted records



Dallas Martin











----- Original Message -----

From: "Michael Goldman" <mg188@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, October 14, 2000 12:26 PM

Subject: [asp_databases] hiding a record





> I want to let the user "delete" a record, so it no longer shows up in

> searches through the web, but it stays in the database.  I know I could

have

> the delete action change the value in a "status" field, then test this

value

> in the search page.  But I'm looking for alternatives that would reduce

> server load.  Any ideas?

>

> Happy Trails

>

>

> ---

> FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND

> INSIGHTS IN YOUR INBOX!

> Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips,

tools, and

> developments from the experts.  Sign up for one or more of EarthWeb?s

> FREE IT newsletters at http://www.earthweb.com today!

> ---

I want the record to remain in the db.  That record should not show up in

subsequent user searches.

I know I could add a status field and test the status before showing the

record in user searches, but I'm looking for a slicker way to do this -

maybe set some property in the db?



In case your curious, here's why I want to preserve the record even tho the

users can no longer see it:



A store clerk (or any other authorized user) may change or delete an item

record in the store.  A field records that user's id.



So if the record was "deleted" either maliciously or by mistake, I can

restore the record and either discipline or retrain the clerk.





----- Original Message -----

From: "Dallas Martin" <dmartin@z...>

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, October 14, 2000 10:15 AM

Subject: [asp_databases] Re: hiding a record





> I'm not sure of your intentions.  If the user "deletes" a record,

> is it to be "deleted" in the db? If so, the "deletion" could set

> a nullable field to null (or not null). Then you subsequent queries

> could look like this:

>

> SELECT field1, field2 FROM table1 WHERE field3 IS NOT NULL

>

> OR (depending on the logic used for the phantom deletion)

>

> SELECT field1, field2 FROM table1 WHERE field3 IS NULL

>

> Gone are the XBASE days when one could have use the

> SET DELETE ON command to hide deleted records

>

> Dallas Martin

>

>

>

>

>

> ----- Original Message -----

> From: "Michael Goldman" <mg188@h...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Saturday, October 14, 2000 12:26 PM

> Subject: [asp_databases] hiding a record

>

>

> > I want to let the user "delete" a record, so it no longer shows up in

> > searches through the web, but it stays in the database.  I know I could

> have

> > the delete action change the value in a "status" field, then test this

> value

> > in the search page.  But I'm looking for alternatives that would reduce

> > server load.  Any ideas?

> >

> > Happy Trails

> >

> 

Seriously, I'd just index the status field. I don't think the server load

will be all that heavy, compared to other methods. The need to describe is a

fairly common one, and I've always used a status field...



Cheers

Ken



----- Original Message -----

From: "Michael Goldman" <mg188@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, October 16, 2000 5:16 AM

Subject: [asp_databases] Re: hiding a record





> I want the record to remain in the db.  That record should not show up in

> subsequent user searches.

> I know I could add a status field and test the status before showing the

> record in user searches, but I'm looking for a slicker way to do this -

> maybe set some property in the db?

>

> In case your curious, here's why I want to preserve the record even tho

the

> users can no longer see it:

>

> A store clerk (or any other authorized user) may change or delete an item

> record in the store.  A field records that user's id.

>

> So if the record was "deleted" either maliciously or by mistake, I can

> restore the record and either discipline or retrain the clerk.

I would use a status field as well, but here is another suggestion as to

what you could do.



Create another table in the DB called "Deletes" When the user is deleted to

the public eye, send that record to the deletes table. This way the user

will be preserved. Then if there is a need to re-instate the user, you can

create a page that sends the user from the "deletes" table back to the table

of live users.



Does that help?



Jason Greenfeld

Rubicon Technologies, Inc.

http://www.rubicontechnologies.com







-----Original Message-----

From: Michael Goldman [mailto:mg188@h...]

Sent: Sunday, October 15, 2000 3:17 PM

To: ASP Databases

Subject: [asp_databases] Re: hiding a record





I want the record to remain in the db.  That record should not show up in

subsequent user searches.

I know I could add a status field and test the status before showing the

record in user searches, but I'm looking for a slicker way to do this -

maybe set some property in the db?



In case your curious, here's why I want to preserve the record even tho the

users can no longer see it:



A store clerk (or any other authorized user) may change or delete an item

record in the store.  A field records that user's id.



So if the record was "deleted" either maliciously or by mistake, I can

restore the record and either discipline or retrain the clerk.





----- Original Message -----

From: "Dallas Martin" <dmartin@z...>

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, October 14, 2000 10:15 AM

Subject: [asp_databases] Re: hiding a record





> I'm not sure of your intentions.  If the user "deletes" a record,

> is it to be "deleted" in the db? If so, the "deletion" could set

> a nullable field to null (or not null). Then you subsequent queries

> could look like this:

>

> SELECT field1, field2 FROM table1 WHERE field3 IS NOT NULL

>

> OR (depending on the logic used for the phantom deletion)

>

> SELECT field1, field2 FROM table1 WHERE field3 IS NULL

>

> Gone are the XBASE days when one could have use the

> SET DELETE ON command to hide deleted records

>

> Dallas Martin

>

>

>

>

>

> ----- Original Message -----

> From: "Michael Goldman" <mg188@h...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Saturday, October 14, 2000 12:26 PM

> Subject: [asp_databases] hiding a record

>

>

> > I want to let the user "delete" a record, so it no longer shows up in

> > searches through the web, but it stays in the database.  I know I could

> have

> > the delete action change the value in a "status" field, then test this

> value

> > in the search page.  But I'm looking for alternatives that would reduce

> > server load.  Any ideas?

> >

> > Happy Trails

> >

>

Couldn't this get real messy if there are a lot of records related to the

one that is being "deleted"?



Cheers

Ken



----- Original Message -----

From: "Jason A. Greenfeld" <jgreenfeld@r...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, October 17, 2000 1:51 AM

Subject: [asp_databases] Re: hiding a record





> I would use a status field as well, but here is another suggestion as to

> what you could do.

>

> Create another table in the DB called "Deletes" When the user is deleted

to

> the public eye, send that record to the deletes table. This way the user

> will be preserved. Then if there is a need to re-instate the user, you can

> create a page that sends the user from the "deletes" table back to the

table

> of live users.

>

> Does that help?

>

> Jason Greenfeld

> Rubicon Technologies, Inc.

> http://www.rubicontechnologies.com

>

Thanks for replies guys.  Seems to me the tradeoffs in Ken's and Jason's

approach is:



Ken's scheme has negligable increase in db size, with small increase in load

on server;

Jason's scheme increases db size (an added table), with no increase in

server load.



I'm still in the prototyping stage using PWS with a tiny test db at home.

So when I go live, I'll test each approach from time to time.  Lately I'm

thinking the occurrence of needing to retrieve the "deleted" record will be

so seldom it might most sense to use backups for retrieval.  So another

scheme could be:



1.    set the status field to delete in the db.

2.    set another field value to a date (today + x number of days) where x >

days between backups

3.    set up a script to delete those records after that date passes.



Only have vague ideas of how to implement the above, but if I have trouble,

I sure know where to get good help.



happy trails



----- Original Message -----

From: "Ken Schaefer" <ken@a...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, October 16, 2000 5:47 PM

Subject: [asp_databases] Re: hiding a record





> Couldn't this get real messy if there are a lot of records related to the

> one that is being "deleted"?

>

> Cheers

> Ken

>

> ----- Original Message -----

> From: "Jason A. Greenfeld" <jgreenfeld@r...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Tuesday, October 17, 2000 1:51 AM

> Subject: [asp_databases] Re: hiding a record

>

>

> > I would use a status field as well, but here is another suggestion as to

> > what you could do.

> >

> > Create another table in the DB called "Deletes" When the user is deleted

> to

> > the public eye, send that record to the deletes table. This way the user

> > will be preserved. Then if there is a need to re-instate the user, you

can

> > create a page that sends the user from the "deletes" table back to the

> table

> > of live users.

> >

> > Does that help?

> >

> > Jason Greenfeld

> > Rubicon Technologies, Inc.

> > http://www.rubicontechnologies.com

> >

>

>

>

> Jason's scheme increases db size (an added table), with no increase in

> server load.



As I said, if you have related tables, you'll need to move all the related

records when you move the primary record. These will all have to be moved to

new tables.



Secondly, when you undelete the record, you'll need to move all these

related records back again. This will cause a problem because the primary

record will have a new Primary Key, and this will have to be cascaded down

the relationships to all the related records.



Cheers

Ken