I have several objects in my systems that need to go through approvals. User
s 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. User
s 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 Slot
s 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 Slot
s for Playlist
s 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?