Implementing versioning system with MySQL

I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help.

Initially I had my blogstories table with this structure:

| Column | Type | Description |
|-----------|-------------|------------------------------------------------|
| uid | varchar(15) | 15 characters unique generated id |
| title | varchar(60) | story title |
| content | longtext | story content |
| author | varchar(10) | id of the user that originally wrote the story |
| timestamp | int | integer generated with microtime() |


After I decided I wanted to implement some versioning system for every story on the blog, the first thing that came to my mind was creating a different table to hold edits; after that, I thought I could modify the existing table to hold versions instead of edits. This is the structure that came to my mind:

| Column | Type | Description |
|------------ |------------- |------------------------------------------------ |
| story_id | varchar(15) | 15 characters unique generated id |
| version_id | varchar(5) | 5 characters unique generated id |
| editor_id | varchar(10) | id of the user that commited |
| author_id | varchar(10) | id of the user that originally wrote the story |
| timestamp | int | integer generated with microtime() |
| title | varchar(60) | current story title |
| content | longtext | current story text |
| coverimg | varchar(20) | cover image name |


The reasons why I came here:

• The uid field of the initial table was UNIQUE in the table

Analyzing the scenario —which presents characteristics associated with the subject known as temporal databases— from a conceptual perspective, one can determine that: (a) a “present” Blog Story Version and (b) a “past” Blog Story Version, although very resembling, are entities of different types.

In addition to that, when working at the logical level of abstraction, facts (represented by rows) of distinct kinds must be retained in distinct tables. In the case under consideration, even when quite similar, (i) facts about “present” Versions are different from (ii) facts about “past” Versions.

Therefore I recommend managing the situation by means of two tables:

-
one dedicated exclusively for the “current” or “present” Versions of the Blog Stories, and

-
one that is separate, but also linked with the other, for all the “previous” or “past” Versions;

each with (1) a slightly distinct number of columns and (2) a different group of constraints.

Back to the conceptual layer, I consider that —in your business environment— Author and Editor are notions that can be delineated as Roles that can be played by a User, and these important aspects depend on data derivation (via logical-level manipulation operations) and interpretation (carried out by the Blog Stories readers and writers, at the external level of the computerized information system, with the assistance of one or more application programs).

I will detail all these factors and other relevant points as follows.

Business rules

According to my understanding of your requirements, the following business rules formulations (put together in terms of the relevant entity types and their kinds of interrelationships) are specially helpful in establishing the corresponding conceptual schema:

• A User writes zero-one-or-many BlogStories

• A BlogStory holds zero-one-or-many BlogStoryVersions

• A User wrote zero-one-or-many BlogStoryVersions

Expository IDEF1X diagram

Consequently, in order to expound my suggestion by virtue of a graphical device, I have created a sample IDEF1Xa diagram that is derived from the business rules formulated above and other features that seem pertinent. It is shown in Figure 1:

Why are BlogStory and BlogStoryVersion conceptualized as two different entity types?

Because:

-
A BlogStoryVersion instance (i.e., a “past” one) always holds a value for an UpdatedDateTime property, while a BlogStory occurrence (i.e., a “present” one) never holds it.

-
Besides, the entities of those types are uniquely identified by the values of two distinct sets of properties: BlogStoryNumber (in the case of the BlogStory occurrences), and BlogStoryNumber plus CreatedDateTime (in the case of the BlogStoryVersion instances).

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is based on the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on the Entity-Relationship view of data, developed by Dr. P. P. Chen; and also on the Logical Database Design Technique, created by Robert G. Brown.

Illustrative logical SQL-DDL layout

Then, based on the conceptual analysis previously presented, I declared the logical-level design below:

```
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also you should make accurate tests to define the most
-- convenient index strategies at the physical level.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE UserProfile (
UserId INT NOT NULL,
FirstName CHAR(30) NOT NULL,
LastName CHAR(30) NOT NULL,
BirthDate DATETIME NOT NULL,
GenderCode CHAR(3) NOT NULL,
UserName CHAR(20) NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT UserProfile_PK PRIMARY KEY (UserId),
CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
FirstName,
LastName,
BirthDate,
GenderCode
),
CONSTRAINT UserProfile_AK2 UNIQUE (UserName) -- ALTERNATE KEY.
);

CREATE TABLE BlogStory (
BlogStoryNumber INT NOT NULL,
Title CHAR(60) NOT NULL,
Content TEXT NOT NULL,
CoverImageName CHAR(30) NOT NULL,
IsActive BIT(1) NOT NULL,
AuthorId INT NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT BlogStory_PK PRIMARY KEY (BlogStoryNumber),
CONSTRAINT BlogStory_AK UNIQUE (Title), -- ALTERNATE KEY.
CONSTRAINT BlogStoryToUserProfile_FK FOREIGN KEY (AuthorId)
REFERENCES UserProfile (UserId)
);

CREATE TABLE BlogStoryVersion (
BlogStoryNumber INT NOT NULL,
Cr

StackExchange Database Administrators Q#112116, answer score: 26