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
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
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
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
Playlists needed to have Foreign Keys to both the
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?