Requirements Document: Database Modeling
Learn how to properly structure a database schema for a professional Ruby on Rails web application.
Guide Tasks
  • Read Tutorial

What is Database Modeling?

Before we get into modeling our database, we first need to know some of the key components of a well structured relational database:

  1. A good database structure should be DRY - this means that there should never be duplicate content in the database. As an example, you shouldn't have a table for posts and authors that both contain the name of the user who created a post. Instead you should have the posts and authors tables reference a users table, and the users table should store the name.

  2. Tables should have one responsibility - similar to #1, your database tables need to each have a single function. For example, if you're building a survey application, it would be a bad practice to put survey questions in a table called surveys. If you did this you'd need to alter the database every time you need to add a new question. Instead you'd create a table called surveys that lists all of the surveys and then connect that to a table called survey_questions, that way when you want to add a new question it would be simply be a new record in the database, not a new column.

  3. Ensure you're using proper data types - make sure you understand the type of data that you're going to be using in a table. I ran into an issue on an EventBrite project I was working on where the API changed and increased the number of characters returned in a query and it was causing errors throughout the site. It turned out that I was using the string data type, which limits the number of characters to 255, instead of text, as soon as I altered the column's data type it fixed the issues.

So what should our schema look like? Let's take it step by step. First let's list all of the potential models for DailySmarty:

  • User
  • Post
  • Follow
  • Comment
  • Like
  • Topic

After we build out the minimum viable product there will definitely be new models to add, however this is the basic structure we're going to start out with. Now that we know the models that we'll use, let's define how they're connected:

User
- User has_many posts
- User has_many followers
- User has_many comments
- User has_many likes

Post
- Post belongs_to user
- Post has_many comments
- Post has_many likes
- Post belongs_to topic

Follow (this will be a has_many through table, we will go into this in detail since it's more advanced, good resource here for a full reading)
- Follow belongs_to user
- Follow belongs_to follower

Comment
- Comment belongs_to user
- Comment belongs_to post

Like
- Like belongs_to user
- Like belongs_to post

Topic
- Topic has_many posts

With our initial tables and relationships all setup, we're ready to build out the schema for the requirement's document. The schema will include:

  • Table names
  • Columns for each table
  • Relationships setup by foreign keys
  • Data types for each column

The key of this portion of the requirement's document is to specify the database in a way that you will be able to create the entire application's database schema directly from this information.

Database Schema

# Since we'll be using Devise for authentication many of these fields will be supplied automatically

"users"

string   "email",                  default: "", null: false
string   "encrypted_password",     default: "", null: false
string   "reset_password_token"
datetime "reset_password_sent_at"
datetime "remember_created_at"
integer  "sign_in_count",          default: 0,  null: false
datetime "current_sign_in_at"
datetime "last_sign_in_at"
inet     "current_sign_in_ip"
inet     "last_sign_in_ip"
string   "first_name"
string   "last_name"
datetime "created_at",                          null: false
datetime "updated_at",                          null: false
text     "avatar"
string   "username"

"posts"

string   "title"
text   "content"
integer  "user_id"
integer  "topic_id"

"follows"

integer "follower"
integer "followed_user"

"comments"

text   "content"
integer  "user_id"
integer  "post_id"

"likes"

integer  "user_id"
integer  "post_id"

"topics"

string  "title"

The most complicated portion of this database schema is the Follow model. Even though the concept of having friends or followers in an application is common in real life, building the functionality into databases isn't quite as straightforward. This concept is called a self referential model, where a user will need to be able to reference another user. We'll go into the proper way to set this up when we get into the application implementation.

Rails Conventions

You may also notice that I switched between using singular and plural names for the database elements, this wasn't arbitrary. The rails convention for naming is as follows:

  • When referencing a model you call it by it's singular name and you capitalize it. This is because when you're talking about the model you are talking about the abstract data class, not a specific instance or record of the class. The difference is the same as saying that Human is the model, Jordan is an instance of that model.

  • You use the lowercase and plural naming structure when you're talking about the database tables themselves. This is because when you're talking about the database tables you're talking about real world instances of the model.

  • You reference models in plural form only when you're declaring them in a has_many relation declaration. This convention was configured for readability. It makes more sense to say:

class User
  has_many :posts
end

Compared with:

class User
  has_many Post
end

Reference