score:3

Accepted answer

The long answer is to learn about Time-Oriented Database Applications, there's a web site that has an eBook about it.

The short version is to either have another table for the history or change you existing table to allow for history.

Think about how you would differentiate between "original version", "edited versions" and "most recent versions". It could be simply by date, or you could try to maintain your own version number column, or you might even want some kind of flag or bit field (I don't recommend this option).

UPDATE

One possible solution (hard to know the best one for your scenario, so just an example):

Keep your existing table for the most recent version, as that's what you will probably need most of the time.

Add another table for historical versions, and in code (or a SQL Trigger if you have to) copy the existing version to the history table before saving the update. The schema of your history table could look something like this:

CREATE TABLE [dbo].[NewsHistory]
(
    NewsHistoryID int IDENTITY(1, 1) PRIMARY KEY,
    NewsID int NOT NULL,
    Title varchar(100),
    Headline varchar(200),
    Content varchar(MAX),
    CreatedAt datetime NOT NULL,
    CreatedBy int NOT NULL
)

Just populate NewsID, Title, Headline, Content, CreatedAt, CreatedBy from the existing table before writing an update.

When you want to see the history for a news item, simply:

SELECT
    Title,
    Headline,
    Content,
    CreatedAt,
    CreatedBy
FROM NewsHistory
WHERE NewsID = @newsID
ORDER BY NewsHistoryID

Sort by date if you prefer, but natural ordering should work here if everything is inserted in the right order. Again, depends on your needs/environment.

Read More

score:0

See, I suppose you want to track article's editing version, So use below schema for that.

NewsId,User,Title,HeadLine,Content,Status,PostType,UpdatedBy,UpdatedDate

Status contains enum like Published,Draft something.! So you can find published posts.

PostType contains enum like Revision,Final so you can find revision of any news article.

UpdatedBy contains user's Id who updates news & UpdatedDate contains date when post is updated.

score:0

I’d go with pretty much identical table structure for history.

Table NewsHistory News_History_ID, News id, Revision, title, headline, content, date, user

News_ID would reference the latest version of the news in news table Revision would be used to make tracking easier

What you can do is to create UPDATE trigger in your news table that will automatically copy current version into NewsHistory table.


More questions

More questions with similar tag