How to handle entity versions in SQL Server?

Question

I have a SQL Server 2008 R2 database that contains a table that holds my entities from an ad. It looks something like this :

Title
Price
CreatedDate
UpdatedDate
Description
EndDate
PublishedDate
StateOfProduct
Id
UserId
UserLocationId
Visits
CategoryId
TypeOfAd
AdminComment
ReviewedDate
AmountOfImages
UserPostCode
UserEmail
UserPhoneNr
UserPassword
OwnerType
AdminUserId
InactivatedDate
OldPrice
PriceLastChange

An Ad entity can have the following stages : Published, Waiting for review, Outdated.

Say that the ad is published and the customer decides to edit the ad, this means that the settings on the ad above will change. A change like this demands a new review and that means that the ad will go from Published to Waiting for review.

The problem with this is that when a ad is changed and is waiting for review it is not published anymore.

What I need is to let the ad be published with the old (reviewed) data and when the review is done of the updated ad it will switch places but keep the old id.

The question is how to handle this the right way?

I Suppose that I could have two identical tables to keep record count down and to make it simpler to find published ads but it will be a lot more work to use Entity Framework against two identical tables at the same time.

Another solution might be to add two extra columns in the Ads table that holds the version of the ad and the original adId. The flow will in this case be something like this :

1. Create Ad(id=1), originalId = null, version = null.
2. Review Ad(id=1)
3. Publish Ad(id=1), version = 1
4. Customer Updates Ad, new Ad(id=2) is created with version set to null(=waiting for review) and originalId = 1
5. Reviewed Ad(id=2) done, Ad(id=1) will be copied to a new records (id = 3 (or2)), originalId set to 1. Ad(id=2) will be moved/copied to Ad(id1), originalId = null, version = 2.

The ad table might however be vary large with this but it will be a lot easier to trace ads history.

Answer 1

I believe this could be done using many approaches, it's just a matter of preference. What I would do is make a new table named, say, ReviewedAds with int Id, int AdId, bit Reviewed. 1:many relationship with Ads table so AdId from this table is a foreign key and matches Id from Ads table. Then I would display only those ads whose Reviewed bit is set to '1'. Two records in ReviewedAds couldn't have the same AdId and Reviewed bit set to '1'.