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