Textrevisioning in MySQL

Question

at the moment I am working on a cms that should support text revisioning. Because sometimes I tend to overcomplicate things I thought I ask here for improvements and advise.

I have the following 4 tables:

article

ID INT PK
createdAt DATETIME
updatedAt DATETIME

article_revision

ID INT
articleID INT FK
headline VARCHAR(255)
teaser TINYTEXT
text LONGTEXT
createdAt DATETIME

tag

ID INT
name VARCHAR(20)
createdAt DATETIME

article_revision_has_tag

articleRevisionID INT PK FK
tagID INT PK FK

So the article is the holder for general information of an article. An article can have one or more article_revisions. All assigned tags are saved in reference to the article_revision. This is so I can keep track of changing tags.

But the last thing I am not quite sure about is: How can I create a history like it is implemented in stackoverflow? For example the text is changed from three different users over time and at the end the author does a rollback to the first version. How would the database table look like to keep track of this?

I thought about something like this:

article_revision_history

ID INT PK
articleID INT FK
articleRevisionID INT FK
userID INT FK
note VARCHAR(255)
createdAt DATETIME

With this I have references to article, article_revision and an optional user table. If I use ORDER BY createdAt DESC I would get the revision history. Starting with the newest one.

Is this the best way for a revisioning history? If not what could be the best way?

And if we assume that this will be a software solution where it could be possible that over 100 articles are added to the database in a day (with more than one revision), is a full history tracking maintainable? What are your experiences?

Thanks in advance!