A database schema for articles and their revisions

Question

I'm designing a super simple web application for creating articles. All articles on this site can be edited by different people and each edit is a revision. Using an RDBMS, this is my schema:

Articles
id
title
first_revision_id --> Revisions.id
current_revision_id --> Revisions.id

Revisions
id
article_id --> Articles.id
content
editor_id --> Users.id
created_at

Users
id
username

You may be wondering what the first_revision_idfield is for. In the application the author of the first revision of an article is the article's "author". I use the field as an easy way to join and get the author of the article.

I think this is a pretty solid schema. My only problem is that I have to do a lot of joins to do things like display current article content or getting the author of an article. Also, creating an article is a bit odd because I have to:

1. create an article
2. create a revision and using the article's id
3. update the article with first_revision_id or current_revision_id

That's 3 calls to the database!

I'd love to hear your opinions or suggestions if any!

Answer 1

If first revision is the sole criterion for someone to be the author, then Articles needs neither a first_revision_id nor a user_id foreign key. Similarly, if the most recent revision is always the current revision, then Articles.current_revision_id can go, too.

Benefits of replacing foreign keys with more complex queries:

• It won't be possible for the foreign keys to be out of sync (e.g. a first_revision_id that points to a revision from another article).

• Fewer foreign keys means smaller rows and fewer indices, meaning that more of the database fits in cache, a potential performance benefit.

Costs of replacing foreign keys with more complex queries:

• Complex joins can be less performant, depending upon the database engine.

• Complex queries are harder to read and maintain. However, complex queries can be abstracted away in views so that the application can use simple queries.

Whenever there's a choice between a schema that cannot become inconsistent and one that can, chose the one that cannot, then test for performance. Only if performance is inadequate should you apply an optimization that has the potential for inconsistent data (and then only when testing proves that it actually does help performance).

If your database is like most, reads will predominate and writes will be relatively rare. In that case, don't sweat the number of separate writes it takes to create a new article. Just be sure to wrap them all in a transaction so that they occur atomically.

Answer 2

If you're only using first_revision_id to get the author, then why not link this to user.id directly?

There are obviously other operations you want your (web) app to perform:

• get articles for reading (by title, since date, by author, etc),
• get articles for for editing/admin,
• show revisions (by article, since date),
• other non-article operations as well (create user, etc.)

Define all these operations first, whether they are called by your (web) app or via some admin script, and ensure these functions are the only ones called by your web app. SQL should only appear in these functions. this makes requirements explicit, future changes easier, and centralizes other concerns like security or authorization.