Creating a Database — Part 3 of 3: Table Data Association

So far in parts 1 and 2 from the ‘Creating a Database’ post series, we went through:

• The file hierarchy composition of the project;
• Functionality and usage of each file, along with name conventions and file configurations;
• Created migrations for the customers and orders tables;
• Created orders and customers tables structures and migrated those changes to the database;
• Seeded the table rows with sample data using Active Record through the Pry console.

→ Project Composition:

• 1st part;
• 2nd part;
• 3rd part (this post).

Now we are heading to the 3rd and last part of the project. Let’s take a look again at the tables we created in the last post:

customers table

orders table

Above we have two tables: orders and customers. The relationship between them is represented through the Entity Relationship Diagram:

Adding a foreign key column

Knowing that the relationship of customers and orders tables is one-to-many, we can write down the following logic:

• A customer has many orders;
• An order belongs to a customer.

To create the connection between both tables, we need to use a foreign key column in the orders table, indicating that an order belongs to a specific customer with a foreign key corresponding to a customer’s primary key from the customers table.

Note: It’s extremely important that once again we follow name conventions. The foreign key column we will be creating in the orders table needs to have the exact same name of the table where the primary key of a customer is located, so Active Record can recognize the connection between the tables.

To modify a database table structure we cannot do it directly in the migration file, neither in schema.rb. The best practice if we are going to make a change is to create a migration so we can have a version control of this change. Let’s create a migration with the name flag of add_customer_id to indicate that we are going to add a customer id column to the orders table:

$ bundle exec rake db:create_migration NAME=add_customer_id

Doing so, will generate a new migration file with a timestamp and the name we just gave in the command, along with the upper camel-cased AddCustomerId class and the change method:

Now we can use the add_column Active Record migration method to add a column to the orders table, specifying the table’s name, the column’s name and data type we want the column to have:

To perform the migration, you can execute the following command in the terminal:

$ bundle exec rake db:migrate

If we take a look at our schema.rb file, we now see that the orders table has a new column of Integer type, called customer_id:

Note: Remember that you cannot make alterations to this file, it is just a snapshot of our database.

Checking the orders table using the SQLite extension for Visual Studio, we have our customer_id column we just added:

Through a pry session, let’s give the orders some customer_id foreign keys. You can initialize the console with the command below:

$ bundle exec rake console

To assign a foreign key of a customer to an order, let’s first retrieve the customers that we have in the customers database table, using the .all Active Record method on our Customer class:

We can use the two customers primary keys to be the foreign keys in the orders table. First, let’s assign the order with id = 1, a customer_id of the second customer from the customers table:

# Save the first order from the orders table into a variable
order_one = Order.first

# Save the customer with id=2 from the customers table into a variable
second_customer = Customer.find(2)

# Save the id of the retrieved customer into a variable
second_customer_id = second_customer.id

# Use the .update method to update the first order's customer_id
order_one.update(customer_id: second_customer_id)

You can see below how it looks on the pry session in the terminal, which also logs the SQL code:

Now let’s repeat the process but now assigning the order with id = 2, a customer_id of the first customer from the customers table:

# Save the second order from the orders table into a variable
order_two = Order.first

# Save the customer with id=1 from the customers table into a variable
first_customer = Customer.find(1)

# Save the id of the retrieved customer into a variable
first_customer_id = first_customer.id

# Use the .update method to update the second order's customer_id
order_two.update(customer_id: first_customer_id)

Nice! Now our orders table has its customer_id column with values related to customers present in the customers table:

We just went through the steps for updating a column’s data. We didn’t need to do all of that, but it’s important to have an understanding of where the foreign keys from the customer_id column come from — we could have just directly updated each order to have a customer id of values 2 and 1, without going through the process of getting it from a customer in the customers table.

I ended up adding more data to the tables so we will be going into the next section with the following updated tables:

customers table

orders table

Active Record Macros

So far we have the customer_id column in the orders table representing an association with a customer through a foreign key, but we still need to take an important step to make the connection work.

To do so, we are going to use Active Record macros which are methods that give our application some features to establish relationships between models. For example, we have a macro called attr_accessor from Active Record that allows you to both read and change data of an Object.

The macros we will be using to establish the logic between the customers and orders tables are:

• has_many
• belongs_to

Going to our customer.rb and order.rb models, we currently have:

Note: It’s essential to include the class inheritance from ActiveRecord::Base as it is where the macros come from.

Let’s add the has_many macro (or method) to the Customer class, to indicate that a customer has many orders:

Now in the order.rb file, we are going to use the belongs_to to indicate that an order belongs to a customer:

The macros we added to the classes above are nothing more than a Ruby method that takes a symbol as an argument. For the one-to-many relationship to work, we need to make sure we follow a name convention: the belongs_to macro should take a symbol argument on singular (:customer) and the has_many macro, on plural (:orders).

This makes sense if we think of the one-to-many association of the orders and customers tables: we have one customer that has many orders, being the orders belonging to a single customer, but the opposite is not true — an order cannot have many customers.

Manipulating data with Associations

Now that we added a column for the foreign key of a customer in the orders table and established a relationship between the classes of Order and Customer through Active Record macros, we can start working with our association. First, run the console Rake task from your terminal, it will start a pry session:

Adding the has_many macro to the Customer class, generated the .orders instance methods, which we can use to retrieve the orders from a particular customer:

# Get the first customer
customer_one = Customer.first

# Retrieve the orders from the customer with the .orders method
customer_one.orders

You can check how it looks in the terminal:

Great! We were just able to access the orders from a customer just chaining the .orders method directly to a customer. Let’s repeat the process for the other customers:

• Retrieving orders from a customer of id=2

• Retrieving orders from a customer with id=3

Now looking at the Order class, we have the belongs_to macro that adds some additional functionality to our class. This is how we would create a new Order instance before we had the belongs_to:

customer = Customer.first

order = Order.create(number: 7, delivered: false, item_name: "king bed", customer_id: customer.id)

With the macro added to our Order class, we can assign a customer_id foreign key passing the instance of a customer directly, instead of getting a customer id:

customer = Customer.first

order = Order.create(number: 8, delivered: true, item_name: "chairs set", customer: customer)

Another useful tool from the belongs_to macro is that we are able to use a create_customer method, which creates a customer adding it to the customers table and then we can associate the method with an order directly:

# Create an order without assigning a foreign key
order = Order.create(number: 9, delivered: false, item_name: "center table")

# Associate the order with a new customer created through the .create_customer method
order.create_customer(name: "Meghan Sefried", address: "40 Warner Ave, CA", city: "Los Angeles", zip: 30597)

# Save the association
order.save

For the has_many macro we can also perform some helpful methods. One common case is to use the << method (shovel method) to add a new order to a customer:

customer = Customer.last

customer.orders << Order.new(number: 10, delivered: true, item_name: "mirror")

So handy! It’s not just easy to use but it’s also easy to understand what we are doing in our code.

Conclusion

This was the last part of the ‘Creating a Database’ post series. In this post we:

• Added a foreign key column to our orders table, to associate an order with a customer;
• Created the association between a customer and an order, adding foreign key values to the customer_id column;
• Added the belongs_to and has_many macros to the Order and Customer classes respectively;
• Went through some examples of how the macros can create helpful methods to associate two tables.

Building associations between models with macros allows access to more methods and therefore it expands our application. The ‘Creating a Database’ project had 2 tables in a one-to-many table association. However, you can develop database table associations using several tables interconnected, for example with a many-to-many relationship, applying the has_many :through macro.