How to save all versions of posts in mysql database

Question

It is popular to save all versions of posts when editing (like in stackexchange projects), as we can restore old versions. I wonder what is the best way to save all versions.

Method 1: Store all versions in the same table, and adding a column for order or active version. This will makes the table too long.

Method 2: Create an archive table to store older versions.

In both methods, I wonder how deals with the row ID which is the main identifier of the article.

Answer 1

The "best" way to save revision history depends on what your specific goals/constraints are -- and you haven't mentioned these.

But here some thoughts about your two suggested methods:

• create one table for posts, and one for post history, for example:

  create table posts (
    id int primary key,
    userid int
  );
  
  create table posthistory (
    postid int,
    revisionid int,
    content varchar(1000),
    foreign key (postid) references posts(id),
    primary key (postid, revisionid)
  );
  

(Obviously there would be more columns, foreign keys, etc.) This is straightforward to implement and easy to understand (and easy to let the RDBMS maintain referential integrity), but as you mentioned may result in posthistory have too many rows to be searched quickly enough.

Note that postid is a foreign key in posthistory (and the PK of posts).

• Use a denormalized schema where all of the latest revisions are in one table, and previous revisions are in a separate table. This requires more logic on the part of the program, i.e. when I add a new version, replace the post with the same id in the post table, and also add this to the revision table.

(This may be what SE sites use, based on the data dump in the SE Data Explorer. Or maybe not, I can't tell.)

For this approach, postid is also a foreign key in the posthistory table, and the primary key in the posts table.