?
Solved

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

Posted on 2013-06-14
6
Medium Priority
?
297 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 1000 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 1000 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

Technology Partners: 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!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a 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…

762 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