Solved

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

Posted on 2013-06-14
6
292 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

831 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