As stated in comments, I would store the current or effective version of a BlogStory in its respective table and keep all of its previous versions (or past states) in a separate (but related) BlogStoryVersion table.
In this manner, you may find this post helpful since it presents a comparable method for a similar scenario.
Business rules
In accordance with my understanding of your specifications, the following assertions are especially relevant:
- 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 data model
I have derived a sample IDEF1X logical data model from the aforementioned assertions in order to illustrate the method that I will describe below. This model is shown in Figure 1.
Proposed physical structure
From the above presented data model, I have created the following demo DDL statements:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.
-- 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,
CreatedDateTime DATETIME NOT NULL,
CONSTRAINT PK_UserProfile PRIMARY KEY (UserId),
CONSTRAINT UIX_UserProfile_FirstName_LastName_and_BirthDate UNIQUE
(
FirstName,
LastName,
BirthDate
)
);
CREATE TABLE BlogStory
(
BlogStoryNumber INT NOT NULL,
Title CHAR(60) NOT NULL,
Content TEXT NOT NULL,
CoverImageName CHAR(30) NOT NULL,
IsActive BOOLEAN NOT NULL,
AuthorId INT NOT NULL,
CreatedDateTime DATETIME NOT NULL,
CONSTRAINT PK_BlogStory PRIMARY KEY (BlogStoryNumber),
CONSTRAINT UIX_BlogStory_Title UNIQUE (Title),
CONSTRAINT FK_BlogStory_UserProfile FOREIGN KEY (AuthorId)
REFERENCES UserProfile (UserId)
);
CREATE TABLE BlogStoryVersion
(
BlogStoryNumber INT NOT NULL,
CreatedDateTime DATETIME NOT NULL,
Title CHAR(60) NOT NULL,
Content TEXT NOT NULL,
CoverImageName CHAR(30) NOT NULL,
IsActive BOOLEAN NOT NULL,
AuthorId INT NOT NULL,
UpdatedDateTime DATETIME NOT NULL,
CONSTRAINT PK_BlogStoryVersion PRIMARY KEY (BlogStoryNumber, CreatedDateTime),
CONSTRAINT FK_BlogStoryVersion_BlogStory FOREIGN KEY (BlogStoryNumber)
REFERENCES BlogStory (BlogStoryNumber),
CONSTRAINT FK_BlogStoryVersion_UserProfile FOREIGN KEY (AuthorId)
REFERENCES UserProfile (UserId)
)
Suggested method description
BlogStory
As you can see in the proposed physical structure, I have defined the BlogStory PRIMARY KEY (PK) data type as INT so, in this regard, you could establish an automatic process in order to store a numeric value in such column in every row insertion. If you do not mind leaving gaps occasionally in this set of values, then you can employ the AUTO_INCREMENT attribute which is commonly used in MySQL environments.
When storing all your individual BlogStory.CreatedDateTime occurrences, you can utilize the NOW() function, which returns the date and time values that are current in the database server at the specific insertion instant. To me, this practice is decidedly more suitable and less prone to errors than the use of external routines.
If, as discussed in comments, you want to avoid the posibility of storing BlogStory.Title duplicate values, you have to set up a UNIQUE INDEX for this column. Due to the fact that a given Title may be mantained over each or several blog story versions, then a UNIQUE INDEX should not be implemented in the BlogStoryVersion table.
I included the BlogStory.IsActive BOOLEAN column in case you need to provide soft or logical deletion functionality.
BlogStoryVersion
On the other hand, the BlogStoryVersion PK is composed of BlogStoryNumber and a column named CreatedDateTime which, of course, marks the exact instant in which a BlogStory instance was inserted.
BlogStoryVersion.BlogStoryNumber, besides being part of the PK, is also set as a FOREIGN KEY (FK) that references BlogStory.BlogStoryNumber, a fact that enforces referential integrity between these two tables. In this respect, implementing an automatic generation of a BlogStoryVersion.BlogStoryNumber is not necessary because, being defined as a FK, the values inserted in this column must be retrieved from the related BlogStory.BlogStoryNumber.
The column BlogStoryVersion.UpdatedDateTime should store, as expected, the point in time when a BlogStory was modified and, as a consequence, added to the BlogStoryVersion table, therefore, you can also use the NOW() function in this situation.
The interval comprehended between BlogStoryVersion.CreatedDateTime and BlogStoryVersion.UpdatedDateTime expresses the period during which a BlogStory row was current or effective.
Considerations for a Version column
It can be useful to think of BlogStoryVersion.CreatedDateTime as the column that stores the value that represents the particular version of a BlogStory. I deem this much more beneficial than a VersionId or VersionCode, since it is user-friendlier in the sense that, as you know, people tend to be more familiar with time concepts. For instance, your blog authors or readers could refer to a story version in a way similar to the following:
- “I want to see the specific version of blog story number 1750 that was created on 26 August 2015 at 9:30”.
Author and Editor
With this approach, you can identify who is the original AuthorId of a given blog story SELECTing the earliest version of a certain BlogStoryId FROM the BlogStoryVersion table by means of applying the MIN() function to BlogStoryVersion.CreatedDateTime.
In this way, each of the BlogStoryVersion.AuthorId values contained in all the later versions indicate, naturally, the authors of the respective version at hand, but one can also say that such values are, at the same time, denoting the role played by the involved users as editors of the original version of a BlogStory.
Database DATETIME columns format
About the DATETIME data type, yes, you are right, “MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format”, but you can confidently store your pertaining data in this manner, and when you have to perform a query you can easily make use of the DATE and TIME functions in order to, among other things, show the concerning values in the appropriate format for your users. Or you could also carry out this kind of data formatting in your application(s) code, if necessary.
BlogStory UPDATE process implications
When a given BlogStory suffers an UPDATE, you must ensure that the corresponding values that were in effect until such UPDATE took place are inserted into the BlogStoryVersion table. In this sense, I would highly suggest implementing a single ACID Transaction so that these operations are treated as an indivisible Unit of Work.
Using a VersionId or VersionCode column
If you decide (due to business circumstances or personal preference) to use a BlogStory.VersionId or BlogStory.VersionCode column representing a particular blog story version, you have to ponder the following possibilities:
- The
VersionCode could be required to be UNIQUE in the whole BlogStory table and also in the BlogStoryVersion table. Therefore, you have to implement a carefully tested and totally reliable method in order to generate each Code.
- Maybe the
VersionCode values could be repeated in different BlogStory occurrences, but never duplicated along with the same BlogStoryNumber. E.g., you could have blog story number 3 - version 83o7c5c and, simultaneously, a blog story number 86 - version 83o7c5c and a blog story number 958 - version 83o7c5c.
The later possibility opens another alternative:
- Keeping a
VersionNumber for your BlogStories, so you could have: blog story number 23 - versions 1, 2, 3…; blog story number 650 - versions 1, 2, 3…; blog story number 2254 - versions 1, 2, 3…; etc.
Storing original and subsequent versions in a single table
If you choose to maintain all the blog story versions in the same table, you can still take advantage of many of the concepts detailed above, for example: a composite PK consisting of an INT column (BlogStoryNumber) and a DATETIME column (CreatedDateTime), the usage of server functions in order to optimize the pertinent processes, and the Author and Editor roles.
Due to the fact that by proceeding with this approach a BlogStoryNumber will be duplicated as soon as newer versions are added, an option that is very alike to those mentioned in the previous section and that you could analyze is establishing a BlogStory PK composed of the columns BlogStoryNumber and VersionCode, this way you would be able to uniquely identify each version of a blog story. And you can also try with a combination of BlogStoryNumber with a VersionNumber.
