Planning the Physical Model

Learn about planning the physical model of the database for our Rails application.

A formal way to model a database is called normalization, and it’s a dense topic full of equations, confusing terms, and mathematical proofs. Instead, we are going to outline a simpler approach that might lack the precision of theoretical computer science but is hopefully more approachable. Here’s how to go about it:

  1. Create a table for each entity in the logical model.
  2. Add columns to associate related models using foreign keys.
  3. For each attribute, decide how we will enforce its requirements and create the needed columns, constraints, and associated tables.
  4. Create indexes to enforce all uniqueness constraints.
  5. Create indexes for any queries you plan to run.

To do this, it’s immensely helpful if we understand SQL. In addition to knowing how to model our data, knowing SQL allows us to understand the runtime performance of our app, which will further help us with data modeling. Of all the programming languages we will ever learn, outside of learning SQL, the hardest part of the planning process is step 3: deciding how to enforce the requirements of each attribute.

We’ll bring together some or all of the following techniques:

  • Choosing the right column type.
  • Using database constraints.
  • Creating lookup tables.
  • Writing code in your app.

Let’s dive into each one of these.

Choosing the right column type

Each column in the database must have ...

Get hands-on with 1400+ tech skills courses.