6

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,

1 Answer 1

5

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.

8
  • Yes, but what about maintaining a historical record of who originally created the record and all updates since? Also, what about when someone edits an existing record and the changes are not yet approved? I want the original approved record to continue showing until the changes are approved by an admin.
    – Dan
    Feb 6, 2012 at 21:37
  • Every update is it's own row. So the MIN(created) for a record will be the "original"
    – JNK
    Feb 6, 2012 at 21:38
  • But how will all the other items linked to it remain linked once the update is approved? The links are all maintained in separate tables, and there are actually A LOT of linking tables. Customer can be linked to one or many phones, addresses, orders, etc.? Pardon my questions, just trying to figure this out. Would I have to programatically update the links in all those tables every time an update is applied? Seems like a lot of opportunity for errors.
    – Dan
    Feb 6, 2012 at 21:44
  • Or do updates get written to the original record, and then the temp update record gets deleted?
    – Dan
    Feb 6, 2012 at 21:45
  • It'll depend on how you want to set it up. Come to chat: chat.stackexchange.com/rooms/179/the-heap
    – JNK
    Feb 6, 2012 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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