Solved

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

Posted on 2013-06-14
6
296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

707 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