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 bu

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.

StackExchange Database Administrators Q#25548, answer score: 8