What exactly is going on with the data in my Rails models?
For many people, starting out with Ruby on Rails, it’s not just a learning curve with this new environment, with its opinionated ways of doing things. It’s also their first time using a relational database and it’s often hard to find online tutorials that explain exactly what’s going on in there.
You know you want to associate your models together, but does it “have one” of these or does it “belong to” the other? Does it “have many” of those or are they associated “through” something else?
If you get your head around what’s going on in the underlying database it makes how you think about your Rails models much easier.
Relational databases (such as PostgreSQL and Mysql) have been around for many years and are built on mathematical set theory. If you follow their rules then you are guaranteed that certain characteristics will hold true, giving you a certainty that you dont get with non-relational (NoSQL) databases. But you have to play by their rules (continued below).
At its heart, a database engine is a server that sits over, and controls access to, a number of “databases”. Each database, in turn, is a set of structures, known as “tables”, that actually store your data. Relations are defined between different tables and bits of data within those tables (hence the name relational database) and if you want to read or manipulate that data you use Structured Query Language (SQL).
So the main unit of data storage is a table. What does one of these look like?
Well tables can basically be thought of as a single sheet of a spreadsheet. Every table has a fixed structure – a number of columns in our spreadsheet – and the data is stored in the rows. For example, if we had a table of people, it may end up like this:
So we can see that our table has a fixed structure; an ID column (more on this later), first and last name columns and an age column. Nearly all relational databases require that you specify the data-type stored in each column as well; in this case ID and Age would be integer columns and first_name and last_name would be strings – which in SQL syntax is a “varchar” (variable number of characters) column. Most common data-types can be represented in most SQL databases; date, date-time, decimal (although you need to specify the size and number of decimal places for these), text (strings that are longer than 255 characters), blob (binary large object, meaning an arbitrary stream of data) and a number of others that tend to be database-engine specific. What relational databases do not allow are compound types; no arrays, hashes, dictionaries or anything like that (not strictly true, some do offer it but it’s not standardised). If you want to store stuff like that, you need to define some relations.
A relation defines a link between a column in one table and a row in another (or even the same) table. Which is where our ID column comes in.
When defining a table, you nearly always want to define a “primary key” column (not always and you can also use compound keys, but that’s for another day). This is a column that stores a value that uniquely identifies the record in question; in the example above, Claire has primary key 1, Phil has primary key 2. Because these values are vital to the operation of the databae and need to be unique, we can tell the database to generate the value for us; normally by defining the ID column as an auto-incrementing integer column.
In order to show how this is used in a relation, let’s add a new table, companies:
|1||Pritchett’s Closets and Blinds|
And then we add in a new column, company_id (integer) into our people table:
So Claire’s company is Pritchett’s Closets and Blinds and Phil’s is “null”. This is a special database value that means “nothing”. To formalise the relationship between people and their companies we define a “foreign key” between the people table’s company_id field and the companies table (the syntax for this varies between database engines but luckily Rails’ migrations handle that for you). Now that the foreign key is defined many database engines can now enforce rules – for example, if you try to set a company_id of 999 your update would be rejected because there is no company record with an ID of 999. Or if you deleted Pritchett’s Closets and Blinds the database would reject the deletion because it knows that Claire is referencing that record. Not all databases enforce these rules; technically they’re not a true relational database if they don’t.
And from that simple setup – tables in a fixed structure with defined relations between certain columns and tables – a whole series of mathematically provable operations are possible. If you define your database structure correctly (known as rules of normalisation) then those same mathematical rules can apply to your data as well, meaning the engine can optimise your data access and you can be sure that certain operations will work as expected. And all Rails’ associations are variations on that same theme.
Tables and relations; really simple, but underpin huge amounts of commercial software written over the last fifty years.