Database Approve Reject WorkFlow

Question

I want to implement approve reject workflow using relational database. Currently i am using MS-SQL database along with NHibernate.

Example:

Lets say i have below entry in database

ID  Name    Location    FromDate    ToDate  ApprovalStatus
1   ABCD    London       1-Jan      31-Dec  approved

now the logged in user can change the entry using front end... which will go for approval

ID  Name    Location    FromDate    ToDate  ApprovalStatus
1   ABCD    London      1-Jan       30-Jun  edited

If the authority approves the editing then the edits should stay, but if the changes are rejected the i want to revert back the old entry.

There are few ways to implement this, but all of them need lot of boilerplate code for each table and i have around 20-30 such entities which follow the approval process as shown above. The options I can think of are

1.create another table which is replica of original table to maintain the edited entries/ approval workflow. just .

pros: easy to manage, just replace replace the original entry in case of approval, delete in case of reject

cons: too many duplicate tables + similar number of duplicate domain classes

OR

2.use same table, but create duplicate row, which keep track of the parent row and does appropriate changes on approval/rejection.

pros: reduces unnecessary tables & domain classes

cons: really complex to manage

---

I think the scenario is pretty common and there must be some material/ framework/ architecture addressing this issue. though i am not able to find out anything relevant even after much googling...

please help...

Answer 1

One object: Table + View

First, define a (single) table. This is psuedo code -- I’m guessing at your datatypes and ignoring nullability, you’d need to fix this up:

CREATE TABLE EventData
 (
   ID              int
  ,Name            varchar(50)
  ,Location        varchar(50)
  ,FromDate        datetime
  ,ToDate          datetime
  ,ApprovalStatus  tinyint   --  Foreign key to a lookup table of valid editing statuses
  ,CreatedAt       datetime  DEFAULT CURRENT_TIMESTAMP
)

Then define a view:

CREATE VIEW ApprovedEventData
AS
SELECT
  ID
 ,Name
 ,Location
 ,FromDate
 ,ToDate
FROM MyTable
WHERE ApprovalStatus = 1  --  Or whatever “Approved” is

Every time an entry is created, a row is added. Every time an entry is changed, a row is added At any given point in time, presumably on only one row per ID can be marked as “Approved”. (Managing and maintaining this could be difficult, and is outside the scope of this answer.)

To access all currently approved items:

SELECT * from ApprovedEventData

To access all items ever entered, by ID in the order they were created (i.e. that new rows indicating status were added):

SELECT * from EventData order by ID, ItemCreated

Properly speaking, the primary key would be ID + CreatedAt, under the assumption that no two editing events for a given ID would happen at the same time. You could toss in column

VersionID   int  identity(1,1)  --  Make this the primary key

as a standard surrogate key if need be.

If you need fast access to the set of Live Data, you might be able to build a clustered index on the view, but that too can get difficult.

Answer 2

why not have an additional bit column which would be an indicator for active or inactive for each id. you can simply have the old and new records in the same table and only display active records. A Job can be setup to run everyday which deletes rejected records ONLY. This will allow you save the old and new records. You can also have the job delete the old records (assuming you don't need them) if new record was approved.

So what I am trying to say is: Original Table:

ID  Name    Location    FromDate    ToDate  ApprovalStatus  isActive
1   ABCD    London       1-Jan      31-Dec  approved          1

Lets say someone edited the entry (your table will look like)-

ID  Name    Location    FromDate    ToDate  ApprovalStatus  isActive
1   ABCD    London       1-Jan      31-Dec  approved          0
1   ABCD    London      1-Jan       30-Jun  edited            1

Entry was rejected:

ID  Name    Location    FromDate    ToDate  ApprovalStatus  isActive
1   ABCD    London       1-Jan      31-Dec  approved          1
1   ABCD    London      1-Jan       30-Jun  rejected          0

IF Entry was Approved:

ID  Name    Location    FromDate    ToDate  ApprovalStatus  isActive
1   ABCD    London       1-Jan      31-Dec  approved          0
1   ABCD    London       1-Jan       30-Jun  approved         1

A SQL Job can be set up to do the following:

    DELETE FROM TableName where ApprovalStatus=rejected and isActive=0;
    GO

You can delete the approved 0 records after based on the datetime (which i am assuming you may have as you should always consider having an audit field). If not then carefully delete all approved records with isActive 0.

 DELETE FROM TableName where ApprovalStatus=0 and isActive=0;

Make sure you do some tests before you do that delete because you may potentially delete a record which is still in the edited phase and has not been rejected or approved.

Answer 3

If you want to create a new table:

Original Table (keep as is):

ID  Name    Location    FromDate    ToDate  ApprovalStatus
1   ABCD    London       1-Jan      31-Dec  approved
2   EFGH    New York     1-Jan      31-Dec  approved
3   IJKL    Berlin       1-Jan      31-Dec  approved

New Table After Change Made

ID  Name    Location    FromDate    ToDate  ApprovalStatus 
1   ABCD    London       1-Jan      31-Dec  edited
3   IJKL    Berlin       1-Jan      31-Dec  edited

New Table After Rejected (You can delete the rejected record if you want)

ID  Name    Location    FromDate    ToDate  ApprovalStatus 
1   ABCD    London       1-Jan      31-Dec  edited
3   IJKL    Berlin       1-Jan      31-Dec  rejected

New Table After Approved (You can delete the approved record if you want)

ID  Name    Location    FromDate    ToDate  ApprovalStatus 
1   ABCD    London       1-Jan      31-Dec  edited
3   IJKL    Berlin       1-Jan      31-Dec  approved

WHEN APPROVED: Update the original table with id 3 the data from the new table and delete the record from new table.