Database Tables & Relationships

At the heart of any CRUD app are database tables (also known as Models in Rails) and the relationships between them. Before you can do any real work on building an app, it’s important to decide how to set up your data into various tables, and how the tables relate to each other. Once these relationships are organized, building the rest of the app should be a fairly simple process.

There are several ways database tables can be related to each other. One table can belong to another table — for example, a product can belong to a suppliers. Two tables can have many instances of each other — for example, a user can attend many events while an event would have many attendees/users. One table can also be linked with itself, as in the case of a social media site where users can follow each other.

It’s important to understand how your data is related so that you can set up your database tables properly with the correct associations!

Has Many/ Belongs To Relationship

In order to keep our data DRY (don’t repeat yourself!), sometimes it’s necessary to create more than one data table that are related. In the has_many/ belongs_to relationship, one set of data can be applied to multiple instances of a particular model. When this occurs, it’s better to separate out the repeating data and create two distinct tables and simply link them.

An example would be storing a separate table of suppliers that you use that provides multiple products within your online store. In this example, each product belongs_to :supplier, and each supplier has_many :products.

Every time information from a supplier might change (for example, if the primary contact changes and you need to update the email address or phone number), then you only have to update this information in one place (the suppliers table).

The process of separating out data into different tables to keep things DRY is called normalization. In order to normalize our example product/supplier data, simply move all the supplier information to a new table. Headings for the new supplier table include: supplier name, supplier address, supplier contact information, etc. Then in the products table, add a column for supplier_id, and list the id# of the particular supplier that the product belongs to.

Once the data is separated into two different tables, you can still view the two tables together in a program such as Postico using the SQL query:

SELECT * FROM products
JOIN suppliers
ON suppliers.id = products.supplier_id;

This syntax tells Postico how the two tables are related, and you’ll easily be able to look up the supplier information for a particular product, without duplicating all that information on all the products that the supplier provides!

Join Tables & Has Many Through Relationships

In some cases, two different tables are related in a has_many/ has_many relationship. For example, in an event management app, a user/attendee would have many events, but an event would also have many users/attendees. When a relationship like this occurs, you’d create a third table, called a join table, that links the two other tables.

In the users/events example, you’d set up a join table, perhaps called Registrations. The Registrations would contain a user_id and an event_id, and each registration would belong to both a user and an event. Conversely, a user would have_many :registrations, and an event would also have_many :registrations.

Optionally, you could also store other data in the join table. In the Registrations example, you may want to store a “status” to indicate whether a registration is active, pending, canceled, etc. However for a pure join table, the only required fields would be the id’s associated with the two other tables being joined.

Once you have the two main tables linked via a join table, you can also link them to each other through the join table. In our example, a user has_many :events, through: :registrations, and an event has_many :users, through: :registrations.

Self-Join Table using Aliases

When a more complicated relationship occurs within one table, you may need to create a join table to link instances of the original table to each other. An example of when this might be useful is a social media platform in which one user can follow another user. To pull this off, a join table must be created to link one user to another within the same Users table.

When creating this new join table, rather than having two columns with the id numbers from different tables, it’ll have two columns with the id numbers from the same table. In order to keep the information straight, you must give the model two different aliases to represent their role in the relationship being depicted.

In the example mentioned, the relationship being modeled between two users is a follower/followee relationship. To make things easier to understand, in class we called it a fan/idol relationship wherein the fan follows the idol. The join table would then be called Connections, and the columns would be: id, fan_id, and idol_id.

Since there are no tables in our database called Fans or Idols, we have to specify in our Models what these aliases mean. In the Connection model, we have to say:

belongs_to :fan, foreign_key: :fan_id, class_name: “User”
belongs_to :idol, foreign_key: :idol_id, class_name: “User”

Then in the User model, we have to add:

has_many :fans, through: :relationships
has_many :idols, through: :relationships

These are only a few ways database tables can be related to each other. For further reading on Active Record Associations in Rails, visit the Ruby on Rails Guide!