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

Question

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?

Answer 1

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

Why you wish to have only ID.you can have composite FK.Suppose only ID was FK then what problem it would solve ?This is not clear.

searching for objects with a priority on their pending versions required using a subquery in the Where clause

This requirement is not clear to me.can you explain with example ?

Also how this table will be populated on daily basis ?

With how many rows will be pending and approved. Do you soft delete or permanent delete ?

You can keep 2 tables . PendingImage and ApprovedImage.when user upload then record goes to PendingImage.when that record is approved then it goes to ApprovedImage.

Child table will have FK relation with ApprovedImage table.

I think only in few places you will require info from both table. And very frequently use table in front end will be ApprovedImage

I will modify my answer after clearing few doubts.