Database design: Keeping track of tag changes between revisions

Question

I'm building a revision system similar to the one Stack Overflow has and there's one thing I can't get my head around, what's the best way to the differences in tags between multiple revisions?

The simplest way I can think of is we have 3 tables, revisions, tags and another to link the two.

Each revision then has its own set of tags, the only problem is that when you want to display all revisions, the tags need to be re-compared to find out the differences even though they don't change.

Perhaps an extra field or two could be added to mark tags which have been added or removed from the previous revision.

Is there a better way of doing this?

Answer 1

Do not overcomplicate your schema and do not optimize prematurely. Pair-wise comparison of revision tags is a very cheap operation (provided you have all data in memory) and it can be done while prepararing view model.

Answer 2

I'd say you're on the right track

T1: Revisions
|Item ID|revision ID|more...|

T2: Tags
|Tag ID|Tag fields...|

T3: Tags Per Revision
|Item ID|revision ID|Tag ID|  /* This will have multiple rows, 1 per tag) */

To find tag diffs, simply retrieve tags for last 2 revisions and compare using your favorite hashmaps in whatever language you have, or however else your language can implement set differences efficiently.