Why are records not really deleted in the database?

Hi,

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.

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

Hi,

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.

Thanks
Jigar

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...

You could run a local query and remove the records with Deleted=True

using nC1.9 you can download the datebase - fix it in dev - then upload it again.
nice !
A

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.

Hello!

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?


Bye!

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.

Andrew SQLDBA

AndrewSQLDBA wrote:

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.

Thnx!