Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Handling deleted records

Author  Topic 

nlocklin
Yak Posting Veteran

69 Posts

Posted - 2002-03-27 : 03:20:19
This might seem like a weird question, but here goes:

The place where I'm working is in the process of migrating from System 1032 to SQL Server. In System 1032, records are never "deleted", they just have a flag applied to them that indicates the record is deleted and generally the record doesn't show up (although they can go in and see the deleted records the table).

It has been suggested that we do something similar on SQL Server - having a flag that indicates not to use the record, although the record still exists in the table. My gut reaction to that was no, because I can just picture one of the developers or statisticians that access the database (through custom-written programs, or the tables themselves through a Microsoft Access interface) forgetting the flag in a query, and some really inaccurate report being produced.

My preference is just to delete the row. If it needs to be brought back for whatever reason (deleted by mistake, or perhaps someone just wants to see what the values of the row were), it's simple enough to do that, right?. I'm waiting to hear back about how often they expect to be looking at deleted rows, and for what reasons.

Has anyone else had the use of a "deleted" flag suggested to them? Does anyone use anything similiar to this? I'm interested in everyone's take on this.


--
"I'm always doing that. I'm always messing up some mundane detail."

Nazim
A custom title

1408 Posts

Posted - 2002-03-27 : 03:29:21
Never worked on anything like that . but got a suggestion. you can very well follow the suggestion and have the a flag which indicates deleted record , create views which display only the one's which are not marked as deleted and give the users access only to the views.

HTH

--------------------------------------------------------------


Edited by - Nazim on 03/27/2002 03:32:59
Go to Top of Page

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-03-27 : 05:58:09
You could perhaps have alternative tables in your database that only hold deleted records. This would save having 'deleted' records in the live tables. You are unlikely to bring back that many i would suspect so why have them cluttering up your live tables.

A US colleague of mine adopts this strategy and it has worked very well for her so far.

my pennies worth

Go to Top of Page

findajay
Starting Member

1 Post

Posted - 2002-03-27 : 06:58:03
quote:

You could perhaps have alternative tables in your database that only hold deleted records. This would save having 'deleted' records in the live tables. You are unlikely to bring back that many i would suspect so why have them cluttering up your live tables.

A US colleague of mine adopts this strategy and it has worked very well for her so far.

my pennies worth



Go to Top of Page

Jay99

468 Posts

Posted - 2002-03-27 : 08:41:00
quote:

...If it needs to be brought back for whatever reason (deleted by mistake, or perhaps someone just wants to see what the values of the row were), it's simple enough to do that, right?....


Not really. Once the transction commits, the record will be gone. There is no undo button .

In order to bring it back, you will have to restore a version of the database from a backup to a point in time before the transaction completed. Then INSERT into production SELECT the row from the restored database that was deleted. This can prove quite tedious, especially as databases grow and restore times begin to take many minutes and require much disk space . . .


Jay
<O>
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 09:14:45
What is system 1032? Never heard of it.

The concept of a deleted flag is quite old, dating back to the days where DBMS's were not sophisticated enough to separate the logical act of deleting rows ( further queries do not return them ) from the physical act ( removing bytes from disk, which is very expensive ). Developers with any DBase exposure, not that they would admit to it now , remember the experience.

It's curious that your developers would have aggregated the deleted flag into a "feature" that lets them see deleted rows, because the DBMS still removes those rows at some point in time - that point being convenient for a developer rather than the system. In DBase you would issue a PACK, for example.

I don't like deleted flags personally because it muddies up the concept of deletion. If you don't want a row anymore, delete it. Flagging it as "inactive" or "not applicable" is a half-way measure that causes confusion and leaves developers wondering what it really means to delete something.

The earlier suggestion of a view that returns rows with the deleted flag clear is a good one, provided you index it. If you have to go with the flag concept I would recommend that approach.

setBasedIsTheTruepath
<O>
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-27 : 11:45:27
quote:
Developers with any DBase exposure, not that they would admit to it now , remember the experience.


I AM AN EX-DBASE AND CLIPPER PROGRAMMER, AND DAMN PROUD OF IT!

I LOVED Clipper. For those that don't know, Clipper was a programming language that let you build stand-alone database applications. It had a tremendous feature set, especially for the time (1986-1990) Don't believe me? How about:

-Index on an expression, not just a column (just released in SQL Server 2000)
-Build indexes in DESC order (just released in SQL Server 2000)
-Strings up to 64K in size, and more functions to manipulate them than you could ever use
-Array support, and every table-to-array-to-table function you could think of
-Write libraries in C and link them in to your Clipper code
-You could actually undelete rows as long as the table handn't been PACKed

