0

I've come up with some ideas for the database design, but I don't know if any of them would be practical in production:

  1. posts and a separate post_edits table with a copy of all previous revisions. To get a single post, it's completely transparent as if the post_edits table isn't there. Nothing wrong with this, but it looks amateur to have two nearly identical tables and simply copy stuff between them like this. One benefit is that you could instantly drop post_edits if you ever needed to do that for some reason.

  2. Single posts table with everything in a self-referencing chain. To read a post, you'd filter for all posts with a certain slug or something, then order by most recent datetime to find the "real" post. On first thought this seemed kinda clever, but on second thought this looks really messy... how would you get a list of actual posts, wouldn't there need to be an extra column just to keep track of which are "real"? Pushing the "real" posts to ElasticSearch or keeping track of them in Redis could solve this issue, but it would be complicated to manage and performance wouldn't be optimal.

  3. post_meta and a separate post_data table joined onto it. Whenever a revision is made, a new row is added to post_data. To get a list of posts: for each row from post_meta, select the latest referencing row from post_data and join it. On the surface it looks more professional than 1., but it'd be at least 2x slower, and you don't really get any advantages from it.

How does StackExchange implement it, what does their database design look like? If they use diffs instead of storing entire posts, is there a similar python implementation to encode/decode between two posts and their diff?

  • The mad scientist in me wonders if there would be a way to use git for versioning posts, but I don't think that I (the somewhat lame, but reasonable programmer) would attempt it. – Neil Dec 12 '17 at 9:12
2

I don't know how SE does it, but the most straight-forward way to approach this would be (IMHO):

  • Store the initial post in a posts table
  • When someone makes a change, create a diff between the new post and the original post (or previous version, if it has been edited before).

    Look for a text-diff library, or google for a 'Longest Common Subsequence' algorithm if you have a really good reason to implement this yourself.

  • Save the diff in a post_edits table (including the date)

  • When someone needs to see a post, load the original post and apply all changes that have been made (see 'Event Sourcing') in the correct order.

This is how some SCM systems, such as svn do it. In fact, for prototyping, you could store your posts in files and use git to manage them. Later, you'll probably want to switch to a DB for performance.

If you're extremely concerned with performance, you can keep the most recent post in the posts table and revert the changes stored in posts_edits when someone wants to see an older version.

Edit: Sorry, I somehow overlooked your last sentence. I don't any python libraries for this, but on first glance, difflib seems promising.

  • Git actually stores snapshots, not diffs. – 8bittree Dec 11 '17 at 19:29
  • @8bittree true, although it does of course store parts that changed separately. I have removed it from my answer, because it really was a bad example of the concept. Thanks for the hint. – doubleYou Dec 12 '17 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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