Separate archive tables or soft delete for inventory database 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)


Archive Table Example (Other related tables omitted)


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



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