Relationship Advice... for Tables

      You can't spell relatable without table!
... Okay, sorry about that. What I'm not sorry about is practicing creating relational databases. Forging relationships between data in different tables can either be a simple, straightforward process, or it can make your head spin. It can help to concoct an example or two (or three) to really help you visualize and then implement these connections in order to create your very own API, especially if you are new to the concepts. In this blog, I'm going to run through one such example using Ruby and the Active Record gem.
      Let's say you're making an application that allows users to catalog and keep track of their book collection. Well, you'll definitely need a books table. It might look something like this:

      At first glance, this is a perfectly fine way to store all the information for a book. But what if you wanted to add more information about the author? Hometown, date of birth, favorite color, or what have you. Sure, you could add those columns to your books table, but is it really The Shining's business to know that Stephen King's favorite food is cheesecake? Hardly, and imagine having to put all of that info in every single time you want to create a Book instance! Fortunately, there's a better way to go about this. We're going to split the author column off into its own table and connect it to our books table by using what's called a foreign key. First, consider the relationship between books and authors - a book belongs to an author, and an author can have many books (a book can have many authors as well, but let's not talk about that right now). This defines a pretty cut and dried example of a one-to-many relationship, wherein the one is the author and the many represents the books by said author. With this knowledge in tow, we can now replace the "author" column in the books table with our foreign key, "author_id". This will be an integer that points to the id column (or primary key) of the Author instance that the Book belongs to:

      Now that we've made this connection, it's time to add macros to our Ruby models to reflect the change:

class Book < ActiveRecord::Base
   belongs_to :author
end

class Author < ActiveRecord::Base
   has_many :books
end

      With the "author_id" column in our books table and the new macros we've put in our classes, Active Record will create an association between these models and generate all new methods for us! You can now call .books on an Author and get back an array of all of the Book instances that belong to them. Likewise, calling .author on a Book will return the Author instance that it belongs to. Neat!

      Taking another look at our books table above, we can see another potential issue; what if a book has many genres that can be attributed to it? Surely we don't want to cram them all into that one space, as that could make searching for books of a certain genre a little bit messy later on. We'll start by creating a Genre model and corresponding genres table, then get to work on forming an association between books and genres. A book can have many genres, but a genre can also be attributed to many books. We can't simply plop a foreign key into one of the tables and call it a day in this scenario. Instead, we're going to establish what's called a many-to-many relationship. This involves creating a join table with a foreign key from both parties involved:

      Our join table, book_genres, froms a connection by taking the id of a Book and a Genre and turning that relationship into an instance of its own (so we will need to add a BookGenres model). Now for our macros...

class BookGenres < ActiveRecord::Base
   belongs_to :book
   belongs_to :genre
end

class Book < ActiveRecord::Base
   belongs_to :author
   has many :book_genres
   has many :genres through: :book_genres
end

class Genre < ActiveRecord::Base
   has many :book_genres
   has many :books through: :book_genres
end

      ... and voila! An instance of Book now has the self.genres method, and an instance of Genre has the self.books method. It may seem like extra work to construct a "middleman" class like BookGenres, but think just think about the work that you aren't having to do thanks to Active Record (it really is a gem)! Now imagine if users were given the option to leave reviews. Can you envision another many-to-many relationship taking place? One in which a book has many reviews, many readers through reviews, and vice-versa?

      In conclusion, relationships between tables of data mirror those of the real-life objects that the data represents. Once you start thinking along those lines, and hash out a couple of examples like this one, creating associations between your tables is a breeze... especially with Active Record doing all the dirty work under the hood.

Tables created with the help of Miro.