S Q L through Active Record, Pt. Two

Joseph Syverson
Oct 29, 2019 · 3 min read
Dunder Mifflin Paper Co, Inc

For my second and best S Q L teacher, who provided both for my capability to write this series, as well as inspired the modeling of this application. See all of his tutorials here.

The previous part of this series.

We last finished with a Rails API using My S Q L as our database. Now, let’s make a schema for our database based on the following propositions:

  • A company has many branches.
  • A branch has many employees, but an employee only has one branch.
  • Employees are not just related as peers — members of the same class, but also hierarchically. Each employee can have one direct superior and each superior can have multiple direct inferiors.
  • Employees have many clients, associated by sales they’ve made to those clients.
  • Clients know employees through the things they’ve bought from the company, through employees at the company.

The banner at the top of this article models the domain that these propositions describe. Let’s code it out, starting in the terminal. In /cmon-learn-some-sql, we’ll use the model generator to make both classes and the tables that they’re related to.

We want the model Branch to look like this:

Active Record will auto-generate the I D, set it to primary, and auto-increment it, plus give us the timestamps created_at and updated_at. So in the terminal we need only:

rails g model Branch name:string

We want Employee to look like this:

We’ll declare the four columns, and let Active Record take care of the rest.

rails g model Employee name:string birth_date:date salary:integer manager:boolean

Client should look like:

We’ll just tell Active Record to make a column “name”, as before.

rails g model Client name:string

Now, we need join tables as reference for our main models. The first will have to join an employee and the branch that they work at.

rails g model BranchEmployee branch_id:integer employee_id:integer

I named the model that joins employees in a relationship of command “SuperiorInferior”.

Finally, employees and clients are related through sales. In addition to the I D’s of both these instances, we’ll also track the amount of the sale that exists between them.


rails g model Sale employee_id:integer client_id:integer amount:integer

All done. Now migrate to create the schema: rails db:migrate .

We now have a database linked up with our Rails application. In the next part, we’ll fill out our models with some associations that take advantage of the join tables that connect them.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade