What is the optimal way to design an approval queue and audit trail for entities in SQL Server 2008 R2?

Question

Simplified Explanation: I have three main tables: customer, phone, and customer_phone to resolve the many-to-many. I need to ensure that only admins can insert/update these tables without approval. Everyone else needs to have their stuff go into an approval queue so admins can approve it. The admin needs to see the current value(s) and the proposed changes before approving/denying. The system should log who submitted the information, and who approved it (when applicable) in an audit trail, as well as anyone who modified it. The current approach employed has three tables for every entity:

1. Main entity table
2. Holding table for unapproved records
3. Audit log table for each entity

I think there should be some kind of simpler solution to this. The current system has problems when:

• An admin tries to approve an entity before the main entity is approved that it is linked to
• The record already exists in the database but linking it to another record needs to be approved

This seems like a less-than-optimal solution to me. Is there a better way?

Lengthier Explanation: Here's a simplified view of the scenario: I have a primary entity and secondary entities linked to it via linking tables. Admins can simply add/edit any entity at will, but standard users must get approval from admins before their changes take effect. The problem is that sometimes when a standard user enters a primary entity and linked entities, the admin must approve the primary entity before linked ones can take effect. I want the admins to be able to see the old value and the proposed new value before approving, and I want to ensure they approve the primary entity first. For example, let's assume we have the following tables below, and customer is the primary entity, phone is the secondary entity, then there is a linking table to resolve the many-to-many relationship:

CREATE TABLE [dbo].[customer](
  [Customer_ID] [int] NOT NULL,
  [Customer_Name] [varchar](255) NOT NULL,
PRIMARY KEY CLUSTERED ([Customer_ID] ASC)

CREATE TABLE [dbo].[phone](
  [Phone_ID] [int] NOT NULL,
  [Phone_Number] [int] NOT NULL,
PRIMARY KEY CLUSTERED ([Phone_ID] ASC)

CREATE TABLE [dbo].[customer_phone](
  [Customer_Phone_ID] [int] NOT NULL,
  [Customer_ID] [int] NOT NULL,
  [Phone_ID] [int] NOT NULL,
  [Approved] [char](1) NOT NULL, --Y/N
PRIMARY KEY CLUSTERED ([Phone_ID] ASC)

Now only an admin can add a customer or a phone number without approval. If a standard user wants to add someone, it goes into a holding table. This is how that currently looks in my system. In addition to the above tables:

CREATE TABLE [dbo].[customer_holding](
  [Customer_ID] [int] NOT NULL,
  [Customer_Name] [varchar](255) NOT NULL,
  [Standard_User_ID] [int] NOT NULL, --who added record
PRIMARY KEY CLUSTERED ([Customer_ID] ASC)

CREATE TABLE [dbo].[phone_holding](
  [Phone_ID] [int] NOT NULL,
  [Phone_Number] [int] NOT NULL,
  [Standard_User_ID] [int] NOT NULL, --who added record
PRIMARY KEY CLUSTERED ([Phone_ID] ASC)

Programatically standard users' submissions go to the holding table until an admin approves them. But then I also need an audit log for the user who submitted the original record plus who approved the record and updates to it. The current system accomplishes this with a third auditing table for each record:

CREATE TABLE [dbo].[customer_audit](
  [Customer_ID] [int] NOT NULL,
  [User_ID] [int] NULL,
  [When_Approved] [datetime] NOT NULL,
  [Approved] [char](1) NOT NULL, --Y/N
PRIMARY KEY CLUSTERED ([Customer_ID] ASC)

CREATE TABLE [dbo].[phone_audit](
  [Phone_ID] [int] NOT NULL,
  [User_ID] [int] NULL,
  [When_Approved] [datetime] NOT NULL,
  [Approved] [char](1) NOT NULL, --Y/N
PRIMARY KEY CLUSTERED ([Phone_ID] ASC)

CREATE TABLE [dbo].[customer_phone_audit](
  [Customer_Phone_ID] [int] NOT NULL,
  [User_ID] [int] NULL,
  [Action_Performed] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED ([Phone_ID] ASC)

Essentially every entity table has a holding table and an audit table for actions associated with that entity. This seems redundant and problematic to me, although the current system does mostly work using this design. I think there should be some kind of simpler solution to this. The current system has problems when:

1. An admin tries to approve the phone before the customer is approved that it is linked to
2. The phone number already exists in the database but linking it to another customer needs to be approved

It seems that these problems could be resolved through better design. What could be done better? Three tables for every entity seems redundant to me, is there a better way? Thanks,

Answer 1

You could greatly simplify this by adding a few fields to your existing tables.

Add:

• ChangeDate - smalldatetime that indicates when a record was added
• Active - BIT
• ChangeBy - user who added the record
• ApprovedBy - admin who OKd the record

You keep all your old records, so an update is really just a new row followed by changing the ACTIVE bit to off for the old record and on for the new one. Your audit trail is built into your data.

The views that you use to expose the data can filter on the ACTIVE bit so no unapproved changes are sent out. It will also assist in conflict resolution since EVERY proposed change has a row. If two users propose a change the admin can choose one to accept, or make a new entry consolidating them.