Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Database design for text revisions

This question contains some excellent coverage of how to design a database history/revision scheme for data like numbers or multiple choice fields.

However, there is not much discussion of large text fields, as often found in blog/Q&A/wiki/document type systems.

So, what would be considered good practice for storing the history of a text field in a database based editing system? Is storing it in the database even a good idea?

like image 606
John McAleely Avatar asked Apr 15 '09 08:04

John McAleely


People also ask

How do you make a versioning database?

Versioning a database means sharing all changes of a database that are neccessary for other team members in order to get the project running properly. Database versioning starts with a settled database schema (skeleton) and optionally with some data.

What are the 3 database design steps?

The methodology is depicted as a bit by bit guide to the three main phases of database design, namely: conceptual, logical, and physical design.


3 Answers

I develop a wiki engine and page/article revisions are stored in a database table. Each revision has a sequential revision number, while the "current" revision is marked with -1 (just to avoid NULL).

Revision text is stored as-is, not diffed or something like that.

I think that performance is not a problem because you are not likely to access older revisions very frequently.

like image 90
Dario Solera Avatar answered Oct 18 '22 07:10

Dario Solera


Given the current state of HDD art, it just does not worth the effort trying to optimize text storage mechanisms: Document (ID, Name) and DocumentRevision (ID, DocumentID, Contents) tables will do the job. the ID in DocumentRevision may also serve as a "repository"-wide revision number. If this is not the behavior you want, assign a separate VersionID to each Document Revision.

like image 28
Anton Gogolev Avatar answered Oct 18 '22 05:10

Anton Gogolev


Often the most sensible way of tracking the versions of a document is to keep track of the changes made to it. Then, if a particular version is requested it can be rebuilt from the current document and the partial set of changes.

So if you have a good method of describing the types of changes to a document (this will depend largely on what the document is and how it used) then by all means use a database to track the changes and therefore the versions.

like image 23
CurtainDog Avatar answered Oct 18 '22 06:10

CurtainDog