Work with user content edits history: storing differences vs data duplication

Question

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)

pltrdy

---

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)

Answer 1

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

#!/bin/bash
#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

#!/bin/bash
#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

Answer 2

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.