What techniques to use when designing one or more tables to allow keeping a pending and approved version of objects?

by Imbajoe   Last Updated May 23, 2018 01:06 AM

I have several objects in my systems that need to go through approvals. Users can create these objects, such as uploading a jpg to the server as a new Media object, and they need to be in a pending state until a Reviewer approves it. Users can also update objects, such as uploading a new jpg to the previous Media object. In this case, I need to store the approved version of this Media object alongside the pending version until the pending version gets approved (or deleted) by a Reviewer.

I don't need to store a complete history of objects, only the approved version and the most recent pending version until it gets approved or deleted.

As far as reading from this table, I need to be able to search for objects, where objects with both a pending and approved versions will only return the pending version. I also need to be able to get only the approved versions of objects.

Objects, for example Media, can be assigned as children of other objects, for example as Slots in a Playlist. I'm hoping that if you assign a Media to a Playlist, as the Media object gets updated with newly approved versions, the association of Media to Playlist doesn't need to be altered to reflect the change in object.

My previous attempt at designing this did not turn out as easy to use as I would like. I created tables for these objects that require approval by have a column for ModerationStatus which held information about whether the object was pending or approved, and used a Primary Key on ID and ModerationStatus. This way I could assign objects as a child of other objects, and keep track of the ID for the child which wouldn't change as the object was subjected to multiple updated and approvals. It turned out clunky, the Slots for Playlists needed to have Foreign Keys to both the ID and ModerationStatus of these objects while I wish I would only need an ID, searching for objects with a priority on their pending versions required using a subquery in the Where clause. However I am no expert on SQL design, so these little quirks which see I see as "clunky" may be fine or expected as from a DBA's standpoint. Regardless I felt the need to look further before committing to a design.

Are there any suggestions or well understood techniques you can recommend for designing such a schema?

Related Questions

How To Manage Random Data Without Repitition?

Updated August 04, 2015 14:02 PM

Encapsulate table with view

Updated March 10, 2016 07:02 AM

Is my data 'Big Data'?

Updated June 03, 2015 22:02 PM