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:
first_revision_id --> Revisions.id
current_revision_id --> Revisions.id
article_id --> Articles.id
editor_id --> Users.id
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:
- create an article
- create a revision and using the article's id
- update the article with
That's 3 calls to the database!
I'd love to hear your opinions or suggestions if any!