I have been focusing on a task that needs draft/live versions of content and also have considered a design for example below:

Article
    ID
    Creator
    CreationDate
    DraftContent(fk to ArticleContent)
    PublicContent(fk to ArticleContent)
    IsPendingApproval

ArticleContent
    Title
    Body

I'm wondering if it might be easier to alter the foreign secrets upon articles being released or if it's easier to just copy the contents in the draft table towards the live table.

Any suggestions?

Edit: Both draft and live versions exist at the same time even though live version is the only person that's visible towards the public. There are only able to be one draft and something live table

Area of the reason behind this design would be to pressure customers to obtain their articles approved before they're going live.

Update:

We made the decision to make use of Kieren's solution having a slight modification. Rather than utilizing a column for products like IsPublished IsLive we made the decision to utilize a single condition column. Otherwise the look continued to be exactly the same.

Draft articles that become live after which are 'published'

The typical factor is always to possess a status/type flag around the article table - IsLive.

Using separate tables is unnecessary and redundant altering foreign secrets does not make much sense either. Think about the content like a valid object, be it draft or live. The only real difference is, in most cases you simply want to display live articles. In some instances later on, you might like to display both.

Articles that could be edited and also have a new draft version after initially becoming live

When it comes to one article getting both an active and draft version - the most typical pattern is always to possess a master Article entity/object, after which say ArticleVersion originating from that. The ArticleVersion might have the IsLive property, as well as, the Article itself might have a house, CurrentLiveVersionId. This way there might be an active and draft versions laying around, but you'd only usually join Article to the ArticleVersion with that CurrentLiveVersionId to obtain the current live version.

Benefits of getting the ArticleVersion table include the truth that the whole good reputation for articles, a changelog, could be saved, so that you can revert to previous versions as needed, or review changes. All for any really low implementation cost..

Tell me basically can clarify this process.

Your design looks appropriate in my experience. Whenever a latest version goes live, I'd:

  1. UPDATE the PublicContent answer to indicate the (formerly) draft article.

  2. DELETE no-longer-recommended formerly-released article.

  3. NULL the DraftContent key or, in case your model requires always getting a draft version, INSERT a brand new, empty draft into ArticleContent and point the DraftContent answer to it.