It may not be clear so I'll develop the idea:

The point is to develop a web interface to write down notes (and anything you like), and track its evolution. My question is then: how can I store such information such that I can also keep track of modification history?

On the relational database world it would ideally look like:
Table document: | docId | authId | content | <meta> |
Table documentHist: | docId | editDate | <data> |

The question is about what to store as the documentHist.<data>. Should I store here all the revisions (easy but huge replication)? Or should I store only differences? (smarter, but no see how I could do this (without implementing a kind of versioning system myself).

That's why I previously mentionned Git, and even more Github which precisely do it: you can edit files and commit. We could then use here Git "under the hood" for our versioning. I'm just not sure how difficult this would be. (Select/Update &co looks easier to me that handling files and git command from web server, I'm maybe wrong)

Thanks for any comment, clue or idea. I maybe have minsunderstanding or misconception, do not hesitate to point it out. (same for language mistake, I'm not EN native as you may have noticed)


Edit notes:

  • History isn't backup: My point isn't to create database backups but instead to be able to query/work with edit history (e.g. tell the user when/what was the last modification, when was this line added etc...

  • Documents: By document I do not (necessarily) talk about file on a file system. It could just be record in a database (we could imagine 1 table for the current content of "document" and 1 for its history)

  • Volume & Goals: I aim to develop such a system for personnal need, but with a scalable design. I would otherwise just use Git. The point is to give a wbe interface to write down notes and keep track of evolutions (among other features)

  • You forgot to mention pragmatically how big your data is. Are you thinking of megabytes, gigabytes, terabytes? Commented Jan 3, 2017 at 10:26
  • I've currently no data as I am designing the project. So called "document" will generally be user written notes/text/anything so quite small
    – pltrdy
    Commented Jan 3, 2017 at 10:29
  • 1
    Your question is unclear. You say "The edits must be stored as in Git: only what has actually been edited. Since the contents can be long documents, I don't want to save a full copy each time for obvious reasons." But that's the exact opposite of how Git stores commits: Git always stores the entire tree for every commit. So, what is it you want to do? Do you want to store only edits? Or do you want to store like Git? Commented Jan 3, 2017 at 10:38
  • Why would you otherwise use git? It looks like you can actually use git (which scales quite well), notably if the data has some "textual" format Commented Jan 3, 2017 at 10:41
  • @JörgWMittag: This is actually unclear. But it looks like Gits isnt duplicating content: stackoverflow.com/a/8198276/5903959
    – pltrdy
    Commented Jan 3, 2017 at 10:46

2 Answers 2


You might use some SQLite database (for example pitrdy.sqlite), with the convention of using textual content (perhaps JSON or XML) for "wide" things like your "document". In other words, you would have some SQL table(s) with some wide TEXT column(s) containing your "content text"

My goal is to keep track on edits

But you then need first to define what you call an edit. It might be less trivial than what you think. You might want to keep only some "validated" data.

(you did not mention any operating system; I'm guessing you are on Linux or some POSIX operating system)

You could then take advantage of git itself. You would version under git the textual dump pitrdy.sql of your database (obtained by sqlite3 pitrdy.sqlite .dump > pitrdy.sql command.

Then you could use git hooks to make the conversions. The pre-commit hook (to be installed, perhaps as a symlink, on the client user's .git/hooks/pre-commit file) would be that dump command

#precommit git hook
# make a backup, just in case
mv -f --backup pitrdy.sql pitrdy.sql~
# dump the sqlite database
squite3 pitrdy.sqlite .dump > pitrdy.sql

The post-merge hook (to be installed, perhaps as a symlink, on the client user's .git/hooks/post-merge) would be

#post-merge git hook
# make a backup, just in case
mv -f --backup pitrdy.sqlite pitrdy.sqlite~
sqlite3 pitrdy.sqlite < pitrdy.sql

I'm using similar tricks in my basixmo github project

If your sqlite3 "database" pitrdy.sqlite has a "reasonable" size (e.g. a few dozens of megabytes at most) this could practically be enough (see this answer).

Perhaps you might look into existing Wiki engines or CMS...

More generally, you might keep the data is some textual structured format (like JSON or XML) and work on that, e.g. use a version control system on that textual data. git can handle any kind of data (even binary, like images or sounds), but textual data is more friendly to it. The git hooks trick could be useful.

after edit in question

I would still suggest to keep textual data in git. Don't reninvent version control, but take advantage of it.

Storing all the versions is not a big issue, since disk space is really cheap. Once you've got tons of users and gigabytes of data, you could afford redesigning the whole thing. Until then, keep it simple.

Look into Wiki implementations (they seem to be close to what you want), and perhaps take inspiration from Emacs org-mode

  • Thx for your reply. 1) I'm not currently considering technical "details" such as OS, but yeah, Linux anyway 2) I'm not sure to 100% understand your point and will read it again but just to be sure: note that I do not want to have backups or versionning of the database, but instead a table containing user edits, to be able to work with it (show him when a line was added for example, when one online document has been edited). I'm editing my post to make it clearer
    – pltrdy
    Commented Jan 3, 2017 at 10:34

What you need is a utility to differentiate (Diff) the last and current record. There's no reason to reinvent the wheel, so search the internet for something you can include in your application. You only need to save the information indicating the differences. This can be undone to see what the file/record looked like previously.

Since most users are likely to look at the current version, I would store that in its entirety and maybe even the previous one. This way, you only have two full copies. After the first change, you can then store the Diff and just roll-them back as needed.

This is something you can slowly implement in your application. You may find there is little need to view every piece of history. Could your users live with the last 5 changes? The size of these records may not be that significant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.