Why are records not really deleted in the database?

9 years ago

I use SQL Server 2008 Management Studio to do some bulk-upload and I discovered that the records that I have deleted via the admin panel are not really deleted but are only marked with "Deleted = True".

Why is that?

Is there a way to clean up the database from these deleted records?

Reply most appreciated!

Regards, Stephan.
9 years ago
To be clear: I deleted them via the NopCommerce admin panel. NOT via SQL Server (I do not dare that yet..;)..)
9 years ago

I think that nopcommerece is not deleting data physically, and just writing the flag as delete=true, that means that logical deletion. so in case if you want any information in future, you can get it back. and it'll not show anywhere in the website.

9 years ago
Yeah that might be the case but how do you get data out of the database then? That might be considered useful to keep things clean and to reduce database / table size...
9 years ago
You could run a local query and remove the records with Deleted=True
9 years ago
using nC1.9 you can download the datebase - fix it in dev - then upload it again.
nice !
9 years ago
Records are not deleted for what is called "Data Integrity". If you delete something and something was referencing what you deleted, you will lose that reference. It's like if you had a real "paper" document and you shredded it and later you came across another paper document that referenced the document you created, you would have no way of knowing what information it was talking about. It is in your best interest to keep all your information for at least 3 years before deleting it for purposes of auditing.
9 years ago

It is ok for "logical" deletion to keep data integrity.

But wouldn't it be a good idea to include filter options in the admin pages to show deleted items in the category and product lists?

And maybe adding a "restore" option?

9 years ago
There are a couple options.

You can actually delete the data. Or you could simply drop the tables and then re-create them without any data. Run the script, but do not load any data. You would need to keep only a couple tables. Those tables, would be the tables that store the login info, the option info and any others that you want to keep.

If you want to delete the data manually, you would need to start with the most distant child table. If the database is designed correctly, you will not be able to truncate a table that has a reference. That means, no orphan records. Which is what data integrity is all about. Knowing that your data is complete and accurate.

There is nothing stopping you from dropping that data, or any database object(s). I have dropped most of mine and created them again. I have a test database and an actual database that stores my products. All I do is modify the connection string, depending on what I need to do, or want to see for troubleshooting issues, or even training a new employee.

9 years ago
AndrewSQLDBA wrote:
Or you could simply drop the tables and then re-create them without any data. Run the script, but do not load any data.

Hi Andrew, can you elaborate a bit more on this please.

I'm doing fairly OK with SQL Server Management Studio (making tables, uploading records, basic queries, here or there a join) but your words sound a bit to much DBA to me ;)

What do you mean with "drop the tables"?
"Run the script" = which script?

Hope I'm not opening up a DBA-course conversation with you. Dont wanna bother you with that.