I'm looking for an efficient way to create drafts and start an approval workflow for some of the entities in my domain. Users should easily track changes and approve or reject the changes. Would you please suggest a generic model / approach / roadmap to easily provide these functionalities?

Let's say I have this model:

public class CourierDistributionArea
{ public City City { get; set; } public Courier Courier { get; set; }
}
public class City
{ public int Id { get; set; } public string Name { get; set; }
}
public class Courier
{ public int Id { get; set; } public string Name { get; set; }
}

And let's say the user adds or updates or deletes a few CourierDistributionArea entities. How would you store the new pending approval version of these entities? And how would you design this system to easily add new type of entities to be used in this approval workflow? How would you present the changeset to the end user?

1 Answer

There are a number of options and these will depend on the likely changes made by your users, whether multiple users can provide alternative drafts simultaneously, and what storage you want to use (I'm ignorantly assuming some table-based RDBMS like SQL Server).

1. Draft Table(s)

Create a CarDraft table alongside your Car table. All columns in the target table should be present in the draft table, alongside any additional columns you require for metadata relating to the draft itself. When the draft is approved, upsert the relevant columns into the target table and delete the draft.

Pros

• No need to support multiple versions of the same entity in the same table
• Drafts don't affect OLTP on the target table in any way

Cons

• Additional table to maintain

2. Versioned Entities

Create Version and Published columns on your target table and relax the constraints on enforcing a single row with the original primary key, instead allowing multiple rows, but only one with a Published column set to true.

Pros

• No additional table to maintain
• No need to write row twice (just delete old row and flip Published bit on new one in a single transaction)

Cons

• Potentially more complicated constraints / application validation
• Additional checks when joining against Published column (easy to forget)

3. Transformation Approval

Again, depending on the operations users can perform to modify entities, you could store the change operation itself rather than the new state as a draft. This could be stored in an CarModifications table for example, potentially as a partial JSON object, or as a number or rows, each one representing a change of value to a column on the target table. Multiple rows could be grouped as a single OperationId if required.

Pros

• No changes to target table
• Multiple changes across multiple tables could be keyed on a single operation id and applied in a transaction

Cons

• More complicated modelling
• More complicated validation