Relationships are Hard
Who belongs to who? Who has many? Is someone between us? …asks the database
belongs_to
has_many
has_many through
Let’s explore database relationships, using a live application I built called Sushi Surprise! as an example throughout.
Databases, Tables and Fields
These are the core components of a database. Think of it like a Spreadsheet as the Database, where each Tab is a Table and each cell is a field for data. I struggled to understand database relationships. There were four constants that I derived that really helped me to connect the dots for my understanding:
A table can only belong to OR have many of another table.
The owned class holds the owner it belongs to's foreign key/id.
Two tables cannot have many of each other.
X is the join table if both Y and Z have many Xs AND if X belongs to both Y and Z.
After I explain the building blocks of database relationships, I’ll explain these four constants further. First, lets break down the data in our example application.
Example Database - Sushi Suprise!
Sushi Surprise! is an all-you-can-eat experience in a kaiten sushi restaurant, where you can have fun eating and reviewing sushi surprises from every one of the seven seas. The tables and fields in Sushi Surprise! are:
For the sake of this blog post, ignore the data types. Also for now, ignore how some data is colored red - that’ll make sense in just a bit once we go over database relationships. What’s important here is to see how we’ve broken down all data into different tables and fields.
Relationships
Here comes the fun part! What tripped me up so much about relationships is how literal I thought. You’ll find yourself in many rabbit holes from being as literal as I was. Remind yourself that there’s no right or wrong way of modeling domain relationships, so long as you keep the aforementioned four constants (dare I say conventions) in mind.
Belongs To or Has Many
When we define our relationships, we can think of it in a binary fashion by asking what the table belongs to or what the table has many of.
A table can only belong to OR have many of another table.
In other words, if a table belongs to another table it cannot have many of that same table. Think of ownership with who belongs to who. A Customer owns an Order, so an Order belongs to a Customer. An Order cannot have many Customers, but a Customer can have many Orders. Our bi-directional relationship is established.
The owned class holds the owner it belongs to's foreign key/id.
This way, Order has the single responsibility of tracking its Customer. There’s no sense of both Order and Customer keeping track of each other as long as their bi-directional relationship (ie contract) is established.
The relationships defined that uphold all four constants in Sushi Surprise! are:
Has Many Through
Whenever you have two tables that have many of each other, you must create a join table to manage that relationship. This convention is so critical to remember. A Customer can have many Sushis. A Sushi can have many Customers. STOP!
Two tables cannot have many of each other.
A join table is required to manage this has many-to-many relationship. A customer will have many sushis but only after ordering them. A sushi will have many customers but only after it is ordered. With the Order table created, a Customer will have many Sushis through Orders and a Sushi will have many Customers through Orders.
Join Tables
Let’s wrap this all up together with our Order join table. We can be extra certain that Order is our join table between Customer and Sushi because it upholds our fourth constant.
X is the join table if both Y and Z have many Xs AND if X belongs to both Y and Z.
The domain model of Sushi Surprise! is:
Here we can see our tables and fields, our relationships and our join tables. We can also confirm that single responsibility of our data is upheld in tandem with our four constants:
A table can only belong to OR have many of another table.
The owned class holds the owner it belongs to's foreign key/id.
Two tables cannot have many of each other.
X is the join table if both Y and Z have many Xs AND if X belongs to both Y and Z.
There we have it!
ActiveRecord and Beyond
What we haven’t covered in this post is how to actually implement the aforementioned tables and relationships with code in an application. That’d be more appropriate for another post. In short and after database migrations, Ruby’s ActiveRecord gem manages these complex database relationships elegantly with Object Relational Mapping to connect the dots with tables in a relational database management system. More advanced capabilities of ActiveRecord include managing a many-to-many relationship without a join table through a has_and_belongs_to_many association, for example. I know - what a cliffhanger!
