Logical Deletion Flag

[TedHolt]

Database design question here. A matter of curiosity.

Many of the database tables (physical files) I have worked with over the years included a one-byte column (field) to indicate whether a record is active or not. (This is commonly referred to as a logical delete.) Sometimes these fields can have more than two values (e.g. active, suspended, deleted).

Do you make a practice of including such field in new tables? If not, how do you decide when to include such a flag or not? Of what data type do you make it? What values do you use for the active and inactive settings?

[GLS400]

Re: Logical Deletion Flag

Hi Ted:

At my shop
If the field will be used for future analysis (what's the cancel rate for rep xxx)...I add the flag.
If the status can change (re-activate)...I add the flag.
If you need it to just disappear ..... delete the record(s).

When the flag is included blank indicates active other codes are validated.
There is usually a date/user associated with the other codes.

Best of Luck
GLS

[DeadManWalks]

Re: Logical Deletion Flag

Hey,

We don't use them here and I have always fought against them at other places. I think they just could up the water. If tracking is needed then a Journal / Trigger is what I'd go with.

[Scott Klement]

Re: Logical Deletion Flag

I've used "status" fields for various circumstances.

Historically we used them for "logical delete", but in my opinion, that should not be done anymore today. That made sense 30 years ago, but not now!

But, there are things that still require status. For example, tracking what phase an order is in... has it been completely entered? Has it been sent to the warehouse workers to begin picking stock from inventory? Has it been loaded on a truck? Invoiced? a status code works nicely here to indicate the current order status.

Likewise, a product might be kept in an item master file long after the company needs it so it can be used on reports, et al. So you might have a status field that indicates that a product is "discontinued". You still have it in your master file, but do not allow customers to order it, manufacturers to make it, or warehousing people to stock it.

But to purely indicate whether a record is "deleted", I think it's a bad choice and is error-prone. Everyone querying the database has to make sure they check for it... including end-users using query tools such as DB2 Web Query. This sort of thing is not good database design, IMHO. If you want to delete a record, just delete it... then you don't have to worry about it being picked up by applications, (or worse, causing duplicate key errors!)

[B.Hauser]

Re: Logical Deletion Flag

Rather than using a flag for indication a logical delete, we write a history file (in some circumstances) when deleting a record. The history file is written based on a before delete trigger and does not only contain the deletion date but also the user and the program/procedure that deleted the record.

Some time ago we had problems with a customer who maintained, orders and other very important information would disappear and so the application does not work correctly.
That's why we added those history files and could prove data was deleted over our application but also deleted with SQL!

We also added methods for reactivating deleted records by copying them from the history tables. We also integrated reorganization programs.

Birgitta

[tomliotta]

Re: Logical Deletion Flag

The obvious first question to me is whether it's simply a 'D'elete flag or it's a 'S'tatus flag that indicates numerous states. With numerous states, there are likely multiple business processes that need attention before any decision can be made. The two kinds of fields would be handled differently.

For a straight 'D'elete flag, the next question is what difference is made to the application if the record is actually deleted. The column might become just a virtual column generated in a VIEW, where it always has a value indicating the the record is not deleted. What application process is affected when the 'D'elete flag value is set? If no record ever presents that value, there might never be a problem.

It's been a long time since I've seen a good use for a 'logical delete'. If an application is being modernized (or simply modified) and it's a good time to make the change, then I'd eliminate it. To me, it's just 'baggage' that can be left behind. I'm a big fan of letting baggage go that isn't needed.

But for a useful 'S'tatus field, it's potentially much more complicated.

Once a 'S'tatus field exists with multiple states, the number of states seems to grow. I've run across a couple cases where new (and very poorly documented) states were added without everyone even knowing they existed. If at all possible, I'd eliminate those flags as well.

For example, an ORDERS file might have a 'S'tatus column that indicates it's been shipped. But a related record could also be added to a SHIPPED file that has OrderNbr and ShipDate as its columns. The existence of the new record can serve the same purpose as a value indicating the same status. For a series of 'S'tatus values, a matching series of files could have records added and deleted as the status changes. It might not be too big of a difference between (1) deleting a record from a PICK file and adding one to the SHIPPED file and (2) updating the same kinds of fields in the ORDERS file record. It can depend on the resources available to get it done. If nothing else, there'd be a slight decrease in the number of record locks in the ORDERS file since it wouldn't have near as many updates applied to it.

Biggest obstacle has always been getting approval for the work. I've spent many "single hour" tasks working around the same irritating issues, but couldn't get approval for a 10-hour task to clean up the problem once and for all. And it's always known that the single-hour tasks are going to happen again and again off into the future and that a cleaner application lets all future tasks be easier.

[kitvb1]

Re: Logical Deletion Flag

I would say this depends on the file and its usage.
The problem with doing a proper delete for files that have more static data like Customer, Supplier, Parts and Orders - how will you reprint an order or an invoice from 8 or 9 years ago if needed, and the record is deleted? This does arise a number of times/year. Here, there is a 10 year retention period for financial data.

On the other hand, more dynamic data such as kit lists or pricing files, may be comfortably deleted when the audit has been written.

[tomliotta]

Re: Logical Deletion Flag

...how will you reprint an order or an invoice from 8 or 9 years ago if needed, and the record is deleted?

That would seem to be mostly a question of when to delete and how to determine that deletion is appropriate. For example, a 'D'elete flag might be set when an INVOICE is paid after delivery. (There's almost certainly something that triggers the setting of a flag.)

But a purge that clears the records months or years later needs to know how much time has passed in order to know which records should be deleted. The criteria used for the purge shouldn't need to include a 'D'elete flag, should it?