Approving new or amended records

djjlewis posted on Wednesday, November 22, 2006

In many of the enterprise applications I am involved in developing, there is a common requirement to have new or edited records ‘approved’ by an application administrator before they become visible in the live system.

In the past I have used object serialization to achieve this and although it works quite well, and certainly simplifies many things, I have been bitten by the versioning issues when de-serialising older assembly versions enough that I am now looking into a more database-centric solution. I was wondering if anyone else has done this kind of thing before and whether or not there are any common implementation patterns.

The way I have done this up to now is to have a database design similar to this:

[Entity]
Id (PK)
Name
Description
…

[EntityRequest]
Id (PK)
EntityId (FK to Entity)
RequestTypeId ( FK to RequestType e.g. ‘Add Request’, ‘Amend Request’ etc)
RequestStatusId (FK to RequestStatus e .g. ‘Pending’,’Accepted’,’Refected’ etc)
RequestedBy
RequestDate
SerialisedObject (blob of the object byte stream)
ObjectLength (used when de-serialising in the business layer)
…

Along with [RequestStatus] and [RequestType] lookup tables

In my business layer, I have a standard Entity Editable Root and an EntityRequest Editable Root. Admins are able to create and save Entity ERs directly, but standard users have to do this through the an EntityRequest object which itself holds a reference to the Entity ER.

When EntityRequest.Save() (and ultimately DataPortal_Update ) is called, the Entity reference is serialised and saved to the EntityRequest table along with the other request fields. In the admin screens, a request is selected and retrieved from the database, the Entity reference is de-serialised and either Accept() or Reject() methods are called on the EntityRequest ER.

If Approve() is called, this will in turn fire off some emails and call Save() on the original Entity ER. The beauty of this is if the Entity ER IsNew flag was true, then a new record is created and added to the database as normal, otherwise the original record is updated with the amended values .

I’d like to hold my hands up and admit that there are no real rights/security checking in the business layer or database; the UI presented is displayed differently depending on an AD role which is what limits normal users seeing the screens that allow objects to be directly inserted … and as a side note I wondered if I may be able to use the new security check methods in CSLA.NET 2.0 to help with this such as CanAdd or CanEdit etc - but I really do want users to be able to add and edit objects… just not persist them immediately in the database!

As I say, this technique has worked very well apart from the versioning, so I am not too keen to move away if the alternatives become much more complicated.

After discussing this internally with a colleague it was suggested to update the EntityRequest like this:

[Entity]
Id (PK)
Name
Description
RequestTypeId ( FK to RequestType e.g. ‘Add Request’, ‘Amend Request’ etc)
RequestStatusId (FK to RequestStatus e .g. ‘Pending’,’Accepted’,’Refected’ etc)
IsLocked

[EntityRequest]
Id (PK)
EntityId (FK to Entity)
RequestTypeId ( FK to RequestType e.g. ‘Add Request’, ‘Amend Request’ etc)
RequestStatusId (FK to RequestStatus e .g. ‘Pending’,’Accepted’,’Refected’ etc)
RequestedBy
RequestDate
ModifiedEntityId(FK) - only populated for amendment requests

In this case, new records are stored immediately in the database, although there status is set to pending, and type would be ‘add’.

If an existing record is edited, another record is created in the Entity table with the new values, RequestType would be set to ‘Amend’ and IsLocked set to true to avoid any other requests overwriting these changes.

I think the RequestType/Status fields have been de-normalised so you don’t need to join on EntityRequest everytime you query the Entity table.

What do you think would be the best way to model this in the business layer. Would it still require the EnityRequest object to handle the insert and add additional info to the RequestType/Status fields.

Finally, do either of these approaches seem sound, or is there a ‘third-way’ which I may be missing?

Regards,

Dan.

Bayu replied on Wednesday, November 22, 2006

Several thoughts:

-  always start by keeping your relational model fully normalized. Experience has taught that denormalization will work against you sooner or later. Considering performance in your app design is good, but resort to denormalization only when your DB design has actually proven to be the bottleneck. Using outer joins you can easily merge your request data into the Entity table, and using inner joins you can merge Entity data into your request data. These are simple, straightforward joins, so you may expect your DB to do a reasonable job when optimizing the evaluation plan for these queries.

- storing the Entity's in your table with a Status field that indicates that approval is pending looks fine to me. Of course you would have to make sure all your SQL statements take this field into account, but that is something you have control over, so it would work just fine. I would recommend creating separate BOs for Entity's that are 'live' and those that are still pending. This way you avoid confusion with your fellow developers (and yourself perhaps ;-) ).

- the request BO may no longer be necessary. If your workflow is just about Approved and Unapproved entities, then having those 2 BOs I suggested should do the job. I you have a more elaborate workflow then your use cases would grow and correspondingly you would need more BOs that explicitly model the individual steps. In fact, you have described 2 use cases (Live Entities and ToBeApproved Entities) so having those 2 BOs exactly cover your needs right now.


Hope this helps.

Regards,
Bayu

djjlewis replied on Wednesday, November 22, 2006

Thanks Bayu,

You've certainly given me plenty to think about... My initial thought was to just put the status request property directly in the Entity ER and have separate Read-only list collections for Pending and Regular/Approved which themselves would call different sprocs. This, I suppose, is the classic OO to RDBMS mindset. Even though I often repeat the mantra "objects are defined by behaviour not data", I didn't think about having separate BOs for different request cases.

My only question then is this: given that the field/data between approved and pending records is almost identical, do you think they should inherit from an abstract/mustinherit base class that defines the common behaviour?

Regards,

Dan.

Bayu replied on Wednesday, November 22, 2006

djjlewis:

Thanks Bayu,

My only question then is this: given that the field/data between approved and pending records is almost identical, do you think they should inherit from an abstract/mustinherit base class that defines the common behaviour?

Certainly.

That's applying good old fashioned inheritance. To create an abstract (mustinherit) base class that implements all shared stuff makes perfect (OO) sense.

'To model after behavior' is merely from a conceptual viewpoint, so the fact that you (conceptually) have distinct BOs that are each specifically designed to address a particular use case. On the implementation level you are indeed almost bound to observe shared functionality that can go into a base class.

Bayu

pelinville replied on Wednesday, November 22, 2006