Solved

Are "deleted" records in a SQL database customarily reported with active data?

Posted on 2013-06-14
6
293 Views
Last Modified: 2013-06-15
I run a third-party CRM that has been giving us "unexpected" results in our searches.  I've tracked this down to the vendor including RECYCLE data in those search results.  Specifically, if a record is deleted it is put into Recycle bin.  If record remains in Recycle bin, it then is included in query results.  The "recycle records" aren't marked as pending deletion which wouldn't make it right but would make it "explainable" to my users who think delete means GONE.

Vendor argues this is normal. I don't think so... do you?  I welcome both developer-level and regular-end-user perspective.  Brisk, direct and simple replies especially prized.

I cannot turn off Recycle feature nor is there an "auto-empty Recycle bin" option, nor is there a "Delete=Recycle/Keep Active" or "Delete=Delete I Really Mean It" option for users.

I would like some community feedback on whether or not I am correct that the occasional opportunity to restore a record is not worth having inaccurate search results and this feature should be eliminated or better controlled.

Thanks!  I'd like to split points like this:

First response = 200
Second and Third  response = 150 each

Everybody else who chimes in:  thanks and many good graces.
0
Comment
Question by:ColdKathleen
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39249584
What crm package?
0
 

Author Comment

by:ColdKathleen
ID: 39249680
CRM detail not relevant to "concept" question, no need to bash the vendor by name... Looking to establish the baseline of deleted data being no longer available to queries.  It seems obvious to me as long-time db user/admin BUT since I am not a developer I needed a true tech's perspective.  Points still available.
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 250 total points
ID: 39249874
This isn't a SQL Server or Database Issue, if i delete records at database level they are GONE..!!!
The Recycle bin is a capability your CRM. The Vendor's argument "this is normal" is correct. They Programmed the CRM so you can recover your records if someone make a mistake and delete them.
I run a third-party CRM that has been giving us "unexpected" results in our searches.
If you mean, by running SELECT SQL-Statements then look in your table again, i'm 99% sure you will find a Column called "deleted" or something like to identify "Deleted Records". In this case you need to include it in your queries.

obviously makes sense to add a option "empty recycle bin" ...   ;)

Regards
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 250 total points
ID: 39250016
As this is heavily related to the CRM package, knowing the type of CRM package it is may indeed help. If you delete a record from a database it is gone.

In Sage CRM for example, records are not deleted rather they are marked as deleted so they are not visible to the program despite there not being a mechanism in the package to restore deleted records. Onyx CRM (if it is still arond) employs a similar mechanism. Each record has a record status.

I would seriously question the usefulness of a CRM package if it included "deleted" records in its search results. If the search is designed to run on records, it could easily be modified to exclude deleted records. If the records are in a recycle bin rather than marked as deleted, then the system would have actively been programmed to include deleted records which seems stupid to me. Even more stupid would be to not program in an override to that silly set up in the first place.
0
 

Author Closing Comment

by:ColdKathleen
ID: 39250600
Thanks for the input.  When a vendor defends a nonsensical function at the expense of the "end user experience" (end user thinks deleted data is gone) then I like to step back and check my own presumptions.  

There IS an "IsDeleted" field but that's a bit esoteric for every-day users on every search every time.  In fact, I figured the vendor was using that for processing searches if they knew they were folding in Recycle records.  

I've firmed up my understanding of "common practice" which is what I love about this particular forum.

ColdKathleen
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39250610
Glad to have been of some help :)
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question