I'm dealing with system like Content management systems that require to versioning documents.
what's best practice and methodology for create database for this with any how its table?
I'd look first at just how MediaWiki did it within their free project that runs Wikipedia. They are about versioning.
After I did this after i thought it was labored well to possess a table structure like:
tbl.posts postid(PK) | title | category tbl.revisions revisionid(PK) | postid(FK) | authorid(FK) | content | date
Fields that may be transformed should enter in the revisions table (e.g. content), stuff that will not use the posts table (postid, category etc.).
From things i heard around the last podcast StackOverflow does such like.
I favor to keep the brand new (the brand new revision) exactly the same way because the posts when they're added the very first time, but I've got a column known as "childOf" which stores the ID of parent publish, the the query looks something similar to:
SELECT * FROM posts WHERE condition=value ORDER BY childOf DESC;
In my opinion 's better to have two different tables one using the data that does not have to be versioned, and the other one using the data which must be versioned (as Spikolynn stated in theOrher previous answer).
The problem I have found when utilizing just one table is when other table includes a foreign key pointing to that particular versioned table, because the primary key changes each time the record is up-to-date the reference sheds. This can lead to very complex and inefficient queries to be able to recover p data.
Chukc, what you might do in order to fix this problem is as simple as adding an additional area and employ that because the foreignKey
Let us if you have posts:
id, revision, name, content
Revision could be:
id-publish or id-draft or id-revision-N
after this you link your other table to revision id-publish rather than id. Just be sure you index it :)