There was a lot more as well, but I have forgotten a great deal of it. This was available looooooooooooong before Windows was even a glimmer (multi-tasking wasn't even available on PCs then), and it could run in 256 KB of memory! No, not megabytes, kilobytes!

Now, by no means was this stuff a RELATIONAL database product. It didn't even use SQL for data access, and there was no system-level data integrity like foreign keys or constraints, nor could you do JOINs properly. You would have to program that in, effectively using triggers for every single constraint or relation you wanted to enforce. In many ways this is akin to some versions of MySQL. There were no real set-based operations either, most of the time you used loops/cursors to perform UPDATEs and so on, but later versions of Clipper provided functions that let you simplify this; you called a DB_EVAL() function that would do the UPDATE, and you could provide it a WHERE clause and SET expression as parameters.

So, in case anyone needs a database app designed to run on a DOS system, gimmee a call!

Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 11:48:45
Now that the cat's out of the bag, I was too!. The heady days of Clipper w/ RASQL/B for Btrieve ...

setBasedIsTheTruepath
<O>
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-03-27 : 11:56:30
I've run into this problem as well.
We orignally thought, lets jsut delete the record. That caused problems because it broke referential integrity. The Child records got orphaned. Then we thought about triggers to go kill the child records too, but that seemed complicated and very dangerous.

We opted for the "flag" method of deleting data. We rarely delete data out fo our database for legal reasons, so flagging works for us. The biggest downside is that the database grows and grows.....

Michael

Go to Top of Page

nlocklin
Yak Posting Veteran

69 Posts

Posted - 2002-03-27 : 14:12:34
Thanks for the thoughts everyone. I should have mentioned I am also considering the idea of moving the deleted records to another table. We're already planning on something similar - we have a process where paper forms are scanned in and exported to the database automatically, and I'll be setting up tables to store duplicate records so that the person responsible for monitoring those records will get an alert and can examine whether the record is a true duplicate or if something is wrong. One of the downsides to that is if I decide to go with deleted tables, I'm tripling the number of tables in my database.

My final decision will probably be based on what I hear back from users about how often they expect to request that deleted records be recovered, and for what reasons. If it doesn't appear that there will be many requests, I'd like to just delete the records and worry about restoring later. If it looks like I'll be getting a ton of requests every day, the flag seems like an easier solution administration-wise.

The idea of creating views for the tables that enforce not acting on rows with a deleted flag and giving users access only to the views I hadn't thought of. That would be a great solution if that's the way we end up going. Thanks for the idea!


--
"I'm always doing that. I'm always messing up some mundane detail."
Go to Top of Page

nlocklin
Yak Posting Veteran

69 Posts

Posted - 2002-03-27 : 14:21:03
quote:

What is system 1032? Never heard of it. The concept of a deleted flag is quite old, dating back to the days where DBMS's were not sophisticated enough to separate the logical act of deleting rows ( further queries do not return them ) from the physical act ( removing bytes from disk, which is very expensive ). Developers with any DBase exposure, not that they would admit to it now , remember the experience.

It's curious that your developers would have aggregated the deleted flag into a "feature" that lets them see deleted rows, because the DBMS still removes those rows at some point in time - that point being convenient for a developer rather than the system. In DBase you would issue a PACK, for example.


System 1032 is an old DBMS that we're running on our VAX . For obvious reasons, they decided to migrate to a SQL Server environment. The way DBase handled deleted records sounds very similar. Yes, System 1032 does actually remove the rows that are marked as deleted in a number of situations. The person who has been maintaining the datasets actually wrote a program a number of years ago that maintains the deleted records in the dataset so that they can maintain the unique identifier ($ID) that System 1032 assigns to the records.


--
"I'm always doing that. I'm always messing up some mundane detail."
Go to Top of Page

Jay99

468 Posts

Posted - 2002-03-27 : 14:21:30
quote:

...If it doesn't appear that there will be many requests, I'd like to just delete the records and worry about restoring later...





Jay
<O>
Go to Top of Page

nlocklin
Yak Posting Veteran

69 Posts

Posted - 2002-03-27 : 15:02:11
Uhm, not sure what that image is all about, but...

Perhaps I should rephrase that quote... If there won't be many requests for deleted records to be recovered, it wouldn't be worth the time re-writing existing software to use a flag - I'll just recover the deleted record through a backup. I'd be the one doing the recovery, I'm not a consultant .


--
"I'm always doing that. I'm always messing up some mundane detail."
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 15:10:35
quote:

...If it doesn't appear that there will be many requests, I'd like to just delete the records and worry about restoring later...



I believe what my colleague Jay is trying to communicate with the image is that this is a bad idea.

Stay with the indexed view concept. What you should do, it sounds like, is rename the table(s), and create views with the table(s) original names. That way you win all round:

1) you don't have to change any code
2) deleted rows are removed from consideration
3) deleted rows are "moved" to another table

setBasedIsTheTruepath
<O>
Go to Top of Page

Jay99

468 Posts

Posted - 2002-03-27 : 15:26:31
quote:

quote:

...If it doesn't appear that there will be many requests, I'd like to just delete the records and worry about restoring later...






I read this as, "When someone needs me to bring a record back, I figure out how to do a restore at that time", but now it kinda sounds like you already have a backup/restore plan, scheme, process etc. So, I guess what you meant to say was
quote:

If it doesn't appear that there will be many requests, I'd like to just delete the records and if someone needs a deleted record back, I will do a restore from my backup set with no worries



See if you had meant the first thing, my image would have been quite funny because that is the contractor mentality. And me being a contractor, I get a kick out of it. I do exactly what I am told, while many times knowing that I am building things that will expose other problems which will require me to fix the problems, plus change the thing I originally built because of the fixes to the problems it exposed. As long as I keep creating problems while appearing as though I am fixing things, I keep getting paid by the hour . . . its kinda sick and dark and funny.

The image was more of a digital high-five than some sorta cut-down.

alas, I fear as though I am ramblin' and no one is hearin'

Jay
<O>

EDIT: also, if you have seen those 'Inspirational' posters hanging around business places, this image is made to look like one . . . more funny stuff, see?

EDITEDIT: crap, I need a spellchecker on this thing

Edited by - Jay99 on 03/27/2002 15:28:00

Edited by - Jay99 on 03/27/2002 15:28:44
Go to Top of Page
   

- Advertisement -