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?
Best Answer
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.
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
andApprovedImage
.when user upload then record goes toPendingImage
.when that record is approved then it goes toApprovedImage
.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 beApprovedImage
I will modify my answer after clearing few doubts.