SQL Server Database - Hidden Fields?!

Question

Hello!

I'm implementing CRUD on my silverlight application, however I don't want to implement the Delete functionality in the traditional way, instead I'd like to set the data to be hidden instead inside the database.

Does anyone know of a way of doing this with an SQL Server Database?

Help greatly appreciated.

Answer 1

You can add another column to the table "deleted" which has value 0 or 1, and display only those records with deleted = 0.

ALTER TABLE TheTable ADD COLUMN deleted BIT NOT NULL DEFAULT 0

You can also create view which takes only undeleted rows.

CREATE VIEW undeleted AS SELECT * FROM TheTable WHERE deleted = 0

And you delete command would look like this:

UPDATE TheTable SET deleted = 1 WHERE id = ...

Answer 2

Be careful with this kind of implementation because soft deletes break referential integrity and you have to enforce integrity in your entities using custom logic.

Answer 3

You could do as Lukasz Lysik suggests, and have a field that serves as a flag for "deleted" rows, filtering them out when you don't want them showing up. I've used that in a number of applications.

An alternate suggestion would be to add an extra status assignment if there's a pre-existing status code. For example, in a class attendance app we use internally an attendance record could be "Imported", "Registered", "Completed", "Incomplete", etc.* - we added a "Deleted" option for times where there are unintentional duplicates. That way we have a record and we're not just throwing a new column at the problem.

*That is the display name for a numeric code used behind the scenes. Just clarifying. :)

Answer 4

Extending Lukasz' idea, a datetime column is useful too.

• NULL = current
• Value = when soft deleted

This adds simple versioning that a bit column can not which may work better

Answer 5

In most situations I would rather archive the deleted rows to an archive table with a delete trigger. This way I can also capture who deleted each row and the deleted rows don't impact my performance. You can then create a view that unions both tables together when you want to include the deleted ones.

Answer 6

Solution with triggers

If you are friends with DB trigger, then you might consider:

• add a DeletedAt and DeletedBy columns to your tables
• create a view for each tables (ex: for table Customer have a CustomerView view, which would filter out rows that have DeletedAt not null (idea of gbn with date columns)
• all your CRUD operations perform as usual, but not on the Customer table, but on the CustomerView
• add INSTEAD OF DELETE trigger that would mark the row as delete instead of physically deleting it.
  • you may want to do a bit more complex stuff there like ensuring that all FK references to this row are also "logically" deleted in order to still have logical referential integrity

I you choose to use this pattern, I would probably name my tables differently like TCustomer, and views just Customer for clarity of client code.