CODEWITHSUNDEEP
database
Database_Query
Database_Query

Reputation: 634

Database schema for tracking edit history

I am creating a question and answer website for a government competition, where a user can ask the question and get the answer.

I need to allow the users to edit each others question and answers like StackOverflow. The problem is that I need to store the version history, so that incorrect edits can be reverted.

For this I have made a new table and stored the original post in it until the edit is successfully commited.

How can I keep a revision history so that previous versions can be displayed, and if necessary the edit can be reverted?

Upvotes: 0

Views: 395

Answers (2)

Gilbert Le Blanc
Gilbert Le Blanc

Reputation: 51565

You keep a revision history by defining your question & answer table with a counter field.

Question                 Answer
--------                 ------ 
Question ID              Answer ID
Edit counter             Edit counter
Question text            Answer Text

Question ID and Answer ID are auto incrementing integers.

When the question or answer is first posted, the edit counter is zero. After the first edit, you write a new row with the edit counter set to 1. The next edit is a new row with the edit counter set to 2. And so on.

The primary index to these tables is the ID and edit counter. The ID is ascending and the edit counter is descending. That way, the most recent edit is the first row retrieved. The edit counter tells you how many edits have been made.

Upvotes: 4

arkascha
arkascha

Reputation: 42974

Don't use a separate table. If the only reason for not using the newer version is an unfinished or invalid editing session then rely on plain transactions in the database. You simply replace the text in the database inside a transaction. That transaction is only committed when the edit is valued as 'valid'. Otherwise you rollback the transaction.

If however you require all previous revisions, which makes sense in most cases, then still keep things in a single table. Just add version after version for each edit of a question or answer. Fetch the newest if no id is specified. This way you can still access all previous revisions.

Upvotes: 1

Related Questions