Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm building an inventory database that tracks computer equipment and other hardware devices. At some point in any device's life it is retired and gets archived. After it becomes archived it needs to be tracked as it is removed from service and properly disposed. I originally designed the archiving mechanism using an exact replica of the active database that would receive its data using a trigger on delete from the active database. The archive database includes replicas of all the related tables because as certain foreign related records are no longer pertinent, they should not be accessible to users to use with new devices, but are required for referential integrity and querying with the archive tables. Keep in mind that the concept of archive here is not just to keep a history or a log. The archive is a part of the business process, and users will need to query and update devices that are both active and archived.

The ERD below uses the Inventory.DeviceType table as an example where all entries and updates are copied to the Archive.DeviceType table. When users should no longer be able to enter inventory records of a certain device type, it is deleted from the Inventory.DeviceType table, but remains in the archive table. This pattern is used on all tables to ensure the archives refer to valid data, hence the replica of all tables.

Active Table Example (Other related tables omitted) Active ERD

Archive Table Example (Other related tables omitted) Archive ERD

Problem

I'm trying to figure out how I would query the database if I don't know if a device is active or archived? For example, if a user has a serial number and wants to find out information about the device, and they are unaware of whether it has been archived.

Option 1: Create a view based on a union all?

Option 2: Query the active database and then query the archive if the first query returns nothing?

The saga continues...

An associate suggested that I eliminate the archive database and use a soft delete scheme. I built a model using this idea, and then started running into a host of other problems.

Here are the same tables using the soft delete scheme.

Soft Delete Example

Soft Delete ERD

With this setup, devices are archived by setting IsArchived field to true and entering an ArchivedDate. I could query any device easily whether it is active or archived. (Please ignore the IsActive field, as this is used for an unrelated concept).

Take notice of the Phone subtype table, where I had to propagate an index of DeviceID and IsArchived flag because phone numbers must be unique for active devices. I have to do this with other subtype tables as well. I don't know if this is a good or bad design.

This part really confuses me...

What are best practices for handling soft deletes where foreign key values can be marked as deleted. The only thing I can think of is create a routine that searches for all records that are related to deleted data, and create a report for users to resolve the discrepancies. For example, if a table of locations is related to the devices table, and some locations are soft deleted, then the devices refer to locations that no longer exist and must be moved.

By the way, I'm using MS SQL Server 2008 R2, and I plan on using Entity Framework 4 in my application. I value database maintainability over performance.

Thank you for reading.

share|improve this question
    
To propagate IsArchived flag, you can use ON UPDATE CASCADE. Because you search on serial number, you want to keep both current and archived rows in one table, with UNIQUE or PRIMARY KEY enforcing its uniqueness. –  AlexKuznetsov Oct 7 '12 at 1:50
add comment

3 Answers

up vote 2 down vote accepted

I would say that if your users are going to need to query the Archive data, then using the bit flag or soft delete is easier. If the users don't need the data any longer, then I would go with the archive tables.

Based on your description above, I would suggest going with the Soft Delete version. I can tell you from experience in one of our systems, we went with an archive schema to move older data to and it lead to nothing but issues because the users needed access to the data. So it lead to using UNION ALL on every query we had to run.

As a result of the issues, we stopped that route and moved to the soft delete, which is much easier.

We added a bit flag to all of the tables it was needed and then we just included this in the WHERE clause when querying the data.

A suggestion would be to make sure that this field has a default value when you INSERT data. If you are using IsArchived then the default value on the column would be false since you do not want it archived immediately.

share|improve this answer
add comment

I've been wrestling with the question of soft delete vs archive tables myself recently, and obviously there are a lot of articles on the web saying "don't soft delete, archive". I've yet to find the archive advocate who even acknowledges your problem exists, though, but I think I have worked out why.

At issue is that there are two reasons that a table may be the PK end of a foreign key: because it logically owns the data in the other table, or because it is being references. An Order "owns" it's OrderDetails. An Address merely references a State.

When all the FKs that reference a table are "owned by" relationships, you should not soft delete.

Your DeviceTypes are being referenced, not owned, by Devices. The archive pattern doesn't work here, it gets in the way of enforcing referential integrity.

Here's the thing though: discontinuing a DeviceType is not logically a delete. The data is still live in the sense of being referenced by devices. You can't (logically) delete a DeviceType that is in use. So, since your logical operation is not a delete, your use of the NotAvailable flag is actually not a soft delete. I think this is why the archive advocates don't address it.

For what it's worth, in my case I went with archive tables, because I do really want to logically delete data but retain for auditing purposes only. The reason behind my choice wasn't really anything to do with the database though, it was because EntityFramework does not have good support for soft deletes, I couldn't find a good way to enforce them on navigation properties.

In SQL Server 2008 R2 you can mitigate most of the disadvantages archive advocates claim soft deletes have. You can use filtered indexes to reduce the performance penalty of carrying a bunch of deleted records in your table (and the act of soft deleting is way cheaper than the act of archiving, unless you also partition your table, which might make sense for backup strategy). I believe you can also enforce referential integrity on the delete flag too, borrowing from a technique I once saw in a Celko article - child tables can have a ParentIsDeleted flag and two FKs to their parent, one just on ParentId, the other on ParentId, ParentIsDeleted. You could even have ParentIsDeleted, SelfIsDeleted and make IsDeleted a persisted computed field so undelete can distinguish direct deletes of children from deletes cascaded from the parent. However, for you, you're not really deleting, and you do not want to prevent or cascade the unavailability of a DeviceType for which Devices exist.

share|improve this answer
add comment

You could partition the key tables on a combination of the primary id and the IsArchived flag; this would have the dual benefit of making queries easy and maintaining performance of queries etc against the non-archived records.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.