Getting Started With Rails: RDBMS, Associations, Resources

A single table in a relational db, in its simplest form, can be imagined to be like a spread-sheet (rows and columns)
Each column has a title (attribute) and can store only one predefined type of data (integer, string, boolean, …) and this is where it differs from a generic spreadsheet
Each row represents the data stored in the form of attribute values as per the columns
Each row is identified by a unique integer attribute called the 'primary_key' of the table
There can be multiple (linked or unlinked) tables in the database, just like one file can have multiple spread-sheets
One table refers to the rows of one other table using an integer attribute called the 'foreign_key' (see image below for illustration - Courtesy: http://publib.boulder.ibm.com)

The CUST_ID is foreign key in Accounts table and primary key for the Customers table
The foreign-key column is used to set up a 1-to-Many association between two tables (Foreign Key is on the 'Many' table)
Rails offers SQLite3 as the default database (SQLiteStudio is a good free viewer available for SQLite3 among many other free and paid ones)
A db table has two views
- schema view: shows the columns in each table and the type of data each column can store
- data view: shows the actual data in the table (much like the spread-sheet view)

ActiveRecord is a pattern which helps abstract queries to the database through something called Object Relational Mapper (ORM)
ORM helps generate SQL queries using a Object#method syntax, for example (in Rails)
- code like Post.all would generate an SQL query that looks like
- SELECT "posts".* FROM "posts" which extracts all rows from the Post table in the database
Each instance of an ActiveRecord Model object represents a row in the corresponding table
Each object has getter and setter methods for the real attributes (columns) as well as virtual attributes (created based on associations)
ActiveRecord in Rails is a thus a very simple, yet powerful (convenient) ORM implementation that saves lot of verbosity
Although not obvious to new web developers (like me :-)), but the simplicity of Rails ActiveRecord brings in a harsh limitation on Rails
- the more complex the database(s) gets (big banks, hospitals, large corporations, etc.), the harder it is to translate code to SQL commands when data for one object spans over multiple tables across multiple linked databases
- these large and complex databases need to be optimized for other aspects (for example, analytics) than just simple traversing
See the image below (Courtesy: Tealeaf Academy) to get the basic idea of the implementation of ActiveRecord in Rails

Migrations are modifications done to the database schema (additions, deletions, modifications of or to the individual tables in the database)
Migrations should be created using the rails generate migrations new_migration_name task (this is the only worthwhile use of the rails generate task)
- the created migration file (Ruby) could be then modified manually to fine-tune the changes to be made to the schema
Migrations should be effected using the rake db:migrate rake task
- this will modify the schema.rb file to record the changes made to the schema (this file should be checked into the version control system)
- this task will also make the implement the changes in the db (which is not checked in to the repository)
see the Migrations documentation for details

1-to-Many (1:M) associations between two Models (each table in the db represents an ActiveRecord Model) can be set up using the foreign-key attribute
The model class for each entity needs to have the appropriate command that Rails provides to set up this association correctly (See the image below as an example - Courtesy: Tealeaf Academy)
Many-to-Many (M:M) associations are essentially two 1:M associations set up through an intermediary table, called the join-table
the preferred way to set up a M:M association is using a has_many, through: (hmt) method (see the diagram below - Courtesy: Tealeaf Academy)
the 'hmt' method lets us have the join-table as an ActiveRecord Model, which means it can have additional attributes (like created_at if we want to keep track of how long the association exists between the two objects of the end models)
the non-preferred way to set up a M:M is with a has_and_belongs_to_many method
the 'habtm' way needs a join-table too but does not give us a join-model, which limits the way we can use the join-table
see the Associations Basics documentation for details

The routes.rb file defines all the routes the web application can take
Individual routes can be added to this file using the syntax HTTP_METHOD '/route', to: 'controller#action'
- get '/posts', to: 'posts#index' will set a route for a GET request to the /posts path to be handled by the Posts controller's index action

The most common routes required for a controller named, say Posts, can be added by a single line of code in the routes.rb file

just having resources :posts in the block in the routes.rb file is going to give the following routes

Prefix	Verb	URI Pattern	Controller#Action
posts	GET	/posts(.:format)	posts#index
	POST	/posts(.:format)	posts#create
new_post	GET	/posts/new(.:format)	posts#new
edit_post	GET	/posts/:id/edit(.:format)	posts#edit
post	GET	/posts/:id(.:format)	posts#show
	PATCH	/posts/:id(.:format)	posts#update
	PUT	/posts/:id(.:format)	posts#update
	DELETE	/posts/:id(.:format)	posts#destroy

This can be customized by using the except: or only: options
- resources :posts, except: :destroy would set all routes except the DELETE (last one in the list above)
- resources :posts, only: [:index, :new, :create] will set routes only for the first three actions in the list above
See this Rails Routing Guide page for details

Learning Ruby